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

Ovisele

New Member
Joined
Apr 28, 2015
Messages
30
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

0N1W4cS
0N1W4cS
Thank you very much!
0N1W4cS
96b1bbc4d41d61a37b4e2b2d855e4648-full.jpg
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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:
Upvote 0
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?
f28ad8998b67403aa218a43a93d6ba0d-full.jpg



Many thanks!
 
Last edited:
Upvote 0
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:
Upvote 0
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!
 
Upvote 0
You are well come
and thank you for feedback
Be happy
 
Upvote 0
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!
93859e533358ab4be0fd0b252b127b90-full.jpg
 
Upvote 0
Well
you can add one line code

Code:
If lr>1 then

your code

end if
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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
Back
Top