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

pook_666

Board Regular
Joined
Aug 16, 2018
Messages
80
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:

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

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

VBA Code:
Range(Range("A2"), Range("A" & lrow)).FillDown

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

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,836
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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):
Excel Formula:
lrow2 = Range("A" & Rows.Count).End(xlUp).Row
Then:
VBA Code:
Range(Range("A2"), Range("A" & lrow + lrow2)).FillDown
Otherwise (if your lastrow in column A is same as column B):
VBA Code:
Range(Range("A2"), Range("A" & lrow * 2)).FillDown
 

pook_666

Board Regular
Joined
Aug 16, 2018
Messages
80
@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

Well-known Member
Joined
Oct 22, 2012
Messages
1,836
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
If you want Added One day from last Cell of Column A to Last Cell of Column B for Each cell Use This:
VBA Code:
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

Board Regular
Joined
Aug 16, 2018
Messages
80

ADVERTISEMENT

@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

Well-known Member
Joined
Oct 22, 2012
Messages
1,836
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Use This:
VBA Code:
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
VBA Code:
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
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,836
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You 're Welcome & thanks for feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,125
Messages
5,640,258
Members
417,131
Latest member
Seanr19871

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top