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

The VBA Rows property returns rows within a range or worksheet in Excel. This property is useful for manipulating or interacting with rows in Excel through VBA code.

In this Excel tutorial, you’ll learn about the VBA Rows property, and its syntax, and discover various actions like selecting, inserting, adjusting row height, counting, copying, hiding and unhiding, grouping and ungrouping, deleting, freezing, highlighting, and finding duplicate rows using VBA in Excel.

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.

⏷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 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.

However, 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 operate.

What Is the Syntax of Rows Function in Excel VBA?

In Excel VBA, the Rows property refers to a collection of rows in a worksheet. The basic syntax of the Rows function in Excel VBA is:

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 can use the Select method with the Rows object to select specific rows using VBA in Excel. We will use the dataset below to select rows without range and within a range using Excel VBA.

1. Selecting Entire Row

In this example, we will demonstrate how to select an entire row using VBA. Apply the following steps.

• First, we have to open the Visual Basic Editor window. To do so, go to the Developer tab => In the Code group of command, you will find the Visual Basic option.

• Click on the Visual Basic option to open the Visual Basic Editor window.
• Next, click on the Insert menu => You will find the Module option.

• Click on the Module option to open 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. So we can use the following VBA code to do so:

Sub SelectEntireRow()
    Rows(5).Select
End Sub 
			

• Enter the provided code in the module => click on the Run button.

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

Now, If you want to select a different row based on your requirements, 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 you will find the Run button on the ribbon.

• After clicking the Run button, you will get an input box like the following. Enter the desired row number that you want to select in the input box.

Here, I have entered 8.

• Click the OK As you can see, the entire 8th row is now selected.

2. Selecting Rows in a Range

Sometimes, instead of selecting the entire row, you may want to select rows from a range. Here, we will select rows within a range instead of selecting an entire row using the Select method. Apply the following steps:

• 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 3 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.
• Finally, in the third input box, enter the row number where you want to stop the selection.
• After clicking the OK button, it will select the rows within the specified range like the gif below.

Click the GIF to enlarge it

How to Insert Rows in Excel with VBA

We can insert rows in Excel VBA using the Insert method with the Rows object. Here, we will show how to insert blank rows and how to insert rows while copying all the formulas from the above row.

1. Inserting Rows

We will use the dataset below to illustrate the row insert procedures. Suppose, we want to insert two blank rows right below the 6th row of the worksheet.

• Enter the following code in a module => click on the Run button from the ribbon.

Sub InsertRows()
Rows("7:8").Insert
End Sub

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

You can use the code below if you want a more dynamic code. This code takes the input of inserting place and the number of rows to insert through input boxes.

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.

• Click on the Run button and you will get an input box. Enter the start row number from where you want to insert rows.

Here, I have entered 8 to insert rows after the 7th row.

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

Here, I have entered 2.

• Finally, click the OK button in the input box and this will insert 2 blank rows after the 7th row in your current worksheet.

2. Inserting Row 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 insert new rows in this dataset, these rows will be blank. But we can use VBA to copy formulas from the above cells while inserting rows.

• 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.

Here, I have entered 10.

• In the second input box, enter how many rows you want to insert.

Here, I have entered 2.

• Finally, press the OK button, and you will get the desired result. As you can see cells of columns Discount (10%) and Discounted Price in inserted rows have formulas from the above cells as well.

Click the GIF to enlarge it

How to Set Row Height Using VBA in Excel

We can use VBA to customize row height in Excel. By using the rowHeight property, we can set the preferred height and by using the Autofit method, we can adjust the row height automatically.

1. Setting Row Height Based on User Input

• To set the 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

• 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.

Here, I have selected the range B7:C9.

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

Here, I have entered 30.

• Finally, click on the OK button and the selected rows will have the specified row height.

2. Autofitting Row Height

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

We will use the Autofit method with the Rows object to adjust row height here. Apply the following steps:

• 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

• 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.

Here, I have selected the range B5:C15.

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

How to Count Rows with VBA in Excel

We can use the Count method with the Rows property to count the number of rows with VBA in Excel. In this context, we will count the total number of rows in a given range and the number of rows in a range that contains data.

1. Counting Rows in a Range

• To count the number of 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 
			

• 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.

Here, I have selected the range B6:C11.

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

2. Counting Rows in a Range with Data

In this example, we will use the following dataset to count the total number of rows with data in a range. As you can see, rows 8, 10, and 13 are empty.

Now, apply the following steps to count rows 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

