CODEWITHSUNDEEP
vbaexcelloops
bhawesh sah
bhawesh sah

Reputation: 103

How to loop through various columns in VBA

I am trying to come up with a macro to drag down the columns. I just recorded a macro that drags down the columns. Is there a way I can put a loop which drags down all the columns that I have?

I came up with something this this but it does not work. This code stops working just after dragging down the first column. 

Sub Macro3()
    '
    ' Macro3 Macro
    '
    For i = 1 To 100

        Selection.AutoFill Destination:=ActiveCell.Range("A1:A4")
        ActiveCell.Range("A1:A4").Select
    Next i
End Sub

How can I make it work on all the 100 columns that I have? I appreciate any help as I am trying to learn this.

Upvotes: 1

Views: 100

Answers (1)

user4039065
user4039065

Reputation:

Switch .AutoFill to .FillDown and do them all at once.

dim rws as long
rws = 4
with worksheets("sheet1")
    with .range(.cells(1, "A"), .cells(1, .columns.count).end(xltoleft))
        .resize(rws ,.columns.count).filldown
    end with
end with

Set rws to the total number of rows or figure out some other method of determining the total number of rows.

Upvotes: 2

Related Questions