VBA to Loop through Rows of Table in Excel (11 Methods)

When we have a large dataset in our Excel workbook, sometimes it is convenient if we can loop through rows to extract the specific results that we want to get. Implementing VBA is the most effective, quickest, and safest method to run any operation in Excel. In this article, we will show you 11 different methods on how to loop through rows of a table in Excel with the VBA macro.

11 Methods with VBA to Loop through Rows of Table in Excel

Following this section, you will learn how to loop through rows of a table with 11 different methods, such as loop through rows until a blank cell, loop through rows until a specific value is found, loop through rows and colour a specific cell etc. with VBA macro in Excel.

Above is the example dataset that this article will follow to describe the methods.

1. Embed VBA to Loop through Each Cell in Every Row of a Table by Cell Reference Number

If you want to loop through each cell in every row of a table in your Excel worksheet and get the cell reference number as the return value, then follow the steps discussed below.

Steps:

In the beginning, press Alt + F11 on your keyboard or go to the tab Developer -> Visual Basic to open Visual Basic Editor.

Next, in the pop-up code window, from the menu bar, click Insert -> Module.

Then, copy the following code and paste it into the code window.

Sub LoopThroughRowsByRef()
LastRow = Range("B" & Rows.Count).End(xlUp).Row
FirstRow = 4
i = FirstRow
FirstColumn = 2
Do Until i > LastRow
    LastColumn = Cells(i, Columns.Count).End(xlToLeft).Column
    Count = FirstColumn
    Do Until Count > LastColumn
        MsgBox "Currently iterating cell " & Chr(Count + 64) & i
        Count = Count + 1
    Loop
    i = i + 1
Loop
End Sub

Your code is now ready to run.

Now, press F5 on your keyboard or from the menu bar select Run -> Run Sub/UserForm. You can also just click on the small Play icon in the sub-menu bar to run the macro.

Or to visually witness and compare the dataset and the result, you can save the code and go back to the worksheet of interest.
From there, you can click Macros from the Developer tab, select the Macro name, and then click Run.

After the successful code execution, look at the above gif to see the result. There will be a pop-up MsgBox showing you the cell reference number of every cell from each row from the table in your Excel sheet.

VBA Code Explanation

LastRow = Range("B" & Rows.Count).End(xlUp).Row

To get the last row number in the table by searching column B.

FirstRow = 4

Set row number 4, from where our data starts.

i = FirstRow

To loop from the first row.

FirstColumn = 2

Set column number 2, from where our data starts.

Do Until i > LastRow
    LastColumn = Cells(i, Columns.Count).End(xlToLeft).Column

Start looping through rows to get the last column number by evaluating the current row until the last row.

Count = FirstColumn
Do Until Count > LastColumn

Increment looping the column from the first row until the last row.

MsgBox "Currently iterating cell " & Chr(Count + 64) & i
        Count = Count + 1
    Loop
    i = i + 1
Loop

This piece of code works to process, increment after each iteration and display the result of the code.

2. Implement VBA to Loop through Each Cell in Every Row by Value

If you want to loop through each cell in every row of a table and throw the value residing in the cells as the return value, then this section will help you figure out how to do that with VBA Excel.

You can do that with the ListObject and with the DataBodyRange property of VBA. We will show you the macro code with both the object and the property.

2.1. With the ListObject

The steps to loop through each cell in every row of a table by cell value with the ListObject in VBA Excel are given below.

Steps:

Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Then, in the code window, copy the following code and paste it.

Sub LoopThroughRowsByList()
Dim iListRow As ListRow
Dim iCol As Range
    For Each iListRow In ActiveSheet.ListObjects("TblStudents").ListRows
        For Each iCol In iListRow.Range
            MsgBox iCol.Value
        Next iCol
    Next iListRow
End Sub

Your code is now ready to run.

After that, Run the macro as we showed you in the above section. The result is shown in the gif below.

There will be a pop-up MsgBox showing you the value carried by every cell from each row from the table in your Excel sheet.

VBA Code Explanation

Dim iListRow As ListRow
Dim iCol As Range

Define the variables.

For Each iListRow In ActiveSheet.ListObjects("TblStudents").ListRows
        For Each iCol In iListRow.Range
            MsgBox iCol.Value
        Next iCol
    Next iListRow

This piece of code first starts looping through rows in the table (“TblStudents” is our table name). Then enters columns for each row. After that, pass the value of the cell in MsgBox. Then go to the next column. After finishing iterating through all columns of one row, then it goes to the next row and continues the iteration process until the last row.

