1

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!!

Community
  • 1
  • 1
user2887213
  • 11
  • 1
  • 1
  • 2

2 Answers2

2

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)
Joe Laviano
  • 1,038
  • 7
  • 14
-1

You don't need Autofill for this. You can assign the formula in one go to the entire range

Range("B1:B" & Lrow).Formula = "=INT(A1/5)+1"

Where LRow is the LastRow.

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

Please do not hard code values like 99999 to find the last row. See THIS on how to find the last row.

Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250