• Click the Run button and you will get an input box like the following. Enter the range for which you want to count the non-empty rows.

Here, I have selected the range B6:C15.

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

How to Copy-Paste Rows in Excel with VBA

We can use the Copy method in VBA to copy and paste rows in Excel. Follow the steps below:

• Enter the following code in a module to copy and paste a range of cells in your current worksheet.

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

• 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. Here, I have entered the range B5:C9.

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

• Click OK and the copied cells will be pasted in your desired destination.

Click the image to enlarge it

How to Hide and Unhide Rows Using VBA in Excel

We can use the Hidden property along with the Rows object to unhide or hide rows using VBA in Excel. The Hidden property is a Boolean property which means if you set the property to True, the object will be hidden, but if you set the property to False, the object will be visible.

1. Hiding Rows

In the following dataset, we want to hide the 7th and 8th number row of the worksheet. Apply the following steps to do this.

• Enter the following code in a module => click on the Run button.

Sub HideRows()
    Rows("7:8").Hidden = True
End Sub

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

Now, 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.

• Click on the Run icon and an input box will ask you to select the range of rows that you want to hide.

Here, I have selected the range B7:E8.

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

2. Unhiding Rows

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

• Enter the following code in a module => click on the Run button.

Sub Unhide_Rows()
    ActiveSheet.UsedRange.EntireRow.Hidden = False
End Sub

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

How to Group and Ungroup Rows in Excel With VBA

Grouping rows in Excel means combining multiple adjacent rows into a single unit. We can use the Group method with the Rows property to group rows in Excel using VBA, and we can ungroup rows by using the Ungroup 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 to do so => click on the Run button.

Sub GroupRows()
    Rows("8:10").Group
End Sub

• As a result, rows 8 to 10 are now grouped.

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.

• After clicking the Run button, you will get an input box like the following. Enter the range of rows you want to group.

Here, I have selected the range B8:E10.

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

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.

• To do so, enter the following code in a module => click on the Run button.

Sub Ungroup_Rows()
    ActiveSheet.UsedRange.EntireRow.Ungroup
End Sub

• As a result, all grouped rows from the current worksheet are now ungrouped.

How to Delete Rows Using VBA in Excel

By using the Delete method along with the Rows object, we can delete rows with VBA in Excel. We can delete any rows from the Excel file, or we can delete rows based on specific criteria.

1. Deleting Rows

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

• We can enter the following code in a module and click on the Run button.

Sub DeleteRows()
    Rows("7:8").Delete
End Sub

• This will eliminate rows 8 to 10 from the current worksheet.

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.

• After clicking the Run button you will get an input box. Select the range of rows that you want to delete.

Here, I have selected the range B8:E10.

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

You can click the image to enlarge it

2. Deleting Rows Based on Criteria

We can also use VBA macro to delete rows based on criteria. For example, in our dataset, if the Order Quantity of any particular order is less than 20, we want to delete that row from the dataset.

• To do so, 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

• Click on the Run button and an input box will ask you to select the column range where you want to test the criteria. Here, I have selected the range D6:D15.

Click the image to enlarge it

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

How to Freeze Rows Using VBA in Excel

We can use the ActiveWindow.FreezePanes property with the Rows property to freeze rows using VBA in Excel.

Suppose, we want to freeze all the rows above row number 4 in our dataset. We can use the following code to do so.

Sub FreezeRow()
    Rows(4).Select
    ActiveWindow.FreezePanes = True
End Sub

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

You can click the GIF to enlarge it

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

We can use the Interior.Color property along with the Rows object to highlight rows using VBA in Excel.

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

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

• 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, I have entered 3 here.

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

How to Find Duplicate Rows with VBA in Excel

We can find duplicate values using loops in VBA and then we can use the Interior.Color property along with the Rows property to highlight those duplicates.

Consider the following dataset. Row 13 is a duplicate of row 7 and row 15 is a duplicate of row 8.

• To do so, 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

• 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. Here, I have entered the range B6:E15.

• 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.

Click the image to enlarge it

Download Practice Workbook

This concludes our article on VBA rows. We showed various applications of the VBA Rows property in this article. You have learned about its syntax and various actions to do with rows such as selecting, inserting, counting, setting row height, copying and pasting, hiding and unhiding, grouping and ungrouping, deleting, freezing, highlighting, and finding duplicate rows.

I hope this tutorial will help you to efficiently manage and manipulate rows within your Excel workbooks. If you have any queries or feedback, please share it in the comment section.