Excel Delete blank or zero rows 2

[vaneagle]

Any ideas on how I can remove blank or zero rows??

Sorting is not an option.....
would prefer a marco option....

basically the data is such:

12345 ttt aaa ccc
0
56778 eee aaa qqq
(blank)
etc

 

[Di0genes]

Hello,

try this:

Sub emtyrowsdelete()

'--dimmensionieren---
    Dim spalte, zelle As String
    Dim zeile As Integer
    Dim isnullcounter As Integer
'--initialisieren---
    spalte = "A"
    zeile = 1
    isnullcounter = 0
            
'--und los gehts --
    While isnullcounter < 6
        zelle = spalte + CStr(zeile)
        Range(zelle).Select
        If IsNull(ActiveCell.Value) Or ActiveCell.Value = 0 Then
            isnullcounter = isnullcounter + 1
            Selection.EntireRow.Delete
        Else
            isnullcounter = 0
            zeile = zeile + 1
        End If
    Wend
End Sub

You can change the stop parameter "isnullcounter" in what you need.

Regards
Daniel

 

[Loomah]

Hi
Here's another possibility. However this will not work if, for example, you have positive & negative values that cancel each other out (eg 1, 2, 3, -2, -4)

Sub ClearNilOrEmptyRows()
Dim lRow As Long
Dim lCnt As Long
lRow = Cells.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row
For lCnt = lRow To 1 Step -1
    If WorksheetFunction.CountA(Rows(lCnt)) = 0 Or _
        WorksheetFunction.Sum(Rows(lCnt)) = 0 Then
    Rows(lCnt).EntireRow.Delete
    End If
Next
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[vaneagle]

Thanks very much guys!!

they worked!!!

A star for you both!!