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

Ovisele

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

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
8
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
8
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
8
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:

Forum statistics

Threads
1,082,316
Messages
5,364,483
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top