2.2. With the DataBodyRange Property

To be more specific with the extracted data from the table, you can utilize the DataBodyRange property of ListObject. DataBodyRange property will throw you the result containing the range from the list between the header row and the insert row.

The steps on how you can loop through each cell in every row of a table by cell value with the DataBodyRange in VBA Excel are given below.

Steps:

As shown before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Then, copy the following code and paste it into the code window.

Sub LoopThroughRowsByRange()
  Dim iRange As Range
  For Each iRange In ActiveSheet.ListObjects("TblStdnt").DataBodyRange
    MsgBox iRange.Value
  Next iRange
End Sub

Your code is now ready to run.

Later, Run the macro and look at the following gif to see the output.

There will be a pop-up MsgBox showing you the value carried by every cell from each row from the table in your Excel sheet.

VBA Code Explanation

Dim iRange As Range

Define the variable.

  For Each iRange In ActiveSheet.ListObjects("TblStdnt").DataBodyRange
    MsgBox iRange.Value
  Next iRange

This piece of code first starts looping through rows in the table (“TblStdnt” is our table name) and returns a range of values excluding the header row of the table. Then pass the value of the range in MsgBox. Then it goes to the next row to extract the range and continues the iteration process until the last row.

3. Apply VBA Macro to Iterate through Rows by Concatenating Columns in Excel

This section will show how to loop through rows in a table by concatenating the columns with the first column from your dataset in Excel.

For instance, for our dataset, first, we will iterate through John in Cell B5 and 101 in Cell C5 by concatenating them and then iterate through John in Cell B5 and 89 in Cell D5 by concatenating them from row 5.

Let us show you how you can do that with VBA macro in Excel.

Steps:

At first, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Secondly, in the code window, copy the following code and paste it.

Sub LoopThroughRowsByConcatenatingCol()
Dim iRange As Range
Dim iValue As String
With ActiveSheet.ListObjects("TblConcatenate")
For Each iRange In .DataBodyRange
If iRange.Column = .DataBodyRange.Column Then
iValue = iRange.Value
Else
MsgBox "Evaluating " & iValue & ": " & iRange.Value
End If
Next iRange
End With
End Sub

Your code is now ready to run.

Thirdly, Run the macro. Look at the gif below to see the result.

There will be a pop-up MsgBox showing you the concatenated value of the cells from the first and the second column (John in Cell B5 from Colum B and 101 in Cell C5 from Column C) and then the concatenated value of the cells from the first and the third column (John in Cell B5 from Colum B and 89 in Cell D5 from Column D) of row number 5 from your dataset. And this concatenation operation will keep continuing until it reaches the last row of the table.

VBA Code Explanation

Dim iRange As Range
Dim iValue As String

Define the variable.

With ActiveSheet.ListObjects("TblConcatenate")

Then the code selects the table from the active sheet (“TblConcatenate” in our table name).

For Each iRange In .DataBodyRange
If iRange.Column = .DataBodyRange.Column Then
iValue = iRange.Value

After that, starts iterating each row excluding the header of the column. If the iteration finds a match in the range between the column header and the rows then it stores the value in the iValue variable.

Else
MsgBox "Evaluating " & iValue & ": " & iRange.Value
End If
Next iRange
End With

If the above condition doesn’t get fulfilled, then the code throws the value in the MsgBox and finishes the condition. After that, it moves on to looping in another range and continues iterating until the end row. Once it reaches the end row, the macro ends the code execution.

4. Embed Macro to Iterate through Rows by Concatenating All Columns in a Table in Excel

In this section, we will learn how to concatenate all the columns that each row holds in the dataset with VBA macro in Excel.

The steps to execute that are shown below.

Steps:

Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Then, copy the following code and paste it into the code window.

Sub LoopThroughRowsByConcatenatingAllCol()
Dim iObj As Excel.ListObject
Dim iSheet As Excel.Worksheet
Dim iRow As Excel.ListRow
Dim iCol As Excel.ListColumn
Dim iResult As String
Set iSheet = ThisWorkbook.Worksheets("ConcatenatingAllCol")
Set iObj = iSheet.ListObjects("TblConcatenateAll")
For Each iRow In iObj.ListRows
For Each iCol In iObj.ListColumns
iResult = iResult & " " & Intersect(iRow.Range, iObj.ListColumns(iCol.Name).Range).Value
Next iCol
MsgBox iResult
iResult = ""
Next iRow
End Sub

