Introduction
When writing Excel VBA, if you repeat similar processes, the code can become very long, making it difficult to manage and modify.
In this article, I will introduce how to rewrite “code that performs the same process (copying formulas) on multiple columns” into a concise loop.
What I Want to Achieve
- Copy formulas from row 2 to the last row for multiple columns (Column AH to Column BQ).
- Shorten and simplify the code using a loop structure.
Before: Redundant Code Processing One Column at a Time
My initial code was very long because I wrote the instruction for each column individually.
' Excerpt (Actually continues for 36 columns)
ws.Range("AH2").Copy Destination:=ws.Range("AH3:AH" & lastRow)
ws.Range("AI2").Copy Destination:=ws.Range("AI3:AI" & lastRow)
ws.Range("AJ2").Copy Destination:=ws.Range("AJ3:AJ" & lastRow)
' ... and so on
Although this method works, maintenance becomes harder as the number of columns increases.
After: Concise Code Rewritten with a For Loop
By using column numbers in a loop, the code becomes dramatically shorter and easier to understand.
Sub CopyFormulasDown()
Dim ws As Worksheet
Dim lastRow As Long
Dim col As Long
' Set worksheet
Set ws = ThisWorkbook.Sheets(1)
' Get the last row of Column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Loop from Column AH (Column 34) to Column BQ (Column 69)
If lastRow > 2 Then
For col = 34 To 69
ws.Cells(2, col).Copy Destination:=ws.Range(ws.Cells(3, col), ws.Cells(lastRow, col))
Next col
End If
End Sub
Key Points
ws.Cells(2, col): Refers to the cell in row 2 (the source formula).Destination:=...: Sets the copy destination from row 3 to the last row.34 To 69: These are the Excel column numbers (AH = 34, BQ = 69).
Tip: How to Find Column Numbers
You can check column numbers using the following logic:
- A = 1, B = 2, …, Z = 26
- AA = 27, AB = 28, …, AH = 34, …, BQ = 69
If you are unsure, you can verify it with a simple code like this:
MsgBox Columns("AH").Column ' Displays 34
Summary
In VBA, replacing repetitive syntax with loops significantly improves code readability and maintainability.
- For loops using column numbers are effective when the same process is repeated across columns.
- Using Column Numbers (1 to 16384) instead of letters allows for dynamic processing.
- It organizes the code and helps reduce errors.
This approach not only increases work efficiency but also makes the code more robust against future changes. Please give it a try.
