It is most common when programming in VBA to need to loop down columns row by row. The reason for this is many computer systems produce reports in columns however how do you look across rows to search for data:
To loop across columns in an Excel spreadsheet is actually fairly straightforward to do provided that you use the cell reference method Worksheets(“SheetName”).Cells(Row, Column) rather than the range method to reference cells Worksheets(“SheetName”).Range(“A1”) which makes life significantly more difficult.
When using the cell method to reference cells normally most people will reference the columns using letters ie Worksheets(“SheetName”).Cells(1, “A”) to reference cell A1. However, in this method columns can also be referred to using numbers where column A = 1, column B = 2, column C = 3 etc. This allows a conventional loop to count through the columns in the same way that you would use it to count through Rose. Below is an example of the code that could be used;
Sub Loop_Across_Columns Dim Col As Integer For Col = 1 to 100 Step 1 Worksheets(“Sheet1”).Cells(1, Col) = Col Next Col End Sub
What The Code Does
This code will place the number 1 to 100 across the first row of sheet1
How To Loop Across Columns And Rows At The Same Time
It may also be necessary on some occasions to loop across both rows and columns simultaneously to search for a specific value within a range of cells. To do this is also relatively straightforward but it does require a loop on a loop.
Sub Loop_Across_Rows_Columns Dim Col As Integer Dim Row As Integer For Row = 1 to 100 Step 1 For Col = 1 to 100 Step 1 Worksheets(“Sheet1”).Cells(Row, Col) = Col Next Col Next Row End Sub
How to Make The Number Of Rows And Columns Looped Through Dynamic
In the examples shown in this article thus far the code has loop through a defined number of rows or columns. However, to make your code run faster it is best to ensure that you only loop through the rows and columns that you actually need to which can be done by making the loops being used dynamic.
To do this VBA code may be used to find the last column or row in your worksheet that is being used.
The command for find the last row and column is as follows:
How To Make Loops Dynamic
To make your loops dynamic you need to apply the last row and column commands to the loops that you already have which is straightforward. The differences between the previous loop and this one are highlighted in bold.
Sub Dynamic_Loop Dim Col As Integer Dim Row As Integer Dim LastRow As String Dim LastColumn As String LastRow = Worksheets("Sheet1").UsedRange.Rows.count LastColumn = Worksheets("Sheet1").UsedRange.Columns.count For Row = 1 to LastRow Step 1 For Col = 1 to LastColumn Step 1 Worksheets(“Sheet1”).Cells(Row, Col) = Col Next Col Next Row End Sub
How Do I Combine Two Macros? (With And Without VBA)
How Format A Cell In VBA? A Simple Way To Find The Code?
Can VBA Be Used In Google Sheets?
Can You Do An If Statement In Excel Based On Cell Formatting Such As Color? (It Is Possible Without VBA)