Is there a way to edit the macro so that the prompt is only to fill the values of the rows that have data? [duplicate]

Solution 1:

You should use a with statement to qualify both your Rows and Columns counts. This will prevent any errors while working with older pre 2007 and newer 2007 Excel Workbooks.

Last Column

With Sheets("Sheet2")
    .Cells(1, .Columns.Count).End(xlToLeft).Column
End With 

Last Row

With Sheets("Sheet2")
    .Range("A" & .Rows.Count).End(xlUp).Row
End With 

Or

With Sheets("Sheet2")
    .Cells(.Rows.Count, 1).End(xlUp).Row
End With 

Solution 2:

How is this?

dim rownum as integer
dim colnum as integer
dim lstrow as integer
dim lstcol as integer
dim r as range

'finds the last row

lastrow = ActiveSheet.UsedRange.Rows.Count

'finds the last column

lastcol = ActiveSheet.UsedRange.Columns.Count

'sets the range

set r = range(cells(rownum,colnum), cells(lstrow,lstcol))