Your code is now ready to run.

Next, Run the macro code.

As you can see from the above gif is that there is a pop-up MsgBox showing you the concatenated value of all the columns residing in every row from the table of your Excel worksheet.

VBA Code Explanation

Dim iObj As Excel.ListObject
Dim iSheet As Excel.Worksheet
Dim iRow As Excel.ListRow
Dim iCol As Excel.ListColumn
Dim iResult As String

Define the variables.

Set iSheet = ThisWorkbook.Worksheets("ConcatenatingAllCol")

Set the sheet name that we will work with (“ConcatenatingAllCol” is the sheet name in the workbook).

Set iObj = iSheet.ListObjects("TblConcatenateAll")

Define the table name that we will work with (“TblConcatenateAll” is the table name in our dataset).

For Each iRow In iObj.ListRows

Starts iteration through each row of the table.

For Each iCol In iObj.ListColumns

Starts iteration through each column of each row of the table.

iResult = iResult & " " & Intersect(iRow.Range, iObj.ListColumns(iCol.Name).Range).Value
Next iCol
MsgBox iResult
iResult = ""
Next iRow

Store the result by intersecting the values each column of each row carries. After scanning through all the columns residing in each row, it passed the result in the MsgBox. Then again starts looping in the next row and continues looping until it reaches the last row of the table.

5. Stop Iteration If Value Found by Looping through Rows of a Table with VBA Macro

Suppose you want to loop through the rows of your table has and stop the looping when it finds a specific value. You can do that with just a simple macro code.

Let’s learn how to do that VBA in Excel.

Steps:

At first, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Then, in the code window, copy the following code and paste it.

Sub LoopThroughRowsForValue()
Dim iData As Range
For Each iData In Range("1:15")
If iData.Value = "Edge" Then
MsgBox "Edge is found at " & iData.Address
End If
Next iData
End Sub

Your code is now ready to run.

Later, Run the macro.
It will start the loop and stop it when it finds the specific value (“Edge”) in the range and throws the result in the MsgBox.

As you can see from the image above, there is a pop-up MsgBox showing you the cell address, $B$10, where we found the specified value, “Edge”.

VBA Code Explanation

Dim iData As Range

Define the variable.

For Each iData In Range("1:15")
If iData.Value = "Edge" Then
MsgBox "Edge is found at " & iData.Address
End If
Next iData

This piece of code is here for looping through rows from 1 to 15. If it finds the specific word “Edge” then it passes the result with the cell address that holds the word. It continues to do this until it finishes scanning through all the data from rows 1 to 15 in searching for the word.

6. VBA to Loop through Each Row and Color a Specific Value in Excel

What if you don’t want to throw the cell address of the specified value in the MsgBox? You might want to color the cell that carries the value you are looking for.

Let’s learn how to do that with VBA macro.

Steps:

Same way as before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Then, copy the following code and paste it into the code window.

Sub LoopThroughRowsAndColor()
Dim iData As Range
For Each iData In Range("1:15")
If iData.Value = "Edge" Then
iData.Interior.ColorIndex = 8
End If
Next iData
End Sub

Your code is now ready to run.

After that, Run the macro.
It will start the loop and stop it when it finds the specific value (“Edge”) in the range and color the cell with the ColourIndex that you provided in the code.

As you can see from the image above, Cell B10, where we found the specified value, “Edge” is coloured after the code execution.

VBA Code Explanation

Dim iData As Range

Define the variable.

For Each iData In Range("1:15")
If iData.Value = "Edge" Then
iData.Interior.ColorIndex = 8
End If
Next iData

This piece of code is here for looping through rows from 1 to 15. If it finds the specific word “Edge” then it colors the cell that holds the word. It continues to do this until it finishes scanning through all the data from rows 1 to 15 in searching for the word.

7. Implement VBA to Loop through Each Row and Color Every Odd Row in Excel

From the previous section, we have learned how to color a cell that carries a specific value. In this section, we will learn how to loop through each row of a table and color every odd row with VBA macro in Excel.

Steps to execute the process are described below.

Steps:

As shown before, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Then, copy the following code and paste it into the code window.

Sub LoopThroughRowsAndColorOddRows()
Dim iRow As Long
With Range("B4").CurrentRegion
For iRow = 2 To .Rows.Count
If iRow / 2 = Int(iRow / 2) Then
.Rows(iRow).Interior.ColorIndex = 8
End If
Next
End With
End Sub

Your code is now ready to run.

Now, Run the macro and look at the following image to see the output.

