how to use variable “last row number” to set the range in another column

Question

The data file has value 1 to 10 from A1 to A10.

      A    B
1     1    1
2     2    1
3     3    1
4     4    1
5     5    2
6     6    2
7     7    2
8     8    2
9     9    2
10    10   3

I need insert a column (eg column B) to my data file. In Cell B1 there is a formula (eg: (=INT(A1/5)+1)). I usually double click the right-bottom corner of cell B1, this auto-filling the rest of cells in column B to the row with data in column A (as shown in table above)

By using record Macro, it gave me codes below:

Range("B1").Select
Selection.AutoFill Destination:=Range("B1:B10")
Range("B1:B10").Select

The problem is the data files have different last row number with data(it might be row 8 or row 14). So the macro created as above won't fit the other data files.

I know using macro below, it finds last row with data.

lastRow = Range("A99999").End(xlUp).Row

How can I replace B10 in the range "B1:B10" with the variable? I would like to have a common macro. Could someone help on this? Appreciated!!

Joe Laviano Joe Laviano · Accepted Answer · 2013-10-16T17:22:44

Also, added to my comment, recording is a great tool, but can add some unnecessary "Select" statements. Your lines (with lastrow) can be just:

Range("B1").AutoFill Destination:=Range("B1:B" & lastrow)

how to use variable “last row number” to set the range in another column

2 Answers