How to Work with Rows Using VBA in Excel (Ultimate Guide)

For example, in the GIF below, you can see that we have selected the header row of our dataset. We have used the Rows property along with the Select method to select an entire row.

1- Overview of Using VBA Rows Property


Syntax of Rows Function in Excel VBA
Select Rows Using VBA in Excel
 ⏵Selecting Entire Row
 ⏵Selecting Rows in a Range
Insert Rows in Excel with VBA
 ⏵Inserting Row
 ⏵Inserting Row and Copying Formula
Set Row Height Using VBA in Excel
 ⏵Setting Row Height Based on User Input
 ⏵Autofitting Row Height
Count Rows with VBA in Excel
 ⏵Counting Rows in a Range
 ⏵Counting Rows in a Range with Data
Copy-Paste Rows in Excel with VBA
Hide and Unhide Rows Using VBA in Excel
 ⏵Hiding Rows
 ⏵Unhiding Rows
Group and Ungroup Rows in Excel With VBA
 ⏵Grouping Rows
 ⏵Ungrouping Rows
Delete Rows Using VBA in Excel
 ⏵Deleting Rows
 ⏵Deleting Rows Based on Criteria
Freeze Rows Using VBA in Excel
Highlighting Rows That Meet a Certain Condition
Find Duplicate Rows with VBA in Excel


What Is the VBA Rows Property?

In VBA (Visual Basic for Applications), the Rows property refers to single or multiple rows within a range or a worksheet. This property is advantageous when you want to modify or interact with rows in Excel through VBA code. By using the Rows property, you can perform various operations on rows, such as copying, formatting, deleting, or inserting rows.

You can use the Rows property with an object qualifier such as Range or Worksheet.

Range.Rows Property

You can change or interact with the rows within a range using this property.

Worksheet.Rows Property

By using this property, you can manipulate or interact with the entire collection of rows in a worksheet.

You can use the Rows property without an object qualifier, which is equivalent to using ActiveSheet.Rows. But, if the active document is not a worksheet, the Rows property will fail to fetch data.


What Is the Syntax of Rows Function in Excel VBA?

The basic syntax of the Rows function in Excel VBA is:

Rows(row_index)

Here, row_index is the index or indices of the rows. It can be a single row or a range of rows. For example, to refer to a single row, you can use the Rows function as Rows(5). To refer to a range of rows, you can use the Rows function as Rows(“5:8”).

Range(“D2:J10”).Rows(3) will specify the 3rd row in the range D2:J10 whereas Worksheets(“Sheet1”).Rows(3) will specify the 3rd row in the worksheet named “Sheet1”.


How to Select Rows Using VBA in Excel

We will use the dataset below to select rows without range and within a range using Excel VBA.

2- Dataset for selecting rows


Method 1 – Selecting an Entire Row

  • Go to the Developer tab.
  • In the Code group, selsect the Visual Basic option.

3-Opening Visual Basic

  • This opens the Visual Basic Editor window.
  • Click on the Insert menu.
  • Select the Module option.

4-Inserting Module

Note: If you don’t have the Developer tab in your Excel ribbon, you can press the Alt + F11 keys on your keyboard to open the Visual Basic Editor window.
  • This opens a new module where you can enter your VBA code.
  • Suppose we want to select the header row of our dataset which is row number 5 in the worksheet. Use the following VBA code to do so:
Sub SelectEntireRow()
    Rows(5).Select
End Sub 
			
  • Enter the provided code in the module and click on the Run button.

5-Running code

  • This will automatically select the row number 5 of your current worksheet.

6-Output after selecting an entire row

  • If you want to select a different row, you can use an input box to take the row number input for selecting a row. Here is the modified code:
Sub Select_Entire_Row()
    Dim selectedRow As Long

    selectedRow = Application.InputBox("Enter the row number to select:", Type:=1)

    If selectedRow >= 1 And selectedRow <= Rows.count Then
        Rows(selectedRow).Select
    Else
        MsgBox "Invalid row number. Please enter a valid row number."
    End If