All the odd-numbered rows are colored after looping through all the rows residing in the table of the worksheet.

VBA Code Explanation

Dim iRow As Long

Define the variable.

With Range("B4").CurrentRegion

Define the range that we will work with.

For iRow = 2 To .Rows.Count
If iRow / 2 = Int(iRow / 2) Then
.Rows(iRow).Interior.ColorIndex = 8
End If
Next
End With

This part of the code refers to the iteration through all the rows, starting from the next row of the current row, B4. If the mod of dividing the row numbers by 2 is equal to the returned row number stored in an integer type, then this code colours all the rows that are extracted by the calculation with the Color Index provided in the code. It keeps moving through all the rows until it reaches the end of the range.

8. Implement VBA to Loop through Rows and Color Every Even Rows in Excel

In the previous section, we have learned how to color every odd row of a table. In this section, we will learn how to loop through each row of a table and color every even row with VBA macro in Excel.

Steps to execute the process are discussed below.

Steps:

Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Then, copy the following code and paste it into the code window.

Sub LoopThroughRowsAndColorEvenRows()
Dim iRow As Long
With Range("B4").CurrentRegion
For iRow = 3 To .Rows.Count Step 2
.Rows(iRow).Interior.ColorIndex = 8
Next
End With
End Sub

Your code is now ready to run.

Next, Run the macro and look at the following image to see the result.

All the even-numbered rows are colored after looping through all the rows residing in the table of the worksheet.

VBA Code Explanation

Dim iRow As Long

Define the variable.

With Range("B4").CurrentRegion

Define the range that we will work with.

For iRow = 3 To .Rows.Count Step 2
.Rows(iRow).Interior.ColorIndex = 8
Next
End With

This piece of code starts iterating after the three rows from the current row, B4. It colors it first then increments the row count by 2 and keeps coloring it until it reaches the last row of the dataset.

9. Apply Macro to Iterate through Rows Until Blank Cell in Excel

If you want your code to work like it will loop through all the rows of the table and stop when it reaches a blank cell, then this section is for you. You can execute that task both with the For Next loop and the Do-Until Loop in Excel VBA.

9.1. With For Loop

Steps to loop through rows in a table until a blank cell with For Loop in VBA Excel are given below.

Steps:

At first, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
After that, copy the following code and paste it into the code window.

Sub ForLoopThroughRowsUntilBlank()
Dim x As Integer
Application.ScreenUpdating = False
NumRows = Range("B4", Range("B4").End(xlDown)).Rows.Count
Range("B4").Select
For x = 1 To NumRows
ActiveCell.Offset(1, 0).Select
Next
Application.ScreenUpdating = True
End Sub

Your code is now ready to run.

Then, Run the macro and the result is shown in the gif below.

After running the macro, it started looping through all the rows in the table and once it reached the blank cell, Cell B8, it stopped the iteration.

VBA Code Explanation

Dim x As Integer

Define the variable.

Application.ScreenUpdating = False

Turn off the screen updating event.

NumRows = Range("B4", Range("B4").End(xlDown)).Rows.Count

Store all the rows starting from Cell B4 till the last one.

Range("B4").Select

Select Cell B4.

For x = 1 To NumRows
ActiveCell.Offset(1, 0).Select
Next

This piece of code starts looping through all the rows. When it finds an empty cell in a row then selects it and continues scanning the rows until it reaches the end.

Application.ScreenUpdating = True

Turn on the screen updating event.

9.2. With Do-Until Loop

Steps to loop through rows until a blank cell with Do-Until loop in VBA are given below.

Steps:

Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Then, copy the following code and paste it into the code window.

Sub DoUntilLoopThroughRowsUntilBlank()
Range("B4").Select
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Your code is now ready to run.

Later, Run the macro. The result is shown in the following gif.

After running the macro, it started looping through all the rows in the table and once it reached the blank cell, Cell B8, it stopped the iteration.

VBA Code Explanation

Range("B4").Select

Select the cell that we will work from.

Do Until IsEmpty(ActiveCell)

Starts and continues looping until an empty cell is found.

ActiveCell.Offset(1, 0).Select
Loop

When an empty cell is found in a row then select it and stop the iteration.

10. VBA Macro to Iterate through Rows Until Multiple Blank Cells in Excel

In the previous section, you have learned how to stop the loop when an empty cell is found. But what if you don’t want to stop the iteration until multiple blank cells are found instead of just one.

The steps to loop through rows until multiple blank cells are found in a table with VBA Excel are shown below.

Steps:

Firstly, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Then, copy the following code and paste it into the code window.

Sub LoopThroughRowsUntilMultipleBlank()
Range("B4").Select
Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0))
ActiveCell.Offset(2, 0).Select
Loop
End Sub

Your code is now ready to run.

Now, Run the macro and see the following gif for the output.

After running the macro, it didn’t stop on the first blank cell, Cell B8. It stopped when it found two consecutive blank cells on Cell B16.

VBA Code Explanation

Range("B4").Select

Select the cell that we will work from.

Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0))

Starts and continues looping until two consecutive empty cells are found.

ActiveCell.Offset(2, 0).Select
Loop

When two consecutive empty cells are found, then select it and stop the iteration.

11. Embed VBA to Loop through Rows by Concatenating All Columns Until Blank in Excel

This section will show you how to loop through all the rows in a table and concatenate all the columns until a blank cell is found with VBA Excel.

Let’s learn how to do that with VBA macro in Excel.

Steps:

At first, open Visual Basic Editor from the Developer tab and Insert a Module in the code window.
Then, copy the following code and paste it into the code window.

Sub ConcatenatingAllColUntilBlank()
Dim iSheet As Worksheet
Dim iValue As Variant
Dim iResult As String
Set iSheet = Sheets("ConcatenatingAllColUntilBlank")
iValue = Range("B4").CurrentRegion
For i = 2 To UBound(iValue, 1)
iResult = ""
For J = 1 To UBound(iValue, 2)
iResult = IIf(iResult = "", iValue(i, J), iResult & " " & iValue(i, J))
Next J
MsgBox iResult
Next i
End Sub

Your code is now ready to run.

Later, Run the macro and see the following gif for the result.

As you can see from the above gif is that there is a pop-up MsgBox showing you the concatenated value of all the columns residing in every row from the table of your Excel worksheet. But it stopped once it reached the blank cell.

VBA Code Explanation

Dim iSheet As Worksheet
Dim iValue As Variant
Dim iResult As String

Define the variables.

Set iSheet = Sheets("ConcatenatingAllColUntilBlank")

Set the sheet name that we will work with (“ConcatenatingAllColUntilBlank” is the sheet name in the workbook).

iValue = Range("B4").CurrentRegion

Define the range that we will work with.

For i = 2 To UBound(iValue, 1)
iResult = ""
For J = 1 To UBound(iValue, 2)
iResult = IIf(iResult = "", iValue(i, J), iResult & " " & iValue(i, J))
Next J
MsgBox iResult
Next i

This piece of code starts the loop with the array. It continues to loop until it returns the largest subscript of the array and the lower bound of the first dimension. Then it enters the iteration of extracting the lower bound of the second dimension. After that, it passes all the extracted values in the iResult variable by concatenating them and throwing the result in the MsgBox. It continues to do that until it finds an empty cell.

Download Workbook

You can download the free practice Excel workbook from here.

Loop through Rows of Table with VBA.xlsm

Conclusion

To conclude, this article showed you 11 effective methods on how to loop through rows of a table in Excel with the VBA macro. I hope this article has been very beneficial to you. Feel free to ask any questions regarding the topic.

Get FREE Advanced Excel Exercises with Solutions!

6 Comments

Mike M Jul 26, 2022 at 5:20 AM

Hello, very helpful article. I have a specific real-world example for which I’m having trouble getting my macros to work properly. You could probably figure this out in a few minutes, but I’m pretty rusty with my coding skills. I would compensate you for your time helping out with this if you’re open to it. Thanks!
- Reply
  Mursalin Oct 10, 2022 at 1:55 PM
  
  Hi MIKE M,
  
  Sorry for the late reply. I am replying to you on behalf of Exceldemy. You can mail your problem to [email protected]. Our team will take a look and try to give a solution to your problem.
  
  Thanks!
Chris Oct 9, 2022 at 5:21 PM

Can you post examples that can actually be useful in the real world? You cannot DO anything with message boxes and you can color cells just fine with the built in conditional formatting. Can you post how to loop through a row of data, identify data, copy that data and paste it? This is great but useless.
- Reply
  Mursalin Oct 10, 2022 at 11:19 PM
  
  Hi CHRIS,
  
  Thanks for your comment. I am replying to you on behalf of Exceldemy. We can use VBA to loop through rows of data and extract the desired data.
  For example, we will use a VBA to look for the marks of a specific student in the datasheet, copy them and paste the marks into the reportsheet. You can see the marks of all students in the picture below:
  
  We will copy the marks of a specific student and paste them into the reportsheet.
  
  For that purpose, you can use the code below:
  
  Sub Loop_Through_and_Extract_Data()
  Dim datasheet As Worksheet 'From where data will be copied
  Dim reportsheet As Worksheet 'where data will be pasted
  Dim StudentName As String
  Dim finalrow As Integer
  Dim i As Integer 'row counter
  'set variables
  Set datasheet = Sheet1
  Set reportsheet = Sheet3
  StudentName = reportsheet.Range("F5").Value 'Cell F5 of reportsheet contains the student name
  'clear old data
  reportsheet.Range("B5:D200").ClearContents
  'go to datasheet and start searching and copying
  datasheet.Select
  finalrow = Cells(Rows.Count, 1).End(xlUp).Row
  'loop through rows to find matching records
  For i = 2 To finalrow
  If Cells(i, 1) = StudentName Then 'if the name matches, then copy
  Range(Cells(i, 1), Cells(i, 3)).Copy 'copy columns 1 to 3 (A to C)
  reportsheet.Select 'go to reportsheet
  Range("B200").End(xlUp).Offset(1, 0).PasteSpecial xlPasteAll 'paste values in the reportsheet
  datasheet.Select 'go back to datasheet and continue searching
  End If
  Next i
  reportsheet.Select
  Range("A1").Select
  End Sub
  
  I have also attached the Excel file below:
  Loop Through Rows.xlsm
  I hope this will help to solve your problem. Please let us know if you have other queries.
  Thanks!
David Oct 31, 2023 at 12:10 AM

Thanks for all the code examples. A couple of pointers if I may.

1. Prefix you variables with something helpful:
Prefixing variables is a good idea, it straight away shows the reader it is a variable, it might tell you something about the variable (eg what type it is), it makes it less likely to be a reserved word (eg you can have a variable called “iRange” but not “range”)

Unfortunately, you have prefixed ALL your variables with “i”, for example above you have:
Dim iSheet As Excel.Worksheet
Dim iRow As Excel.ListRow
Dim iCol As Excel.ListColumn
Dim iResult As String

Whereas something like this would be much more helpful:
Dim wsSheet As Excel.Worksheet
Dim lrRow As Excel.ListRow
Dim lcCol As Excel.ListColumn
Dim sResult As String

It also gives the added benefit in that I know the type if each variable (ie sRESULT is a string), simple types (string, long double…) have a single letter prefix and objects (worksheet, ListRow, ListColumn) have a double prefix, plus it means I give variables a helpful name but still know what they are eg:
Dim wsConCat as Excel.Worksheet

2. Indenting code makes it much easier to read, particularly if you nested loops, for example when you have a piece of code like this:

MsgBox “Currently iterating cell ” & Chr(Count + 64) & i
Count = Count + 1
Loop
i = i + 1
Loop

It is so much more readable if you add indenting:
MsgBox “Currently iterating cell ” & Chr(Count + 64) & i
Count = Count + 1
Loop
i = i + 1
Loop
- Reply
  Shamima Sultana Oct 31, 2023 at 9:02 AM
  
  Hello David,
  
  Thanks for your valuable suggestions.
  
  Regards
  ExcelDemy

VBA to Loop through Rows of Table in Excel (11 Methods)

11 Methods with VBA to Loop through Rows of Table in Excel

1. Embed VBA to Loop through Each Cell in Every Row of a Table by Cell Reference Number

2. Implement VBA to Loop through Each Cell in Every Row by Value

2.1. With the ListObject

2.2. With the DataBodyRange Property

3. Apply VBA Macro to Iterate through Rows by Concatenating Columns in Excel

4. Embed Macro to Iterate through Rows by Concatenating All Columns in a Table in Excel

5. Stop Iteration If Value Found by Looping through Rows of a Table with VBA Macro

6. VBA to Loop through Each Row and Color a Specific Value in Excel

7. Implement VBA to Loop through Each Row and Color Every Odd Row in Excel

8. Implement VBA to Loop through Rows and Color Every Even Rows in Excel

9. Apply Macro to Iterate through Rows Until Blank Cell in Excel

9.1. With For Loop

9.2. With Do-Until Loop

10. VBA Macro to Iterate through Rows Until Multiple Blank Cells in Excel

11. Embed VBA to Loop through Rows by Concatenating All Columns Until Blank in Excel

Conclusion

Leave a reply Cancel reply