CODEWITHSUNDEEP
excelvba
Will H
Will H

Reputation: 33

Making a dynamic VBA autofill range

Currently, I have some VBA code to auto fill the formulas in columns AE:AH whenever more data is posted into the sheet. I am attempting to future proof it and make the range more dynamic in case we were to add more formulas. Here is the current code.

Sheets(twtsumsheet).Select
usedRows1 = Worksheets(twtsumsheet).Cells(Worksheets(twtsumsheet).Rows.Count, "A").End(xlUp).Row
Range("AE2:AH2").Select
Selection.AutoFill Destination:=Worksheets(twtsumsheet).Range(Cells(2, 31), Cells(usedRows1, 34)), Type:=xlFillDefault

So for example, currently formulas are in columns AE:AH. Let's say we were to add one more to get another calculation off the data, so now we have formulas in columns AE:AI. My macro would continue to just autofill the formulas in AE:AH due to this "Range("AE2:AH2").Select" and this "Cells(2, 31), Cells(usedRows1, 34)"

I'm new to VBA coding so I'm not getting any good ideas for it. Any help is appreciated.

Upvotes: 0

Views: 6799

Answers (2)

Shai Rado
Shai Rado

Reputation: 33682

My OCD forces me to "clean" this code a bit...

Following @Scott Craner advice to look for last column with data.

Note: you should stay away from using Select.

With Sheets(twtsumsheet)
    usedRows1 = .Cells(.Rows.Count, "A").End(xlUp).Row
    usedCols1 = .Cells(2, .Columns.Count).End(xlToLeft).Column ' get the last column

    With .Range(.Cells(2, 31),.Cells(2, usedCols1))
        .AutoFill Destination:=.Range(.Cells(2, 31), .Cells(usedRows1, usedCols1)), Type:=xlFillDefault
    End With
End With

Upvotes: 2

user4039065
user4039065

Reputation:

I honestly do not understand why so many people use .AutoFill when .FillDown or FillRight is more appropriate.

with Sheets(twtsumsheet)
    usedRows1 = .Cells(.Rows.Count, "A").End(xlUp).Row
    usedCols1 = .Cells(2, .columns.count).End(xltoleft).column
    .Range(.Cells(2, 31), .Cells(usedRows1, usedCols1)).filldown
end with

Upvotes: 2

Related Questions