End Sub
  • Enter the provided code in the module and run it.

7-Running code

  • You will get an input box. Enter the desired row number that you want to select in the input box. We have entered 8.

8-Entering the row number

  • Click OK. 
  • The entire 8th row is now selected.

9-Output after selecting an entire row


Method 2 – Selecting Rows in a Range

  • Insert a new module and enter the following code:
Sub Select_Rows_in_Range()
    Dim inputRange As Range
    Dim startRow As Long, endRow As Long

    Set inputRange = Application.InputBox("Select the range from where you want to select rows:", Type:=8)

    If Not inputRange Is Nothing Then

        startRow = Application.InputBox("Enter the start row number:", Type:=1)
        endRow = Application.InputBox("Enter the end row number:", Type:=1)

        If startRow >= 1 And endRow >= startRow Then
            inputRange.Rows(startRow & ":" & endRow).Select
        Else
            MsgBox "Invalid row numbers. Please enter valid start and end rows."
        End If
    Else
        MsgBox "No valid range is selected."
    End If
End Sub
  • After clicking the Run button, you will get three input boxes.
  • In the first input box, choose the range from which you want to select rows and click OK.
  • In the second input box, specify the starting row number for selection and click OK.
  • In the third input box, enter the row number where you want to stop the selection.
  • Click OK.
10-Selecting rows in a range

Click the GIF to enlarge it


How to Insert Rows in Excel with VBA


Method 1 – Inserting Blank Rows

Let’s insert two blank rows right below the 6th row of the worksheet.

11-Dataset for inserting rows

  • Enter the following code in a module and click on the Run button from the ribbon.
Sub InsertRows()
Rows("7:8").Insert
End Sub

12-Running code

  • This will insert two blank rows in your current worksheet right below the 6th row.

13-Output after inserting rows

  • You can use the code below if you want a more dynamic code. This code takes two inputs: where you want the rows, and how many of them.
Sub Insert_Rows()
    Dim startRow As Long, numRows As Long

    startRow = Application.InputBox("Enter the start row number where you want to insert rows:", Type:=1)

    numRows = Application.InputBox("Enter the number of rows to insert:", Type:=1)

    If startRow >= 1 And numRows >= 1 Then
        Rows(startRow & ":" & startRow + numRows - 1).Insert
    Else
        MsgBox "Invalid input. Please enter valid start row and number of rows."
    End If
End Sub
  • Enter the provided code in a module.

14-Running code

  • Click on the Run button and you will get an input box.
  • Enter the start row number from where you want to insert rows. We have entered 8 to insert rows after the 7th row.

15-Entering the start row number

  • Click the OK button and another input box will appear. Enter the number of rows that you want to insert. We have entered 2.

16-Entering the total number of rows to insert

  • Click the OK button in the input box.

17-Output after inserting rows


Method 2 – Inserting Rows and Copying Formula

Consider the following dataset. The cells of the column Discount (10%) and Discounted Price contain formulas to calculate the discount amount and discounted price respectively. If we were to insert new rows in this dataset manually, they will be blank.

18-Dataset for Inserting Row while Copying Formula

  • Enter the following code in a new module.
Sub Insert_Rows_and_Copy_Formula()
    Dim InsertRows As Integer
    insertPlace = InputBox("Insert Rows after which row?:")
    InsertRows = InputBox("How many rows do you want to insert?:")
    For i = 1 To InsertRows
        Rows(insertPlace + 1).Insert
    Next i
    lastCol = Cells(insertPlace, Columns.count).End(xlToLeft).Column
    For i = 1 To lastCol
        If Cells(insertPlace, i).HasFormula = True Then
            Cells(insertPlace, i).Copy
            For j = 1 To InsertRows
                Cells(insertPlace + j, i).PasteSpecial Paste:=xlPasteFormulas
            Next j
            Application.CutCopyMode = False
        End If
    Next i
