How To Loop Across Columns In VBA -

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:

Row: Worksheets(“Sheetname”).UsedRange.Rows.count

Columns: Worksheets(“Sheetname”).UsedRange.Columns.count

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