VBA Code to fill down to next Cell

[ajay_gajree]

Hi All,

I am trying to write a simple macro,

Sheets("Sheet1").Select
Range("B2").Select
Selection.End(xlDown).Select
Range("B18").Select
Selection.FillDown

My aim is to copy a formula in the last cell in Column B, so I recorded what I wanted with the above result,

So this is selecting a sheet and a cell and then uses control down arrow to go to the last cell with data in it in that Column,

I then wish to select the cell below and copy down using Control D

This is the bit that is wrong in the code as recorded macro is showing the specific cell B18, when I would like the first empty cell in the Column,

Any ideas?

Thanks and best regards

Ajay

 

[Rick Rothstein]

Maybe like this

Dim LR As Long
With Sheets("Sheet1")
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("B" & LR).Resize(2).FillDown
End With

I believe this could be use as well with the same results...

With Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp)
  .Copy .Offset(1)
End With

 

[My Aswer Is This]

VoG Always has good answers. I used this one today.

 

[Anne Troy]

Maybe like this

Sub Excel_Model()
 
Dim LR As Long, i As Long
 
 
With Sheets("Excel Model")
    .Range("A3").Value = Date
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    For i = 3 To LR
        With .Range("B" & i)
            If .Value >= Date Then .ClearContents
        End With
    Next i
    .Range("B" & LR).Resize(2).FillDown
End With
 
End Sub

VoG: Thanks! This helped me 6 years later...