End Sub
  • After pressing the Run button, two input boxes will pop up.
  • In the first input box, enter the row number from where you want to start inserting rows and click the OK button. We have entered 10.
  • In the second input box, enter how many rows you want to insert. We have entered 2.
  • Press the OK button, and you will get the desired result.
19- Inserting Row while Copying Formula

Click the GIF to enlarge it


How to Set Row Height Using VBA in Excel


Method 1 – Setting Row Height Based on User Input

  • Enter the following code in a module.
Sub Set_Row_Height_Based_on_User_Input()
    Dim rowHeight As Double
    Dim selectRows As Range

    Set selectRows = Application.InputBox("Select the rows to adjust height:", Type:=8)

    If Not selectRows Is Nothing Then
        rowHeight = val(InputBox("Enter the Row Height:"))

        If rowHeight > 0 Then
            selectRows.EntireRow.rowHeight = rowHeight
        Else
            MsgBox "Invalid row height entered."
        End If
    Else
        MsgBox "No rows selected."
    End If
End Sub

20-Running code

  • Click on the Run button and an input box will ask you to select the range of rows that you want to adjust the height. We have selected the range B7:C9.

21-Selecting range

  • Click OK and another input box will ask you to enter the row height in pixels. We have entered 30.

22-Setting row height

  • Click on the OK button and the selected rows will have the specified row height.

23-Output after Setting row height


Method 2 – Autofitting Row Height

Consider the following dataset. The content of some cells is not visible properly within the current row height.

24-Dataset for Autofitting Row Height

  • Enter the following code in a module.
Sub Autofit_Row_Height()
    Dim targetRows As Range

    Set targetRows = Application.InputBox("Select the range of rows to autofit:", Type:=8)

    If Not targetRows Is Nothing Then
        targetRows.WrapText = True
        targetRows.EntireRow.AutoFit
    Else
        MsgBox "Invalid selection. Please select a valid range of rows."
    End If
End Sub

25-Running code

  • Click on the Run button and an input box will ask you to select the range of rows where you want to autofit the row height. We have selected the range B5:C15.

26-Selecting range

  • Click OK and the rows in the selected range will autofit.

27-Output after Autofitting Row Height


How to Count Rows with VBA in Excel


Method 1 – Counting Rows in a Range

  • Enter the following code in a module.
Sub Count_Rows_in_Range()
    Dim inputRange As Range

    Set inputRange = Application.InputBox("Select the range to count rows:", Type:=8)

    If Not inputRange Is Nothing Then
        MsgBox "Number of rows in the selected range: " & inputRange.Rows.count
    Else
        MsgBox "No valid range selected."
    End If
End Sub 
			

28-Running code

  • After clicking the Run button, an input box will ask you to select the range for which you want to count the number of rows in that range. We have selected the range B6:C11.

29-Selecting range

  • Click OK to find a message box displaying the total number of rows in the selected range.

30-Output after Counting Rows in a Range


Method 2 – Counting Rows with Data in a Range

In this example, rows 8, 10, and 13 are empty.

31-Dataset for Counting Rows in a Range with Data

  • Enter the following code in a module.
Sub Count_NonEmpty_Rows_in_Range()
    Dim inputRows As Range
    Dim nonEmptyRows As Long

    Set inputRows = Application.InputBox("Select the range to count non-empty rows:", Type:=8)

    If Not inputRows Is Nothing Then
        nonEmptyRows = 0

        For Each Row In inputRows.Rows
            If WorksheetFunction.CountA(Row) > 0 Then
                nonEmptyRows = nonEmptyRows + 1
            End If
        Next Row

        MsgBox "Number of non-empty rows in the selected range: " & nonEmptyRows
    Else
        MsgBox "No valid range selected."
    End If
End Sub

32-Running code

  • Click the Run button and you will get an input box. Enter the range for which you want to count the non-empty rows. We have selected the range B6:C15.

33-Selecting range

  • Click OK and the code will display the total number of non-empty rows in the selected range in a message box.

34- Output after counting rows in a range with data


How to Copy-Paste Rows in Excel with VBA

  • Enter the following code in a module.
