Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

vba routine for eliminating rows with all zeros from spreadsheet

  Asked By: Meenachi    Date: Feb 02    Category: MS Office    Views: 924
  

I'm a financial analyst and a very green VBA user. Part of my work
involves spreadsheets I download from various investment web sites.

Usually the spreadsheets have from 50 to 100 rows and have nine
columns. Invariably there are rows where the item for that row isn't
relevant and are therefore filled with zeros. I want to eliminate
all rows that have all zeros in them.

I've taken a shot at this myself but can't get it to do what I need
it to do.

Can someone suggest another approach?

Sub erase_zero_rows()
'
' erase_zero_rows Macro
' Macro recorded 6/19/2007 by
'
Dim iColumnToStart As Integer
Dim iColumnToEnd As Integer
Dim Rng As Range
Dim iRowToStart As Integer
Dim lRowsAll As Integer
Dim iRowIndex As Integer
Dim bNotZero As Boolean

iRowToStart = 4
lRowsAll = 69
iColumnToStart = 2
iColumnToEnd = 4

For Each Rng In Range(Cells(iRowToStart, iColumnToStart), Cells
(lRowsAll, iColumnToEnd))

iRowIndex = 0
bNotZero = False

Do While iRowIndex <= iColumnToEnd
If Rng.Offset(0, iRowIndex).Value <> 0 Then
bNotZero = True
Exit Do
End If
iRowIndex = iRowIndex + 1
Range("a1") = iRowIndex
Loop

If bNotZero = False Then
Rng.EntireRow.Delete shift:=xlUp
End If

Next Rng
End Sub

Share: 

 

7 Answers Found

 
Answer #1    Answered By: Murad Bashara     Answered On: Feb 02

I've never tried deleting rows, but below is what I use for hiding them;

Sub HideEmpty()
Sheet1.Unprotect
Application.ScreenUpdating = False

BeginRow = 20
EndRow = 1000
ChkCol = 1

For rowcnt = BeginRow To EndRow
If Sheet1.Cells(rowcnt, ChkCol).Value < 1 Then
Sheet1.Cells(rowcnt, ChkCol).EntireRow.Hidden = True
Else
If Sheet1.Cells(rowcnt, ChkCol).Value = "AutoHide.Sheet.Stop"
Then Exit For
Sheet1.Cells(rowcnt, ChkCol).EntireRow.Hidden = False
End If
Next rowcnt

Sheet1.Protect
Application.ScreenUpdating = True
End Sub

 
Answer #2    Answered By: Juan Reynolds     Answered On: Feb 02

your approach is pretty good for someone who says they are
green...
How about trying this...
Sub erase_zero_rows()
Dim rCnt As Integer, iCnt As Integer
Dim iColumnToStart As Integer
Dim iColumnToEnd As Integer
Dim iRowToStart As Integer
Dim iRowsAll As Integer

iRowToStart = 4
iRowsAll = 69
iColumnToStart = 2
iColumnToEnd = 4
iCnt = iRowToStart - 1
rCnt = iRowsAll
With Range(Cells(1, iColumnToStart), Cells(1, iColumnToEnd))
Do While rCnt
If WorksheetFunction.Sum(.Offset(iCnt, 0)) = 0 Then
.Offset(iCnt, 0).EntireRow.Delete
Else
iCnt = iCnt + 1
End If
rCnt = rCnt - 1
Loop
End With

End Sub

 
Answer #3    Answered By: Rafael Thompson     Answered On: Feb 02

do you want to delete the rows  where all the cells have zero value...

if the above said is true then find the answer below

Sub OM()
Dim lastrow As Integer
Dim lastcol As Integer

Worksheets("Sheet1").Activate
lastrow = Range("a65536").End(xlUp).Row
lastcol = 9 'because you are sure it is 9

Count = lastrow
i = 1
While (lastrow <> 0)
If (Range("A" & i) = 0 And Range("B" & i) = 0 And Range("C" & i)
= 0 And Range("D" & i) = 0 And Range("E" & i) = 0 And Range("F" & i)
= 0 And Range("G" & i) = 0 And Range("H" & i) = 0 And Range("I" & i)
= 0) Then
Range("A" & i).Select
Selection.EntireRow.Delete shift:=xlUp
End If
i = i + 1
lastrow = lastrow - 1
Wend
End Sub

Let me know if this solves your problem...

 
Answer #4    Answered By: Helga Miller     Answered On: Feb 02

Find the answer below

Sub OM()
Dim lastrow As Integer
Dim lastcol As Integer

Worksheets("Sheet1").Activate
lastrow = Range("a65536").End(xlUp).Row
lastcol = 9 'because you are sure it is 9

Count = lastrow
i = 1
While (lastrow <> 0)
If (Range("A" & i) = 0 And Range("B" & i) = 0 And Range("C" & i)
= 0 And Range("D" & i) = 0 And Range("E" & i) = 0 And Range("F" & i)
= 0 And Range("G" & i) = 0 And Range("H" & i) = 0 And Range("I" & i)
= 0) Then
Range("A" & i).Select
Selection.EntireRow.Delete shift:=xlUp
End If
i = i + 1
lastrow = lastrow - 1
Wend
End Sub

 
Answer #5    Answered By: Willard Washington     Answered On: Feb 02

You'll also need to stop the row increment, otherwise you bypass any
zero rows  that are consecutive.

code ...
While (lastrow <> 0)
If /condition/ Then
code ...
i=i-1
End If
i=i+1
code ...
Wend

 
Answer #6    Answered By: Emily Brown     Answered On: Feb 02

I provided would not
work if there were positive and negative numbers on the same row that
could total 0.

Perhaps, then, this will work.
if abs(worksheetfunction.min(.offset(icnt,0))) + worksheetfunction.max
(.offset(icnt,0)) = 0 then

 
Answer #7    Answered By: Jarrod Williams     Answered On: Feb 02

You got me 99.% of the way there. without i = i-1 before the End If it
would skip consecutive zero rows.

 




Tagged: