Master Advanced Excel & VBA

Learn Excel formulas, data analysis, dashboards, VBA programming, and business automation through structured practical lessons.

COURSE SYLLABUS
1. Excel Basics
Introduction to Advanced Excel Workbook, Worksheets, Rows & Columns Data Types & Cell Formatting Cell References & Keyboard Shortcuts
2. Advanced Excel Formulas
Advanced Formulas Introduction IF, AND, OR & IFERROR Excel Tables XLOOKUP INDEX & MATCH SUMIFS & COUNTIFS
3. Pivot Analytics
Pivot Analytics Introduction Pivot Tables Calculated Fields & Items Timelines & Slicers Text Functions & Data Cleaning
4. Excel Dashboards
Interactive Dashboards Introduction Data Validation Conditional Formatting Combo Charts Sparklines
5. Excel Data Tools
Data Integration Introduction Importing Text Files Power Query Worksheet Protection Formula Auditing
6. VBA Fundamentals
VBA Fundamentals Introduction Macro Recorder VBE Workspace VBA Objects & Properties Variables & Data Types MsgBox & InputBox If...Then...Else Select Case Loops & Dynamic Ranges
7. VBA Automation
VBA Automation Introduction Subroutines & Arguments User-Defined Functions Workbook Events Worksheet Events Form Controls & Buttons
8. VBA UserForms
UserForms Introduction UserForm Design Toolbox Controls Input Validation CRUD Operations with UserForms
9. VBA Optimization & Security
Optimization Introduction Error Handling Debugging Tools Performance Optimization VBA Project Security
10. Corporate Capstone Project
Capstone Project Introduction Data Consolidation Data Cleaning & Analysis Automated PDF Reports Dashboard Automation
11. Excel Practice & Assignments
Practice Introduction Excel Formula Exercises Pivot Table Exercises Dashboard Exercises VBA Programming Exercises Final Assignments

Loops & Dynamic Ranges in VBA

Learn how to repeat VBA statements, process Excel rows automatically, find the last used row, work with dynamic ranges, and automate repetitive spreadsheet tasks.

1. What Is a Loop?

A loop is a programming structure that executes a block of statements repeatedly. Instead of writing the same statement for every Excel row, you can use a loop to process each row automatically.

For example, suppose an Excel worksheet contains 1,000 employees. A loop can calculate each employee's bonus without writing 1,000 separate instructions.

Start loop Condition Satisfied? Yes Execute statements Process current item Repeat No Exit loop A loop repeats until its stopping condition is reached.
Key idea: Loops reduce repetitive code and make Excel automation scalable.

2. Types of Loops in VBA

Loop When to Use It
For...Next When the number of iterations is known.
For Each...Next When processing each object or item in a collection.
Do While...Loop When repeating while a condition is True.
Do Until...Loop When repeating until a condition becomes True.
Do...Loop While When the loop must execute at least once before checking the condition.
Do...Loop Until When the loop must execute at least once and then stop when the condition becomes True.

3. For...Next Loop

Use For...Next when you know the starting value and ending value of the loop counter.

Syntax

For counter = startValue To endValue
    ' Statements
Next counter

Example 1: Print numbers from 1 to 5

Sub PrintNumbers()

    Dim i As Long

    For i = 1 To 5
        Debug.Print i
    Next i

End Sub

Debug.Print displays the values in the Immediate window of the VBA editor.

Iteration Value of i Action
11Print 1
22Print 2
33Print 3
44Print 4
55Print 5

Example 2: Step keyword

Sub PrintEvenNumbers()

    Dim i As Long

    For i = 2 To 10 Step 2
        Debug.Print i
    Next i

End Sub
Output: 2, 4, 6, 8, 10

Step 2 increases the counter by 2 each time. A negative Step can be used to count backwards.

4. For Each...Next Loop

Use For Each...Next to process every item in a collection, such as every cell in a range or every worksheet in a workbook.

Example 3: Process cells in a range

Sub HighlightValues()

    Dim cell As Range

    For Each cell In ThisWorkbook.Worksheets("Sheet1").Range("A1:A5")

        If IsNumeric(cell.Value) Then
            If Len(cell.Value) > 0 Then
                If cell.Value >= 50 Then
                    cell.Interior.Color = RGB(198, 239, 206)
                End If
            End If
        End If

    Next cell

End Sub

This macro checks cells A1:A5 and highlights numeric values greater than or equal to 50.

Note: This example assumes that the worksheet is named Sheet1. When looping through cells, check for errors and non-numeric values before performing numeric comparisons.

5. Do While...Loop

A Do While loop repeats as long as its condition is True. When the condition is False at the beginning, the loop body does not execute.

Syntax

Do While condition
    ' Statements
Loop

Example 4: Print numbers from 1 to 5

Sub DoWhileExample()

    Dim i As Long

    i = 1

    Do While i <= 5
        Debug.Print i
        i = i + 1
    Loop

End Sub

The counter must be updated. Otherwise, if the condition remains True, the loop may continue indefinitely.

6. Do Until...Loop

A Do Until loop repeats while the condition is False and stops when the condition becomes True.

Syntax

Do Until condition
    ' Statements
Loop

Example 5: Print numbers until 5

Sub DoUntilExample()

    Dim i As Long

    i = 1

    Do Until i > 5
        Debug.Print i
        i = i + 1
    Loop

End Sub
Difference: Do While condition continues while the condition is True. Do Until condition continues while the condition is False.

7. Do...Loop While and Do...Loop Until

These forms check the condition at the end of the loop. Therefore, the statements inside the loop execute at least once.

Example 6: Do...Loop While

Sub PostTestWhile()

    Dim i As Long

    i = 1

    Do
        Debug.Print i
        i = i + 1
    Loop While i <= 5

End Sub

Example 7: Do...Loop Until

Sub PostTestUntil()

    Dim i As Long

    i = 1

    Do
        Debug.Print i
        i = i + 1
    Loop Until i > 5

End Sub
Important: A post-test loop executes its body at least once, even if the stopping condition is already met.

8. Exit For and Exit Do

Use Exit For to leave a For loop early and Exit Do to leave a Do loop early.

Example 8: Stop when a value is found

Sub FindValue()

    Dim i As Long
    Dim target As Long

    target = 7

    For i = 1 To 10

        If i = target Then
            Debug.Print "Value found: " & i
            Exit For
        End If

    Next i

End Sub

Once i becomes 7, the macro exits the loop without checking the remaining values.

9. Nested Loops

A nested loop is a loop placed inside another loop. The inner loop completes its iterations for each iteration of the outer loop.

Example 9: Multiplication table

Sub MultiplicationTable()

    Dim i As Long
    Dim j As Long

    For i = 1 To 3

        For j = 1 To 3
            Debug.Print i & " x " & j & " = " & (i * j)
        Next j

    Next i

End Sub

The outer loop selects the first number, and the inner loop multiplies it by 1, 2, and 3.

10. What Is a Dynamic Range?

A fixed range refers to a specific group of cells, such as A2:A10. A dynamic range adjusts according to the amount of data present in the worksheet.

Fixed Range Dynamic Range {#each [0, 1, 2, 3, 4] as n} {n < 3 ? "Data row " + (n + 1) : "Empty row"} {n < 3 ? "Data row " + (n + 1) : "Not included"} {/each} A2:A10 always refers to these rows. Range size is calculated from the data.

Dynamic ranges are useful when the number of rows changes whenever new data is added to the worksheet.

11. Find the Last Used Row

One common method uses the End(xlUp) technique. Starting from the bottom of a worksheet, VBA moves upward to find the last non-empty cell in a specified column.

Syntax

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
  • ws.Rows.Count returns the worksheet's row count.
  • ws.Cells(ws.Rows.Count, "A") refers to the bottom cell in column A.
  • End(xlUp) moves upward to the nearest non-empty cell.
  • .Row returns that cell's row number.

Example 10: Find the last row

Option Explicit

Sub FindLastRow()

    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    If Application.WorksheetFunction.CountA(ws.Columns("A")) = 0 Then
        MsgBox "Column A contains no data.", vbInformation
        Exit Sub
    End If

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    MsgBox "Last used row in column A: " & lastRow

End Sub
Important: The last row is determined by column A only. If column A contains gaps or is empty while other columns contain data, use a method suited to your worksheet's layout.

12. Use For...Next with a Dynamic Range

Once the last row is known, the loop can process every row between the first data row and the last used row.

Example 11: Calculate total price

Assume column B contains quantity, column C contains unit price, and column D should contain the total.

A: Product B: Quantity C: Unit Price D: Total
Mouse 2 500 1000
Keyboard 3 750 2250
Monitor 1 8000 8000
Option Explicit

Sub CalculateProductTotals()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    If Application.WorksheetFunction.CountA(ws.Columns("A")) = 0 Then
        MsgBox "No products found in column A.", vbInformation
        Exit Sub
    End If

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "No product rows to process.", vbInformation
        Exit Sub
    End If

    For i = 2 To lastRow

        If IsNumeric(ws.Cells(i, "B").Value) And _
           IsNumeric(ws.Cells(i, "C").Value) And _
           Len(ws.Cells(i, "B").Value) > 0 And _
           Len(ws.Cells(i, "C").Value) > 0 Then

            ws.Cells(i, "D").Value = _
                CDbl(ws.Cells(i, "B").Value) * _
                CDbl(ws.Cells(i, "C").Value)

        Else
            ws.Cells(i, "D").Value = "Check Input"
        End If

    Next i

    MsgBox "Product totals calculated.", vbInformation

End Sub

The macro automatically adjusts to the number of product rows, so new products can be added without changing the loop's ending row manually.

13. Practical Example: Fill Blank Cells

Suppose a worksheet contains missing department labels. You can use a loop to fill blank cells with a default value.

Example 12: Fill blank department cells

Option Explicit

Sub FillBlankDepartments()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    If Application.WorksheetFunction.CountA(ws.Columns("A")) = 0 Then
        MsgBox "No employee data found.", vbInformation
        Exit Sub
    End If

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    For i = 2 To lastRow

        If Len(Trim$(CStr(ws.Cells(i, "B").Value))) = 0 Then
            ws.Cells(i, "B").Value = "Not Assigned"
        End If

    Next i

    MsgBox "Blank department cells have been checked.", vbInformation

End Sub

This macro assumes employee records are identified in column A and department values are stored in column B.

14. Use For Each with a Dynamic Range

You can combine the last-row technique with For Each to process all populated data cells in a calculated range.

Example 13: Highlight values above 1,000

Option Explicit

Sub HighlightLargeAmounts()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim cell As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    If Application.WorksheetFunction.CountA(ws.Columns("C")) = 0 Then
        MsgBox "No amounts found in column C.", vbInformation
        Exit Sub
    End If

    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    For Each cell In ws.Range("C2:C" & lastRow)

        If Not IsError(cell.Value) Then
            If Len(cell.Value) > 0 And IsNumeric(cell.Value) Then
                If CDbl(cell.Value) > 1000 Then
                    cell.Interior.Color = RGB(255, 235, 156)
                End If
            End If
        End If

    Next cell

    MsgBox "Highlighting completed.", vbInformation

End Sub
Tip: For Each is often convenient when working directly with Range objects. Use an indexed For...Next loop when you need to refer to several columns in the current row.

15. Loop Through All Worksheets

A workbook can contain multiple worksheets. Use For Each to perform the same operation on every worksheet.

Example 14: Display worksheet names

Sub ListWorksheetNames()

    Dim ws As Worksheet

    For Each ws In ThisWorkbook.Worksheets
        Debug.Print ws.Name
    Next ws

End Sub

Open the Immediate window in the VBA editor to see the worksheet names printed by this macro.

16. Improve Loop Performance

Loops over large worksheets can be slow if the macro repeatedly reads and writes individual cells. For larger datasets, consider reading data into an array, processing it in memory, and writing the results back in one operation.

Example 15: Process values using an array

Option Explicit

Sub CalculateTotalsWithArray()

    Dim ws As Worksheet
    Dim lastRow As Long
    Dim inputData As Variant
    Dim outputData() As Variant
    Dim i As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    If Application.WorksheetFunction.CountA(ws.Columns("A")) = 0 Then
        MsgBox "No product data found.", vbInformation
        Exit Sub
    End If

    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "No product rows to process.", vbInformation
        Exit Sub
    End If

    ' Read quantity and unit price into memory.
    inputData = ws.Range("B2:C" & lastRow).Value2

    ReDim outputData(1 To UBound(inputData, 1), 1 To 1)

    For i = 1 To UBound(inputData, 1)

        If Not IsError(inputData(i, 1)) And _
           Not IsError(inputData(i, 2)) Then

            If IsNumeric(inputData(i, 1)) And _
               IsNumeric(inputData(i, 2)) And _
               Len(CStr(inputData(i, 1))) > 0 And _
               Len(CStr(inputData(i, 2))) > 0 Then

                outputData(i, 1) = _
                    CDbl(inputData(i, 1)) * CDbl(inputData(i, 2))

            Else
                outputData(i, 1) = "Check Input"
            End If

        Else
            outputData(i, 1) = "Check Input"
        End If

    Next i

    ' Write all results to column D in one operation.
    ws.Range("D2:D" & lastRow).Value = outputData

    MsgBox "Array calculation completed.", vbInformation

End Sub
Why use an array? Reading and writing worksheet data in batches can reduce repeated communication with Excel and improve performance on large datasets.

17. Different Ways to Find the Last Row

Method Use Consideration
End(xlUp) Finds the last non-empty cell in a column. Choose a column that reliably identifies the data rows.
CurrentRegion Finds a connected data region around a starting cell. Blank rows or columns can split the region.
UsedRange Returns the worksheet's used range. Formatting in otherwise empty cells can affect its size.
Find Can search for the last cell containing data across a worksheet. Search settings should be specified deliberately.

Example 16: Find the last cell containing a value or formula

Option Explicit

Sub FindLastUsedRowAcrossSheet()

    Dim ws As Worksheet
    Dim lastCell As Range
    Dim lastRow As Long

    Set ws = ThisWorkbook.Worksheets("Sheet1")

    Set lastCell = ws.Cells.Find( _
        What:="*", _
        After:=ws.Cells(1, 1), _
        LookIn:=xlFormulas, _
        LookAt:=xlPart, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)

    If lastCell Is Nothing Then
        MsgBox "Worksheet contains no values or formulas.", vbInformation
        Exit Sub
    End If

    lastRow = lastCell.Row

    MsgBox "Last used row: " & lastRow, vbInformation

End Sub

This searches across the worksheet instead of relying on one specific column. It looks for cells containing constants or formulas; formatting alone is not treated as a match.

18. Common Mistakes to Avoid

  • Forgetting to update the loop counter in a Do While or Do Until loop.
  • Creating an infinite loop because the stopping condition never becomes satisfied.
  • Using Integer for large row counters instead of Long.
  • Assuming the last used row in column A is the last used row in every other column.
  • Processing the header row accidentally instead of starting from the first data row.
  • Performing calculations without validating blanks, errors, or non-numeric cell values.
  • Assuming UsedRange always identifies the exact data boundaries.
  • Writing over existing worksheet data without checking the output column first.

19. Practice Exercises

Complete these tasks in Excel VBA. Test your solutions with empty worksheets, blank cells, and different data sizes.

  1. Print numbers from 1 to 100 using For...Next.
  2. Print all even numbers between 1 and 50 using Step.
  3. Calculate the sum of numbers from 1 to 100.
  4. Use For Each to highlight values greater than 500.
  5. Use Do While to print numbers from 1 to 20.
  6. Use Do Until to stop when a counter reaches 10.
  7. Use Exit For to stop when a target number is found.
  8. Find the last data row in column A.
  9. Calculate quantity multiplied by price for every product row.
  10. Fill empty department cells with a default value.
  11. Loop through all worksheets and print their names.
  12. Use a dynamic range to calculate employee bonuses.
  13. Rewrite a cell-by-cell calculation using an array.
  14. Find the last used row across the worksheet with Find.

Important Points to Remember

  • Use For...Next when the iteration count is known.
  • Use For Each...Next for collections and cell ranges.
  • Use Do While while a condition is True.
  • Use Do Until until a condition becomes True.
  • Use Exit For and Exit Do to leave loops early.
  • Use Long for worksheet row counters.
  • Calculate the last row dynamically when data size changes.
  • Validate cell values before processing them.
  • Use arrays for efficient processing of large datasets.

Summary

Loops repeat instructions and eliminate repetitive code. VBA provides For...Next, For Each...Next, Do While, Do Until, and post-test Do loops. Dynamic ranges allow macros to adapt to changing worksheet sizes, while last-row techniques help determine how much data needs processing.

By combining loops, data validation, dynamic ranges, and arrays, you can build reliable and efficient Excel automation for reports, calculations, formatting, and data cleaning.