Sub Copy_Paste_Rows()
    Dim copyRange As Range
    Dim destination As Range

    Set copyRange = Application.InputBox("Enter the range that you want to copy:", Type:=8)
    Set destination = Application.InputBox("Enter the destination range:", Type:=8)

    If Not copyRange Is Nothing And Not destination Is Nothing Then
        copyRange.Copy destination:=destination
    Else
        MsgBox "Invalid range."
    End If
End Sub

35- Running code

  • Click the Run icon and an input box will appear. It will ask you to select the range of rows that you want to copy. We have entered the range B5:C9.

36-Selecting range

  • Click OK and another input box will now ask you to select the destination range where you would like to paste the copied rows. We have set the destination range to cell G5.

37-Destination range

  • Click OK and the copied cells will be pasted in your desired destination.
38- Output after copy-paste rows with Excel VBA

Click the image to enlarge it


How to Hide and Unhide Rows Using VBA in Excel


Method 1 – Hiding Rows

We want to hide rows 7 and 8 of the worksheet.

39-Dataset for Hiding Rows

  • Enter the following code in a module and click on the Run button.
Sub HideRows()
    Rows("7:8").Hidden = True
End Sub

40-Running code

  • This will hide the 7th and 8th number row of your current worksheet.

41- Output after Hiding Rows

  • If you want to hide a range of rows of your current worksheet based on your requirements, you can use an Input Box to take the input of the range for hiding rows. Here is the modified code:
Sub Hide_Rows()
    Dim targetRows As Range

    Set targetRows = Application.InputBox("Select the range of rows to hide:", Type:=8)

    If Not targetRows Is Nothing Then
        targetRows.EntireRow.Hidden = True
    Else
        MsgBox "Invalid selection. Please select a valid range of rows."
    End If
End Sub
  • Enter the provided code in a module.

42- Running code

  • Click on the Run icon and an input box will ask you to select the range of rows that you want to hide. We have selected the range B7:E8.

43-Selecting range

  • Click OK and it will hide the rows that you selected.

44- Output after Hiding Rows


Method 2 – Unhiding Rows

In the following dataset, there are some hidden rows, and we want to unhide all these hidden rows from the worksheet.

45- Dataset for Unhiding Rows

  • Enter the following code in a module and click on the Run button.
Sub Unhide_Rows()
    ActiveSheet.UsedRange.EntireRow.Hidden = False
End Sub

46- Running code

  • This will unhide all the hidden rows in the current worksheet.

47- Output after Unhiding Rows


How to Group and Ungroup Rows in Excel With VBA


Method 1 – Grouping Rows

Suppose we want to group rows 8 to 10 in the dataset that we have been using so far.

  • Enter the following code in a module and click on the Run button.
Sub GroupRows()
    Rows("8:10").Group
End Sub

48- Running code

  • Rows 8 to 10 are now grouped.

49- Output after Grouping Rows

  • To group the rows of your preference, you can modify the above code to take a range of rows as input. Here is the modified code.
Sub Group_Rows()
    Dim targetRows As Range
    Set targetRows = Application.InputBox("Select the range of rows to group:", Type:=8)

    If Not targetRows Is Nothing Then
        targetRows.EntireRow.Group
    Else
        MsgBox "Invalid selection. Please select a valid range of rows."
    End If
End Sub
  • Apply the above code in a module and click the Run button.

50- Running code

  • After clicking the Run button, you will get an input box like the following. Enter the range of rows you want to group. We have selected the range B8:E10.

51- Selecting range

  • Click OK and the rows of the selected cells will be grouped.

52- Output after Grouping Rows


Method 2 – Ungrouping Rows

In the following dataset, we have some rows that are grouped, and we want to ungroup all rows from the current worksheet.

53- Dataset for Ungrouping Rows

  • Enter the following code in a module and click on the Run button.
Sub Ungroup_Rows()
    ActiveSheet.UsedRange.EntireRow.Ungroup
End Sub

