How to delete rows with no entries?

Question

I have this situation

The code deletes rows A3 and A10, though there are values at B3 and D10.

I want to delete rows across all the sheets in the workbook.

Sub Delete()
Dim xlastrow As Integer
Dim xrow As Integer
xrow = 1

Range("a65000").End(xlUp).Select
xlastrow = ActiveCell.Row

Do Until xrow = xlastrow

    If Cells(xrow, 1).Value = "" Then
        Cells(xrow, 1).Select
        Selection.EntireRow.Delete
        
        xrow = xrow - 1
        xlastrow = xlastrow - 1
    End If
        
    xrow = xrow + 1

Loop

End Sub

Answer 1

 Dim r As Range
 Dim i As Integer
 i = 3
 Set r = Rows(i)
 MsgBox "Number of cells with value " + Str(WorksheetFunction.CountA(r))

This will return the text "Number of cells with value" with the number of cells with a value in row 3.

For an empty row this should return 0.

NOTE: If you delete row 3, then you need to check row 3 again, because it will contain the previous values of row 4 ...

Answer 2

To check for all cells of a row, you can use the function CountA. As you are talking about dealing with lots of sheets, you should create a Sub that gets a worksheet as parameter and work on that sheet rather than relying on the active sheet. And you should avoid to use Select

Deleting data should always be done in reverse order, this makes it much easier to implement. Your routine could look like

Sub DeleteEmptyRows(ws As Worksheet)
    Dim row As Long
    For row = ws.UsedRange.Rows.Count To 1 Step -1
        Dim r As Range
        Set r = ws.Cells(row, 1).EntireRow
        If WorksheetFunction.CountA(r) = 0 Then
            r.Delete
        End If
    Next
End Sub

Answer 3

This will check the entire length of the rows (for numbers and / or text values). And remove blank rows. It will also only run delete rows statement once, improving performance.

Option Explicit

Sub removeBlankRows()

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Dim xlastrow As Long
    xlastrow = ws.Range("a65000").End(xlUp).Row

    Dim xrow As Long
    
    For xrow = xlastrow To 1 Step -1
    
        Dim c As Long
        c = WorksheetFunction.CountA(ws.Rows(xrow))
        
        Dim d As Long
        d = WorksheetFunction.Count(ws.Rows(xrow))
        
        If c = 0 And d = 0 Then
        
            Dim deleteRng As Range
            If deleteRng Is Nothing Then
                Set deleteRng = ws.Cells(xrow, 1)
            Else
                Set deleteRng = Union(ws.Cells(xrow, 1), deleteRng)
            End If
            
        End If
        
    Next

    If Not deleteRng Is Nothing Then deleteRng.EntireRow.Delete
    
End Sub

Answer 4

Two things you need:

1. How to know that a row is empty?

The answer can be found in this other post (you need to use the check Application.CountA(SomeCell.EntireRow)=0).

2. How to delete entries from a sorted collection?

"What? A sorted collection? What are you talking about?"
Well, you want to go from first row to last row and delete some of them (the simple fact that I can talk about first and last row means that the whole idea is sorted). There is only one correct way of doing this (plenty of explanations about this on the internet, it all comes down to the fact that you keep an index of your location, when you delete a row and you increase your index, you might jump over a row that you need to delete):

Start at the last row and go back to the first.

Good luck

Answer 5

What if last row doesn't have a value in Column A, but in a different column?
Anyway, I think this a a full solution, other then protecting against the sheet being protected.
It uses a lot of small functions to make it easier to understand. it doesn't rely on the COUNTA or COUNT functions.
LastRowInRange will also work with subset ranges, instead of the entire sheet.

Sub ExampleCall()
Dim Sheet As Worksheet
    ' Clean up all sheets in ActiveWorkbook
    For Each Sheet In ActiveWorkbook.Worksheets
        DeleteEmptyRows Sheet
    Next
End Sub

DeleteEmptyRows

Sub DeleteEmptyRows(Optional Sheet As Worksheet)
Dim LastRow As Long, Row As Long
Dim DeleteRange As Range
    If Sheet Is Nothing Then Set Sheet = Application.ActiveSheet
    LastRow = LastRowInRange(Sheet.UsedRange)
    For Row = Sheet.UsedRange.Row To LastRow
        If IsRowEmpty(Row, Sheet) Then Set DeleteRange = MakeUnion(DeleteRange, Sheet.Cells(Row, 1))
    Next
    If Not DeleteRange Is Nothing Then DeleteRange.EntireRow.Delete
End Sub

LastRowInRange

Public Function LastRowInRange(Target As Range) As Long
Dim Column As Range
Dim Row As Long, MaxRow As Long
    If Target Is Nothing Then Exit Function
    For Each Column In Target.Columns
        ' This check added, in case you want to look into a subset range and not an entire table/sheet.
        If Column.Cells(Column.Row + Column.Rows.Count - 1) <> vbNullString Then
            MaxRow = Column.Row + Column.Rows.Count - 1
            Exit For
        Else
            Row = Column.Cells(Column.Row + Column.Rows.Count).End(xlUp).Row
            If Row > MaxRow Then MaxRow = Row
        End If
    Next
    LastRowInRange = MaxRow
End Function

IsRowEmpty

Public Function IsRowEmpty(Optional Row As Long = 1, Optional Sheet As Excel.Worksheet) As Boolean
Dim Col As Long
    If Sheet Is Nothing Then Set Sheet = Application.ActiveSheet
    Col = GetLastCol(Row, Sheet)
    IsRowEmpty = (Col = 1) And (Trim(Sheet.Cells(Row, 1)) = vbNullString)
    'For debuging, uncomment the following:
    'If Sheet.Cells(Row, Col) <> vbNullString Then
    '    Debug.Print "Found something in Row " & Row & ", Col " & Col & ": """ & Sheet.Cells(Row, Col) & """"
    'End If
End Function

GetLastCol

Public Function GetLastCol(Optional Row As Long = 1, Optional Sheet As Excel.Worksheet) As Long
    If Sheet Is Nothing Then Set Sheet = Application.ActiveSheet
    GetLastCol = Sheet.Cells(Row, Sheet.Columns.Count).End(xlToLeft).Column
End Function

MakeUnion

Public Function MakeUnion(Arg1 As Range, Arg2 As Range) As Range
    If Arg1 Is Nothing Then
        Set MakeUnion = Arg2
    ElseIf Arg2 Is Nothing Then
        Set MakeUnion = Arg1
    Else
        Set MakeUnion = Union(Arg1, Arg2)
    End If
End Function

Answer 6

Finally, I learnt to record a macro and recorded my own macro while using the data filter. modified it accordingly(to search generic columns) and deleted blank rows using the macro. and created also a short key for ease of executions.