Share via

Delete empty rows with Macro

Anonymous
2019-02-25T18:36:02+00:00

Hello,

I need help in creating a macro to delete rows that are randomly placed in my report.  Normally I would record a macro in Excel but because the rows are located on a different row each day after the report is refreshed - I don't know how to record the macro so the empty rows would be deleted.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2019-02-25T20:36:56+00:00

    Try this version

    Sub DeleteRows()

    Dim iLastRow As Long

    Dim i As Long

        iLastRow = Cells(Rows.Count, "A").End(xlUp).Row

        Rows(1).EntireRow.Delete

        For i = iLastRow To 1 Step -1

            If Cells(i, "A").Value = "" Then

                Rows(i).Delete

            End If

        Next i

    End Sub

    Gord

    2 people found this answer helpful.
    0 comments
  2. Anonymous
    2019-02-25T20:42:40+00:00

    Hi,

    try below code

    Sub Deleterows()

    Dim cell As Range

    For Each cell In Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)

        With cell

            If .Value = vbNullString Then

                cell.EntireRow.Delete

            End If

        End With

    Next

    End Sub

    Regards

    1 person found this answer helpful.
    0 comments
  3. Anonymous
    2019-02-25T19:40:49+00:00

    Got a run-time error 438 - Object doesn't support this property or method in VBA I tried to run the code in Excel.

    Also I need to delete column headings along with the empty rows between the reports with the VBA.  Will you update the code to include deleting the column headings?

    1 person found this answer helpful.
    0 comments
  4. DaveM121 868.4K Reputation points Independent Advisor
    2019-02-25T18:44:43+00:00

    Hi Lizzie

    Here is a VBA macro that will do that for you

    Select the range of cells that you want to remove blank rows from and then run the macro . ..

    Public Sub DeleteBlankRows()

    Dim SourceRange As Range

Dim FullRow As Range

Set SourceRange = Application.Selection

If Not (SourceRange Is Nothing) Then

    Application.ScreenUpdating = False

    For I = SourceRange.Rows.Count To 1 Step -1

        Set FullRow = SourceRange.Cells(I, 1).FullRow

        If Application.WorksheetFunction.CountA(FullRow) = 0 Then

            FullRow.Delete

        End If

    Next

    Application.ScreenUpdating = True

End If

    End Sub

    0 comments