So I have an Excel Sheet, Column A is filled with data (the range is different everytime)
I want to fill the empty col H with formula (from H2 to the last filled cell in colA)
This is the code where I referred from multiple sites.
But it only filled H1 and H2, where I want H2:H6 in this case.
Current output using xlUp.
colC,D,E,F are something that can be ignored at the moment.
I tried to change xlUp to xlDown.
It worked but it filled every cell from H2 to H(infinity), when some of data in colA stop at row60.
Sub Try()
Dim lRow As Long
lRow = Range("A" & Rows.Count).End(xlUp).Row
Sheets("Sheet4").Select
Range("H2:H" & lRow).Select
Worksheets("Sheet4").Range("H2").FormulaR1C1 = "=(RC[-6])/(RC[-1])"
Range("H2").AutoFill Destination:=Range("H2:H" & lRow)
End Sub
Can anyone please point out my mistake?
I know some of the method using Active Cells, but I don't want to use it because sometimes I don't know where my active cell is? like I dont know which cell is currently selected. (please let me know if I got the wrong idea of active cell)
Thank you.
Best Answer
This should work for you. It selects range of data in column A and then offsets it to the column H. This range will be given to the auto-fill.
I personally prefer this solution since it does not selects ranges and just deals with the cells till there's an empty cell.