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

[Ovisele]

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!

 

[mohadin]

Hi
try

       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)

 

[Ovisele]

Hi
try

       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!

 

[mohadin]

No No No

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

from there it goes lr rows and lc columns
try

        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)

 

[mohadin]

Any Good

 

[Ovisele]

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

        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]

You are well come
and thank you for feedback
Be happy

 

[Ovisele]

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]

Well
you can add one line code

If lr>1 then

your code

end if

 

[Ovisele]

Absolutely brilliant! Will try and get back.