VBA Macro to Fill Down a column(s) with the same data

Ovisele

New Member
Joined
Apr 28, 2015
Messages
12
First of all, thank you for taking the time to read this.

To keep it simple, is there a simple code line(s) that allow me to fill down automatically one column, with the value from a specific cell (I3 here), until the end of data (variable number of rows)? I have 600 files and the number of lines that need to be filled varied from 2 to 80+.

I have tried this lines but
It returns an error Argument not optional for the Autofill.

Sub Test()
Dim ws As Worksheet
Set ws = Worksheets("Calculus")
With
ws Range("I3").AutoFill Range ("I4:I" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
End With

Thank you very much!
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
Hi
try
Code:
       lc = Cells(3, Columns.Count).End(xlToLeft).Column - 8 
       lr = Cells(Rows.Count, "h").End(xlUp).Row - 3
       Range("I3:m3").AutoFill Cells(3, 9).Resize(lr, lc)
 
Last edited:

Ovisele

New Member
Joined
Apr 28, 2015
Messages
12
Hi
try
Code:
       lc = Cells(3, Columns.Count).End(xlToLeft).Column - 8 
       lr = Cells(Rows.Count, "h").End(xlUp).Row - 3
       Range("I3:m3").AutoFill Cells(3, 9).Resize(lr, lc)
Thank you! Partially it does the trick, with the following specifications:
- the last row is not filled (Row 15 from the below picture)
- as I see from your formula, the autofill is executed from Cells 3 to 9 only, but I have sheets with over 80 lines. How can this be done dynamically?



Many thanks!
 
Last edited:

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
No No No

cells (3,9) is range("I3")

from there it goes lr rows and lc columns
try
Code:
        lc = Cells(3, Columns.Count).End(xlToLeft).Column - 8
        lr = Cells(Rows.Count, "h").End(xlUp).Row - 2
        Cells(3, 9).Resize(, lc).AutoFill Cells(3, 9).Resize(lr, lc)
 
Last edited:

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
Any Good
 

Ovisele

New Member
Joined
Apr 28, 2015
Messages
12
No No No
You were correct. I was testing and just realized that is the refference.

cells (3,9) is range("I3")

from there it goes lr rows and lc columns
try
Code:
        lc = Cells(3, Columns.Count).End(xlToLeft).Column - 8
        lr = Cells(Rows.Count, "h").End(xlUp).Row - 2
        Cells(3, 9).Resize(, lc).AutoFill Cells(3, 9).Resize(lr, lc)
Works perfect with -2

Many many thanks, you are fantastic!
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
You are well come
and thank you for feedback
Be happy
 

Ovisele

New Member
Joined
Apr 28, 2015
Messages
12
Just a quick question - there are several files that have only one line (recording problem) and the VBA rerturns an error, for obvious reasons. Is there a simple way to tell it to move on when there is nothing to fill? Many thanks!
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
Well
you can add one line code

Code:
If lr>1 then

your code

end if
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,008
Messages
5,466,002
Members
406,460
Latest member
tryingtogoalone

This Week's Hot Topics

Top