Find last cell in column and autofill/filldown based on another column

[pook_666]

Hello magicians!

What I am trying to do is find the last cell in column A and then autofilter that cell to the last row in column A based on column B.

I know how to find the last cell in the column using:

lrow = Range("B" & Rows.Count).End(xlUp).Row

And know to use the autofill on a known cell, like below using A2:

Range(Range("A2"), Range("A" & lrow)).FillDown

But how do I use this if A2 is not known?

 

[maabadi]

Fill down, fill cells based last value, then if last cell value is 2 , fill cells with 2, Are you want this?
Also you can add this (if your lastrow in column A different as column B):

lrow2 = Range("A" & Rows.Count).End(xlUp).Row

Then:

Range(Range("A2"), Range("A" & lrow + lrow2)).FillDown

Otherwise (if your lastrow in column A is same as column B):

Range(Range("A2"), Range("A" & lrow * 2)).FillDown

 

[pook_666]

@maabadi thank you for the help....however, the below starts from A2 and FillDown, which is not what I wanted.

In column A there is different data. My VBA code before this puts the current date in the last cell of column A. I then want to FillDown/AutoFill down column A from this point to where the end of column B is, so starting with range A2 won't do this as this will just FillDown what is in cell A2 and not what is currently in the last cell of column A.

I hope that makes sense?

 

[maabadi]

If you want Added One day from last Cell of Column A to Last Cell of Column B for Each cell Use This:

Sub FillDOwn2()
Dim lrow As Long
Dim lrow2 As Long
Dim cell As Range
lrow = Range("B" & Rows.Count).End(xlUp).Row

lrow2 = Range("A" & Rows.Count).End(xlUp).Row

For Each cell In Range("A" & lrow2 + 1 & ":A" & lrow)

cell.Value = cell.Offset(-1, 0).Value + 1
Next cell
End Sub

 

[pook_666]

@maabadi this works, however, as it's the current date in the last column, this just adds an extra day afterwards.

What I need, for example, if the last cell in column A is Dec-20, then I want to copy Dec-20 down until it reaches the same row as column B. (Dec-20 is just an example and this date will change)

Doing it your way, instead of showing Dec-20 shows 1Dec20, 2Dec20 and so on.

Maybe a copy & paste is the better way?

 

[maabadi]

Use This:

Sub FillDOwn2()
Dim lrow As Long
Dim lrow2 As Long
Dim cell As Range
lrow = Range("B" & Rows.Count).End(xlUp).Row
lrow2 = Range("A" & Rows.Count).End(xlUp).Row
For Each cell In Range("A" & lrow2 + 1 & ":A" & lrow)
cell.Value = cell.Offset(-1, 0).Value
Next cell
End Sub

Or

Sub FillDOwn2()
Dim lrow As Long
Dim lrow2 As Long
Dim cell As Range
lrow = Range("B" & Rows.Count).End(xlUp).Row
lrow2 = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & lrow2 + 1 & ":A" & lrow).value = Range("A" & lrow2).value
End Sub

 

[Fluff]

How about

   Dim Lr As Long
   Lr = Range("A" & Rows.Count).End(xlUp).Row
   Range("A" & Lr, Range("B" & Rows.Count).End(xlUp).Offset(, -1)).FillDown

 

[pook_666]

Sorry for the delay it replying....thanks both @maabadi & @Fluff - both of these work the way I wanted!

 

[maabadi]

You 're Welcome & thanks for feedback.