Copy contents to blank cells below, until last cell from another column

fruela

New Member
Joined
Oct 23, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

First post and new to VBA (pretty cool so far to automate boring reports).

I have a column with content in various cells, and some blank cells in the middle.

I have this code, to copy cell content above to all blank cells below.
But I only want to copy until the last row of another column (for example, if column A has rows filled until 100, I only want this to happen until row 100).

Can you please help me?
Cheers

VBA Code:
Sub copyToBlanks2()
With Range("AG:AG")
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,317
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
With Range("AG2:AG" & Range("A"&Rows.Count).End(xlUp).Row)
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,317
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,366
Messages
5,547,486
Members
410,797
Latest member
mlfuson
Top