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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,216
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,216
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,235
Messages
5,546,649
Members
410,752
Latest member
MC01_
Top