54- Running code

  • All grouped rows from the current worksheet are now ungrouped.

55- Output after Ungrouping Rows


How to Delete Rows Using VBA in Excel


Method 1 – Deleting Rows

Suppose we want to delete rows 8 to 10 in the dataset that we have been using so far.

  • Enter the following code in a module and click on the Run button.
Sub DeleteRows()
    Rows("7:8").Delete
End Sub

56- Running code

  • This will eliminate rows 8 to 10 from the current worksheet.
57- Output after Deleting Rows

Click the image to enlarge it

  • To delete a range of rows based on user input, you can modify the code as the following. This code takes a range of rows as input from the user and deletes the rows.
Sub Delete_Rows()
    Dim targetRows As Range

    Set targetRows = Application.InputBox("Select the range of rows to delete:", Type:=8)

    If Not targetRows Is Nothing Then
        targetRows.EntireRow.Delete
    Else
        MsgBox "Invalid selection. Please select a valid range of rows."
    End If
End Sub
  • Enter the above code in a module and click the Run button.

58- Running code

  • After clicking the Run button you will get an input box. Select the range of rows that you want to delete. We have selected the range B8:E10.

59-Selecting range

  • Click OK and the rows of the selected cells will be deleted.
60- Output after Deleting Rows

You can click the image to enlarge it


Method 2 – Deleting Rows Based on Criteria

In our dataset, if the Order Quantity of any particular order is less than 20, we want to delete that row from the dataset.

  • Enter the following code in a module.
Sub Delete_Rows_Based_on_Criteria()
    Dim inputRange As Range

    Set inputRange = Application.InputBox("Select the column range to test the criteria:", Type:=8)

    If inputRange Is Nothing Then
        MsgBox "No range is selected."
        Exit Sub
    End If
    
    Dim cell As Range
    Dim i As Long

    For i = inputRange.Rows.count To 1 Step -1
        For Each cell In inputRange.Rows(i).Cells

            If cell.Value < 20 Then
                inputRange.Rows(i).EntireRow.Delete
                Exit For
            End If
        Next cell
    Next i
End Sub

61-Running code

  • Click on the Run button and an input box will ask you to select the column range where you want to test the criteria. We have selected the range D6:D15.
62- Selecting range

Click the image to enlarge it

  • Click OK and the rows which have an order quantity of less than 20 will be deleted.

63- Output after Deleting Rows Based on Criteria


How to Freeze Rows Using VBA in Excel

Suppose we want to freeze all the rows above row number 4 in our dataset.

  • Use the following code to do so.
Sub FreezeRow()
    Rows(4).Select
    ActiveWindow.FreezePanes = True
End Sub
64- Freeze Rows Using VBA

Click the GIF to enlarge it

  • If you want a more dynamic code, you can use the code below. The following code takes the row number input to freeze rows through an input box.
Sub Freeze_Row()
    Dim rowNum As Integer

    rowNum = InputBox("Enter the row number to freeze:", "Freeze Row")

    If rowNum > 0 Then
        ActiveWindow.FreezePanes = False
        Rows(rowNum & ":" & rowNum).Select
        ActiveWindow.FreezePanes = True

        MsgBox "Row " & rowNum & " has been frozen.", vbInformation
    Else
        MsgBox "Invalid row number entered.", vbExclamation
    End If
End Sub
65- Freeze Rows Using VBA

You can click the GIF to enlarge it


How to Use VBA for Highlighting Rows That Meet a Certain Condition

Suppose we want to highlight rows based on a specific value in the cells. For our dataset, if the Order Quantity of any particular order is less than 20, we want to highlight that row in the dataset.

  • Enter the following code in a module.
