How to Delete Blank and Zero Lines in a Tab

[Henry1]

Hi I have a code that creates a data tab called :Consolidated" in Excel. This tab contains a bunch of rows that are either blank or have zero values. I would like to delete these lines.
Thanks!!

Thanks!!

 

[Richard U]

Sub Delete_blanks()
Dim lstrow As Long
Dim ctr As Long
Dim lstcol As Long

lstrow = GetLast(1, Sheets("MySheet").Cells)
lstcol = GetLast(2, Sheets("MySheet").Cells)
ctr = 1
Do While lstrow > ctr

If WorksheetFunction.Sum(Range(Cells(ctr, 1), Cells(ctr, lstrow))) = 0 Then
    Sheets("MySheet").Rows(ctr).Delete
    lstrow = lstrow - 1
Else
    ctr = ctr + 1
End If

Loop
End Sub


Option Explicit

Function GetLast(choice As Long, rng As Range)
'Ron de Bruin, 5 May 2008
' 1 = GetLast row
' 2 = GetLast column
' 3 = GetLast cell
    Dim lrw As Long
    Dim Lcol As Long

    Select Case choice

    Case 1:
        On Error Resume Next
        GetLast = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Row
        On Error GoTo 0

    Case 2:
        On Error Resume Next
        GetLast = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

    Case 3:
        On Error Resume Next
        lrw = rng.Find(What:="*", _
                       After:=rng.Cells(1), _
                       lookat:=xlPart, _
                       LookIn:=xlFormulas, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious, _
                       MatchCase:=False).Row
        On Error GoTo 0

        On Error Resume Next
        Lcol = rng.Find(What:="*", _
                        After:=rng.Cells(1), _
                        lookat:=xlPart, _
                        LookIn:=xlFormulas, _
                        SearchOrder:=xlByColumns, _
                        SearchDirection:=xlPrevious, _
                        MatchCase:=False).Column
        On Error GoTo 0

        On Error Resume Next
        GetLast = rng.Parent.Cells(lrw, Lcol).Address(False, False)
        If Err.Number > 0 Then
            GetLast = rng.Cells(1).Address(False, False)
            Err.Clear
        End If
        On Error GoTo 0
    If GetLast = 0 Then GetLast = 1
    End Select
End Function

 

[Fluff]

Duplicate to: VBA to Consolidate Multiple tans into one tab

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.