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

 

[VoG]

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

 

[ajay_gajree]

Hi Peter,

Thank you very much that works very well,

I am starting to learn VBA by recording things and then looking and understanding each step.

On your code above, how does this work?

From my understanding you are declaring LR as a variable?
How does the long come into it?

The With statement seems easy to understand, logic seems to be to count the filled rows? and then fill down the first empty one which is presumably where LR has been defined as?

Thank you for your help, and for your help in explaining!

Best regards

Ajay

 

[VoG]

LR ("Last Row") is defined as Long which is a long integer.

LR is found by looking up from the bottom row (Rows.Count) up to find the first filled cell (i.e. the last filled cell when looking down).

The Resize(2) gets the last filled cell plus the one beneath it.

 

[ajay_gajree]

Thanks again, for the explanation, Best regards

Ajay

 

[ajay_gajree]

Hi,

I am trying to adapt this code so that it will copy down as above only if it meets a certain condition,

[COLOR=#ff0000]Sub Excel_Model()[/COLOR]
[COLOR=#ff0000][/COLOR] 
[COLOR=#ff0000]Dim LR As Long[/COLOR]
[COLOR=#ff0000][/COLOR] 
[COLOR=#ff0000]Range("A3").Select
ActiveCell.FormulaR1C1 = Date[/COLOR]
[COLOR=#ff0000][/COLOR] 
[COLOR=#ff0000]With Sheets("Excel Model")
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    .Range("B" & LR).Resize(2).FillDown
End With[/COLOR]
[COLOR=#ff0000][/COLOR] 
[COLOR=#ff0000]Range("B65000").End(xlUp).Offset (1)[/COLOR]
[COLOR=#ff0000][/COLOR] 
[COLOR=#ff0000][/COLOR] 
[COLOR=#ff0000]End Sub
[/COLOR]
[COLOR=#ff0000][/COLOR]

I have placed today's date in a cell (A3), and I would like to only copy the formula into the next empty cell in Column B, if the formula returns. Basically I will assign the original code to a Macro Button but if somebody clicks it twice I would like to delete the cells in Column B that equal or are greater than today's day.

Is this likely to be complicated?

Thanks and regards

I need to do a loop I think that deletes the cells in Column B that equal or exceed the date I have placed in (A3)

 

[VoG]

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

 

[ajay_gajree]

Thank you Peter, that is exactly what I needed!

 

[ajay_gajree]

Hi Peter

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

I've amended this so as to put todays date int he code, I think this is right,

When I play this, I am getting Todays Date in Column B and then when I play again it goes, Is there away to stop today's date being visible if you only play the macro once?

Best regards

Ajay

 

[VoG]

Sorry, I don't understand.