Sub Highlight_Rows_Based_on_Condition()
    Dim datasetRange As Range
    Dim columnRange As Range
    Dim cell As Range
    Dim i As Long

    Set datasetRange = Application.InputBox("Select the dataset range:", Type:=8)

    If datasetRange Is Nothing Then
        MsgBox "No dataset range is selected."
        Exit Sub
    End If
    
    criteriaColIndex = InputBox("Index of the Criteria Column in the Selected range:")
    highlightCriteria = 20
    
    If IsEmpty(criteriaColIndex) = True Or IsNumeric(criteriaColIndex) = False Then
        MsgBox "Invalid Index Value!"
        Exit Sub
    End If
    
    For i = 1 To datasetRange.Rows.count
        If datasetRange.Cells(i, Int(criteriaColIndex)).Value < highlightCriteria Then
            datasetRange.Rows(i).Interior.Color = RGB(245, 220, 220)
        End If
    Next i
End Sub

66- Running code

  • Click on the Run button, and an input box will appear. Enter the range of our dataset to highlight. We have entered the range B6:E15.

67- Selecting range

  • After clicking the OK button, another input box will ask you to enter the index of the criteria column in the selected range. You’ll find the OK button in the input box.
  • As the Order Quantity column is the 3rd column in the selected range, we have entered 3 here.

68- Entering the index of the criteria column

  • Click OK and it will highlight all the rows in the selected range that meet the criteria.

69- Output after Highlighting Rows That Meet a Certain Condition


How to Find Duplicate Rows with VBA in Excel

Consider the following dataset. Row 13 is a duplicate of row 7 and row 15 is a duplicate of row 8. Let’s highlight them.

70- Dataset for Finding Duplicate Rows with VBA

  • Enter the following code in a module.
Sub Finding_Duplicate_Rows()
    Dim inputRange As Range
    Dim i As Integer, j As Integer, k As Integer
    Dim baseRow As String, checkRow As String
    Dim count As Integer

    Set inputRange = Application.InputBox("Select the range to find duplicate rows:", Type:=8)

    If inputRange Is Nothing Then
        MsgBox "No valid range selected. Exiting..."
        Exit Sub
    End If

    inputRange.Interior.Pattern = xlNone
    count = 0

    For i = 1 To inputRange.Rows.count

        If inputRange.Rows(i).Interior.Color = RGB(255, 255, 255) Then
            baseRow = ""
            
            For j = 1 To inputRange.Columns.count
                baseRow = baseRow & inputRange.Cells(i, j).Value
            Next j
            
            For k = i + 1 To inputRange.Rows.count
                If inputRange.Rows(k).Interior.Color = RGB(255, 255, 255) Then
                    checkRow = ""
                    
                    For j = 1 To inputRange.Columns.count
                        checkRow = checkRow & inputRange.Cells(k, j).Value
                    Next j
                    
                    If checkRow = baseRow Then
                        inputRange.Rows(i).Interior.Color = RGB(255, 220, 220)
                        inputRange.Rows(k).Interior.Color = RGB(255, 220, 220)
                    End If
                End If
            Next k
        End If
    Next i

    For i = 1 To inputRange.Rows.count
        If inputRange.Rows(i).Interior.Color = RGB(255, 220, 220) Then
            count = count + 1
        End If
    Next i

    MsgBox count & " duplicate rows found and highlighted.", vbInformation
End Sub

71- Running code

  • Click on the Run button and an input box will ask you to select the range of cells where you want to find the duplicate rows. We entered the range B6:E15.

72- Selecting range

  • Click OK. This will highlight all the duplicate rows in the dataset, and a message box will pop up displaying how many row duplicates have been found and highlighted.
73- Output after Finding Duplicate Rows with VBA

Click the image to enlarge it


Download the Practice Workbook

Get FREE Advanced Excel Exercises with Solutions!

Sishir Roy
Sishir Roy

Sishir Roy, a recent graduate in Civil Engineering from Bangladesh University of Engineering and Technology, has dedicated over a year to the ExcelDemy project. As an Excel and VBA Content Developer, he has authored 50+ articles, updated 100+, and solved complex Excel VBA challenges. Excelling in troubleshooting and simplifying problems, his love for diverse problem-solving and aiding others is evident in his keen interests in Data Analysis, Advanced Excel, VBA Macro, and Excel Power Query, enriching the project's... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo