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? — , Aug 06 '17 at 21:04

score 2 · Answer 1 · answered Aug 06 '17 at 21:10

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 06 '17 at 21:10

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? – bhawesh sah Aug 06 '17 at 21:18
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](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). No sense in correcting what IMHO is the wrong way to do things. – Aug 06 '17 at 21:21
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? – bhawesh sah Aug 06 '17 at 21:59
You would use a For Next loop with Step 2. – Aug 06 '17 at 22:01
Thanks again. How would the code be modified if I want to do a vlookup first and then do the drag down? – bhawesh sah Aug 06 '17 at 22:24
You seem to think that this is a game of 20 questions; it is **not**. If you have achieved the goal of your original question but plan to make further modifications and are having difficulty then ask a new question using your own original code (not cut and pasted from my response above). – Aug 06 '17 at 22:27

How to loop through various columns in VBA

1 Answers1