Delete Rows In A Column That Are Blank

1 minute read

Here I outline lots of ways that you can delete blanks rows in any chosen column.

'## If you have a Column with data and blank Cells,
' you can delete the blank Rows using this method
' starting at Cell "A2" or for Column "A"
' (requires error trap for xlCellTypeBlanks if none exist): ' delete any Rows containing blank Cells in Column 1, "A" (Short Notation method included)
On Error Resume Next
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
' [A:A].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0 ' delete any Rows containing blank Cells in Column A, starting at Cell "A2" (Short Notation method included)
On Error Resume Next
Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
' [A2:A & Cells(Rows.Count, "A").End(xlUp).Row)].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
On Error GoTo 0 ' delete any Rows containing blank Cells in the Active Worksheet for the Used Range
On Error Resume Next
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
On Error GoTo 0 ' ======================================================================================================
' ## Delete empty rows in the selected range only
' works for both ranges and tables as deletes one row at a time
'=======================================================================================================
Sub DeleteEmptyRowsSelection() ' Vars Dim rng As Range Dim rngDelete As Range Dim RowCount As Long, ColCount As Long Dim EmptyTest As Boolean Dim RowDeleteCount As Long, ColDeleteCount As Long Dim X As Long Dim UserAnswer As Variant ' Test a range selected If TypeName(Selection)  "Range" Then Exit Sub ' If the activesheet is protected If ActiveProtected = True Then Exit Sub ' Analyze the UsedRange Set rng = Application.Selection rng.Select RowCount = rng.Rows.Count ColCount = rng.Columns.Count DeleteCount = 0 ' Optimize Code Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Application.EnableEvents = False ' Loop Through Rows & Accumulate Rows to Delete For X = RowCount To 1 Step -1 ' Is Row Not Empty? If Application.WorksheetFunction.CountA(rng.Rows(X)) = 0 Then
' ' If StopAtData = True Then Exit For
' Else If rngDelete Is Nothing Then Set rngDelete = rng.Rows(X) Set rngDelete = Union(rngDelete, rng.Rows(X)) RowDeleteCount = RowDeleteCount + 1 End If Next X ' Delete Rows (if necessary) If Not rngDelete Is Nothing Then rngDelete.EntireRow.Delete Shift:=xlUp Set rngDelete = Nothing End If ' Refresh UsedRange (if necessary) If RowDeleteCount + ColDeleteCount > 0 Then ActiveSheet.UsedRange Else MsgBox "No blank rows or columns were found!", Title:="Delete empty rows or columns - " & ActiveSheet.Name End If ' Error Handler
ExitMacro: Application.Calculation = xlCalculationAutomatic Application.EnableEvents = True rng.Cells(1, 1).Select End Sub

Tags: deleting-data, Dynamic Ranges

Categories: vba

Updated: November 16, 2018

Twitter Facebook LinkedIn

Leave a Comment