Forum Discussion
help making a loop (to automate table formatting)
I am using excel VBA to automate table formatting. I have 24 tables to automate. I wrote the code with a macro then made some changes. It works fine (it autofills data on A3, E3, and F3, based on the last row in filled in column B. The problem is I need to modify the code for Tables 2 though 24. I could do this manually, but its a pain!! Is there a way to set A,B,E, an F as variables, and loop though this 23 more times by adding 18 letters each loop?
Greg
***CODE FOR TABLE 1
Range("A3").Select
Selection.autofill Destination:=Range("A3:A" & Range("B" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("E3").Select
Selection.autofill Destination:=Range("E3:E" & Range("B" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("F3").Select
Selection.autofill Destination:=Range("F3:F" & Range("B" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
***CODE FOR TABLE 2
Range("S3").Select
Selection.autofill Destination:=Range("S3:S" & Range("T" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("W3").Select
Selection.autofill Destination:=Range("W3:W" & Range("T" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
Range("X3").Select
Selection.autofill Destination:=Range("X3:X" & Range("T" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
****and then 22 more times
This should do what you are trying to achieve....
Sub FormatTables() Dim c As Long Dim lr As Long Dim i As Long Application.ScreenUpdating = False c = 1 For i = 1 To 24 lr = Cells(Rows.Count, c + 1).End(xlUp).Row If lr > 2 Then Cells(3, c).AutoFill Destination:=Range(Cells(3, c), Cells(lr, c)) Cells(3, c + 4).AutoFill Destination:=Range(Cells(3, c + 4), Cells(lr, c + 4)) Cells(3, c + 5).AutoFill Destination:=Range(Cells(3, c + 5), Cells(lr, c + 5)) End If c = c + 18 Next i Application.ScreenUpdating = True End Sub
7 Replies
This should do what you are trying to achieve....
Sub FormatTables() Dim c As Long Dim lr As Long Dim i As Long Application.ScreenUpdating = False c = 1 For i = 1 To 24 lr = Cells(Rows.Count, c + 1).End(xlUp).Row If lr > 2 Then Cells(3, c).AutoFill Destination:=Range(Cells(3, c), Cells(lr, c)) Cells(3, c + 4).AutoFill Destination:=Range(Cells(3, c + 4), Cells(lr, c + 4)) Cells(3, c + 5).AutoFill Destination:=Range(Cells(3, c + 5), Cells(lr, c + 5)) End If c = c + 18 Next i Application.ScreenUpdating = True End SubAMAZING!!! I spent, like, hours yesterday entering all of the cells names in manually...and this does it perfectly! Even better, there are other actions that I need to automate on the same data set as well. I'm hoping that I can just modify this sub and use it in other ways as well.
Thank you so much!!!!
Greg
You're welcome Greg! Glad it worked as desired.
Thanks for the feedback.
Please take a minute to accept my post with the proposed solution as an Answer and hit the like button. 🙂
Subodh
