While working with Excel, you often encounter blank rows in a dataset that can cause adverse effects such as inaccurate calculations and analysis.
Although you can clean the dataset by removing the blank rows using formulas and the user interface options, using the ‘Rows.Delete‘ method in VBA is often faster and more efficient.
In this tutorial, I will show you eleven examples of deleting blank rows in Excel using VBA.
Note: I highly recommend creating a data backup before deleting blank rows using VBA, as VBA changes are irreversible. The backup will ensure the availability of your original data should you need it in the future.
Delete Blank Rows From a Specific Range
Assuming you have blank rows in the cell range A1:K100 on ‘Sheet2’ of the current workbook. You can use the following code to delete the blank rows:
Sub DeleteBlankRowsInRange()
' Declares worksheet and range variables
Dim ws As Worksheet
Dim rng As Range
Dim row As Long
' Sets the worksheet and range to work with
Set ws = ThisWorkbook.Sheets("Sheet2")
Set rng = ws.Range("A1:K100")
' Loops through the range from the bottom row to the top
For row = rng.Rows.Count To 1 Step -1
' Checks if the entire row within the range is blank
If Application.WorksheetFunction.CountA(rng.Rows(row)) = 0 Then
' Deletes the row if it is blank
rng.Rows(row).Delete
End If
Next row
End SubThe above VBA subroutine is designed to delete all blank rows within a specific range in an Excel worksheet.
It targets the worksheet “Sheet2” and focuses on the range “A1:K100”. The script loops through this range from the last row upwards, checking each row for any content.
It uses the CountA function to determine if a row is entirely blank (i.e., contains no data). If a row is found to be blank, it is deleted.
Note: When you delete a row in Excel, the rows below it shift up to fill the gap left by the deleted row. Deleting rows from the top down can cause problems because the row numbers dynamically change as you delete them. As a result, you might skip or delete rows you didn’t intend to.
Delete Blank Rows in a Specific Worksheet of the Current Workbook
Suppose you have data with blank rows on ‘Sheet1’ of the current workbook and want to delete the blank rows. Below is a VBA code you can use to do this:
Sub DeleteBlankRowsInWorksheet()
' Declares variables
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
' Sets the target worksheet to "Sheet1"
Set ws = ThisWorkbook.Sheets("Sheet1")
' Determines the last used row in column A
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Loops backwards from the last row to the first
For i = lastRow To 1 Step -1
' Checks if the entire row is blank
If WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
' Deletes the row if it is blank
ws.Rows(i).Delete
End If
Next i
End SubThe above VBA macro code removes all blank rows from a specific worksheet (“Sheet1”) in the current workbook.
It first identifies the last used row in column “A” to determine the range for checking blank rows. It then iterates from this last row up to the first row, examining each row for any content.
It uses the CountA function to check if a row is completely blank. If a row contains no data, it is deleted.
The approach of starting from the last row and moving upwards helps to prevent any potential issues with changing row indices after deletions.
Also read: VBA to Remove Duplicates in Excel
Delete Blank Rows From the Active Worksheet
Suppose you have blank rows in the used range of the currently active worksheet. You can use the below code to delete the blank rows:
Sub DeleteBlankRowsActiveSheet()
' Declares variables
Dim ws As Worksheet
Dim rng As Range
Dim row As Long
' Sets the active sheet as the target worksheet and defines the used range
Set ws = ActiveSheet
Set rng = ws.UsedRange
' Loops backwards through the rows in the used range
For row = rng.Rows.Count To 1 Step -1
' Checks if the entire row within the range is blank
If Application.WorksheetFunction.CountA(rng.Rows(row)) = 0 Then
' Deletes the row if it is blank
rng.Rows(row).Delete
End If
Next row
End SubThe above VBA code is tailored to delete all blank rows within the used range of the active worksheet.
It identifies the used range of the active sheet, which is the area that contains any data, formatting, or formulas. The script then iterates through the rows in this range from the bottom to the top.
For each row, it uses the CountA function to determine if the row is completely empty. If a row is found to be blank, it is deleted.
Also read: Count Rows using VBA in Excel
Delete Blank Rows From All Worksheets of the Current Workbook
Suppose you have blank rows in all worksheets of the current workbook. You can use the following VBA code to delete the blank rows:
Sub DeleteBlankRowsInAllWorksheets()
' Declares variables
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
' Iterates through each worksheet in the current workbook
For Each ws In ThisWorkbook.Sheets
' Finds the last used row in column A of the current worksheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Loops backwards from the last row to the first row
For i = lastRow To 1 Step -1
' Checks if the entire row is blank
If WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
' Deletes the row if it is blank
ws.Rows(i).Delete
End If
Next i
Next ws
End SubThe above VBA subroutine is designed to remove all blank rows from every worksheet in the current workbook.
It loops through each worksheet, identifying the last used row in column A for each one.
Then, for each worksheet, it iterates from the bottom row upwards, checking each row for any content.
It uses the CountA function to determine if a row is completely blank (i.e., contains no data). If a row is found to be blank, it is deleted.
This method is effective for cleaning up data ranges by removing unnecessary blank rows across all sheets, thereby streamlining the entire workbook for further processing or analysis.
Also read: VBA Delete Files in Folder
Delete Blank Rows in a Specific Worksheet of Another Open Workbook
Assuming you have another open workbook named ‘Sales’ with a dataset with blank rows in ‘Sheet1.’ You can use the code below to remove the blank rows:
Sub DeleteBlankRows()
' Declares variables
Dim otherWb As Workbook
Dim otherWs As Worksheet
Dim lastRow As Long
Dim i As Long
' Sets references to the other workbook and its specific worksheet
Set otherWb = Workbooks("Sales.xlsx")
Set otherWs = otherWb.Sheets("Sheet1")
' Finds the last used row in column A of the specified worksheet
lastRow = otherWs.Cells(otherWs.Rows.Count, "A").End(xlUp).Row
' Loops backwards from the last row to the first row
For i = lastRow To 1 Step -1
' Checks if the entire row is blank
If WorksheetFunction.CountA(otherWs.Rows(i)) = 0 Then
' Deletes the row if it is blank
otherWs.Rows(i).Delete
End If
Next i
End SubThe above VBA code deletes all blank rows from a particular worksheet (“Sheet1”) in another workbook (“Sales.xlsx”).
The script first sets references to the target workbook and worksheet. It then determines the last used row in column A of the specified worksheet.
Following this, it iterates through the rows of the worksheet from the last row upwards. For each row, it checks for any content using the CountA function. If a row is completely empty, it is deleted.
Also read: VBA to Delete Rows Based On Cell Value
Delete Blank Rows in All Worksheets Of Another Open Workbook
Assuming you have another open workbook named ‘Student Population.xlsx’ with blank rows in all worksheets. You can use the code below to remove the blank rows:
Sub DeleteBlankRows()
' Declares variables
Dim otherWb As Workbook
Dim otherWs As Worksheet
Dim lastRow As Long
Dim i As Long
' Sets a reference to the external workbook "Student Population.xlsx"
Set otherWb = Workbooks("Student Population.xlsx")
' Iterates through each worksheet in the external workbook
For Each otherWs In otherWb.Sheets
' Determines the last used row in column A of the current worksheet
lastRow = otherWs.Cells(otherWs.Rows.Count, "A").End(xlUp).Row
' Loops backwards from the last row to the first row
For i = lastRow To 1 Step -1
' Checks if the entire row is blank
If WorksheetFunction.CountA(otherWs.Rows(i)) = 0 Then
' Deletes the row if it is blank
otherWs.Rows(i).Delete
End If
Next i
Next otherWs
End SubThis subroutine loops through each worksheet in another open workbook. For each worksheet, it finds the last used row in column A and deletes blank rows using the same code as Example #1.
Also read: VBA to Freeze Top Row in Excel
Delete Blank Rows In All Worksheets In All Open Workbooks
Suppose you have blank rows in all worksheets of all open workbooks. You can use the code below to delete the blank rows:
Sub DeleteBlankRowsInAllOpenWorkbooks()
' Declares variables
Dim wb As Workbook
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
' Iterates through each open workbook
For Each wb In Workbooks
' Iterates through each worksheet in the current workbook
For Each ws In wb.Sheets
' Finds the last used row in column A of the current worksheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Loops backwards from the last row to the first row
For i = lastRow To 1 Step -1
' Checks if the entire row is blank
If WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
' Deletes the row if it is blank
ws.Rows(i).Delete
End If
Next i
Next ws
Next wb
End SubThe above VBA code deletes all blank rows from every worksheet in all open Excel workbooks.
It loops through each open workbook and, within each workbook, iterates through every worksheet.
For each worksheet, the VB script identifies the last row that contains data in column A. It then examines each row from the bottom upwards, using the CountA function to check if a row is entirely blank.
If a row is found to be empty, it is deleted.
Also read: VBA to Hide Rows Based On Cell Values
Delete Blank Rows In a Worksheet of a Closed Workbook
Suppose you have a closed workbook called ‘Sales Report.xlsm’ with blank rows in ‘Sheet1.’ You can delete the blank rows using the following code:
Sub DeleteBlankRowsinClosedWorkbook()
' Disables screen updating for efficiency
Application.ScreenUpdating = False
' Declares variables
Dim closedWb As Workbook
Dim closedWs As Worksheet
Dim closedWorksheetName As String
Dim lastRow As Long
Dim i As Long
Dim closedWorkbookPath As String
' Sets the path for the closed workbook
closedWorkbookPath = "C:\Sales Reports\Sales Report.xlsm"
' Opens the closed workbook
Set closedWb = Workbooks.Open(closedWorkbookPath)
' Sets the target worksheet name and gets a reference to it
closedWorksheetName = "Sheet1"
Set closedWs = closedWb.Sheets(closedWorksheetName)
' Determines the last used row in column A of the specified worksheet
lastRow = closedWs.Cells(closedWs.Rows.Count, "A").End(xlUp).Row
' Loops backwards from the last row to the first row
For i = lastRow To 1 Step -1
' Checks if the entire row is blank
If WorksheetFunction.CountA(closedWs.Rows(i)) = 0 Then
' Deletes the row if it is blank
closedWs.Rows(i).Delete
End If
Next i
' Closes the workbook and saves changes
closedWb.Close SaveChanges:=True
' Re-enables screen updating
Application.ScreenUpdating = True
End Sub
This VBA code removes all blank rows from a specific worksheet in a closed workbook.
It operates on a workbook located at “C:\Sales Reports\Sales Report.xlsm”, specifically targeting the “Sheet1” worksheet.
The VB script opens this workbook, identifies the last used row in column A, and then iterates backwards through the rows. It uses the CountA function to check if each row is completely blank.
Blank rows are deleted, and after completing the operation on all rows, the workbook is saved and closed.
Screen updating is disabled during the script’s execution to enhance performance and is re-enabled once the operation is complete.
Also read: VBA to Find Last Row
Delete Blank Rows In All Worksheets of a Closed Workbook
Assuming you have a closed workbook named ‘Regional Sales.xlsx’ with blank rows in all worksheets. You can delete the blank rows using the following code:
Sub DeleteBlankRows()
' Disables screen updating to enhance efficiency
Application.ScreenUpdating = False
' Declares variables
Dim targetWb As Workbook
Dim targetWs As Worksheet
Dim lastRow As Long
Dim i As Long
Dim targetWorkbookPath As String
' Sets the path for the closed workbook
targetWorkbookPath = "C:\Sales Reports\Regional Sales.xlsx"
' Opens the target workbook
Set targetWb = Workbooks.Open(targetWorkbookPath)
' Iterates through each worksheet in the target workbook
For Each targetWs In targetWb.Sheets
' Finds the last used row in column A of the current worksheet
lastRow = targetWs.Cells(targetWs.Rows.Count, "A").End(xlUp).Row
' Loops backwards from the last row to the first row
For i = lastRow To 1 Step -1
' Checks if the entire row is blank
If WorksheetFunction.CountA(targetWs.Rows(i)) = 0 Then
' Deletes the row if it is blank
targetWs.Rows(i).Delete
End If
Next i
Next targetWs
' Closes the workbook and saves changes
targetWb.Close SaveChanges:=True
' Re-enables screen updating
Application.ScreenUpdating = True
End SubThe above VBA code deletes all blank rows from every worksheet in a specific closed workbook, located at “C:\Sales Reports\Regional Sales.xlsx”.
The script opens the workbook and loops through each of its worksheets. For each worksheet, it identifies the last row with data in column A and then iterates backward through the rows.
Using the CountA function, it checks whether each row is entirely blank. If a row is found to be empty, it is deleted. After processing all worksheets, the workbook is saved and closed.
Also read: Autofill to Last Row Using VBA in Excel
Delete Blank Rows In All Worksheets In All Closed Workbooks in a Specific Folder
Suppose you have several closed workbooks in a folder named ‘Sales Reports’ on drive C, and you want to remove blank rows in all worksheets in all the closed workbooks. You can use the code below to accomplish the task:
Sub DeleteBlankRowsInClosedWorkbooks()
' Disables screen updating for efficiency
Application.ScreenUpdating = False
' Declares variables
Dim folderPath As String
Dim fileName As String
Dim wb As Workbook
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
' Sets the path of the folder containing the workbooks
folderPath = "C:\Sales Reports\"
' Retrieves the first Excel file in the folder
fileName = Dir(folderPath & "*.xls*")
' Loops through all Excel files in the folder
Do While fileName <> ""
' Opens the current workbook
Set wb = Workbooks.Open(folderPath & fileName)
' Iterates through each worksheet in the workbook
For Each ws In wb.Sheets
' Finds the last used row in column A of the current worksheet
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Loops backwards from the last row to the first row
For i = lastRow To 1 Step -1
' Checks if the entire row is blank
If WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
' Deletes the row if it is blank
ws.Rows(i).Delete
End If
Next i
Next ws
' Closes the workbook and saves changes
wb.Close SaveChanges:=True
' Moves to the next file in the folder
fileName = Dir
Loop
' Re-enables screen updating
Application.ScreenUpdating = True
End Sub
The above VBA subroutine cleans up multiple closed workbooks in a specified folder (“C:\Sales Reports”) by deleting all blank rows in each workbook.
It first disables screen updating for efficiency. The VBA script uses the Dir function to loop through all Excel files (.xls, .xlsx, .xlsm, etc.) in the folder.
For each workbook, it opens the file, then iterates through each worksheet. Within each worksheet, the script determines the last row with data in column A and then loops backward from this row, checking for blank rows using the CountA function. Blank rows are deleted.
After processing all sheets in a workbook, the workbook is saved and closed. The loop continues until all Excel files in the folder have been processed. Finally, screen updating is re-enabled.
This method is effective for batch processing multiple workbooks, especially for data cleaning and organization purposes.
Also read: VBA to Insert Row in Excel
Delete Blank Rows From an Excel Table
Suppose you have blank rows in the table named ‘Electronics’ on ‘Sheet1’ of the current workbook. You can use the following code to delete the blank rows:
Sub DeleteBlankRowsTable()
' Declares variables
Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As Range
Dim i As Long
' Sets the worksheet and table object
Set ws = ThisWorkbook.Sheets("Sheet1")
Set tbl = ws.ListObjects("Electronics")
' Loops backwards through the rows of the table
For i = tbl.DataBodyRange.Rows.Count To 1 Step -1
' Sets the range as the current row in the table
Set rng = tbl.DataBodyRange.Rows(i)
' Checks if the row is blank
If Application.CountA(rng) = 0 Then
' Deletes the blank row
rng.Delete
End If
Next i
End Sub
The above VBA code removes blank rows from a specific table named “Electronics” in the “Sheet1” of the current workbook.
It starts by setting references to the worksheet and the table. The script then iterates backwards through the rows of the table’s data body range.
For each row, it checks if the row is completely blank using the CountA function. If a row is found to be empty, it is deleted.
This method is effective for cleaning up tables by removing unnecessary blank rows, which helps in maintaining the integrity and readability of the table data.
The approach of iterating backwards is important to ensure that the row indexing does not get disrupted when rows are deleted.
In this tutorial, I showed you some examples of deleting blank rows in Excel using VBA. I hope you found the tutorial helpful.
Other Excel VBA articles you may also like:
