How to loop through various columns in VBA

Question

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.

OK, so you want to 'drag down' column A through column CV (100 columns or however many you have populated in row 1). What determines how far you 'drag them down'? Is it always rows 1:4? — user4039065
– user4039065, Commented Aug 6, 2017 at 21:04

user4039065 · Accepted Answer · 2017-08-06 21:10:09Z

2

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.

answered Aug 6, 2017 at 21:10

user4039065

Sign up to request clarification or add additional context in comments.

6 Comments

bhawesh sah Over a year ago

Thank you I really appreciate the help. Could you tell me where I was going wrong in my code and how to make it work. I was under the impression that I could record a macro for doing something and put a For loop. Am I going the wrong direction?

user4039065 Over a year ago

You performed the same .AutoFill operation on the same A1:A4 cells one hundred times. I consider using ActiveCell.Offset(0, 1).Select 'bad practice' so I did not correct your code. See How to avoid using Select in Excel VBA macros. No sense in correcting what IMHO is the wrong way to do things.

bhawesh sah Over a year ago

Thanks. I guess I need to do some reading now. How do I modify this code if I want to drag down every other column?

user4039065 Over a year ago

You would use a For Next loop with Step 2.

bhawesh sah Over a year ago

Thanks again. How would the code be modified if I want to do a vlookup first and then do the drag down?

|

Collectives™ on Stack Overflow

How to loop through various columns in VBA

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related