Find Last Cell with Data and Drag that Formula Down

Hello,

I am trying to find the last cell in column B which is in Row 10000+ and copy the formula down to the last cell based on Column A's last row. Here is what I'm starting with:

Dim lastRow, dragRange as Long
lastRow = Range("B2").End(xlDown).Row
dragRange = Range("A2").End(xlDown).Row

I know how to drag a formula down based on a fixed cell such as:

Dim dragRange As Long
dragRange = Range("B2").End(xlDown).Row
Range("A2").AutoFill Destination:=Range("A2:A" & dragRange), Type:=xlFillValues

But, I don't know how to drag a formula down when the cell is not fixed. I don't want to AutoFill any rows above, just B10000 (or whatever the row is at the time) and below. Any help would be greatly appreciated.

Thank you in advance for your time.

Originally Posted by daffodil11

Perhaps like this:

Sub TerFuge()

Dim lngBLR As Long: lngBLR = Range("B" & Rows.Count).End(xlUp).Row
Dim lngALR As Long: lngALR = Range("A" & Rows.Count).End(xlUp).Row
Range("B" & lngBLR & ":B" & lngALR).FillDown

End Sub

Thank you for the quick response but the code does not drag the formula down to the last row in Column A. All it does is update the formula in Column B's last cell. Any ideas?

Originally Posted by daffodil11

I'd recommend attaching an example of your work. It works just dandy in my version.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window (or use the paperclip icon).

daffodil11,

Your code did work as you said. I am not really working with Columns A & B in my code; therefore, when I pasted your code in mine and changed the variable names and Ranges accordingly, I was using the wrong column for the Fill Range.

Thanks for your expertise, it's working great now!