excel97: filtering out the zeros macro
 

hello,

i wrote a simple code to filter out the zeros in three different
columns. for instance:

loanaccount balance balance2 cashbal
aaaaa $4567.89 $0.00 $0.00
ggghh $0.00 $3456.71 $0.00
eeeeei $0.00 $0.00 $0.00
rrrrrr $0.00 $0.00 $8760.99
kmlno $0.00 $0.00 $0.00

so basically loan accounts aaaaa, ggghh, and rrrrrr should be my end
result but neither three loans appear after i run my macro. did i
write the for counter wrong? what did i leave out?

thanks in advance.

here is the code--

Sub FilterZerosOut()
ActiveWindow.Zoom = 85
Range("A1").Select
ActiveCell.FormulaR1C1 = "INTERNAL #"
Range("B1").Select
ActiveCell.FormulaR1C1 = "OBLIGOR #"
Range("C1").Select
ActiveCell.FormulaR1C1 = "LOAN ACCOUNT"
Range("D1").Select
ActiveCell.FormulaR1C1 = "BALANCE"
Range("E1").Select
ActiveCell.FormulaR1C1 = "BALANCE2"
Range("F1").Select
ActiveCell.FormulaR1C1 = "CASHBALANCE"
Range("G1").Select
ActiveCell.FormulaR1C1 = "NEXT INT DATE"
Rows("1:1").Select
Selection.Font.Bold = True
Selection.Interior.ColorIndex = 15

'Sort by = 0.00
Range("D2").Select
If ActiveSheet.AutoFilterMode Then

Else
Cells.AutoFilter
End If


For Counter = 4 To 6
Cells.AutoFilter field:=Counter, Criteria1:="=0" 'not equal to
zero
Next Counter

End Sub

excel97: filtering out the zeros macro
 

If it needs to delete the zeros, try the following:

dim Col as integer
dim Row as integer

For Col = 1 to 7
row = 1
for row = 1 to 4
if cells(row,col).value =0 then cells(row,col).value = ""
next row
next col

It will search the cells one by one and if the value in the cell i
zero, it empties the cell

--
Message posted from http://www.ExcelForum.com

excel97: filtering out the zeros macro
 

Sorry... misunderstood what you were trying to do. Attempt #2:

sub HideEmptyRows

Dim Col as integer
Dim Row as integer
Dim CountingValue as integer

range(cells(1,1),cells(100,1)).entirerow.hidden = false
For row = 1 to 100
col = 2
for col = 2 to 100 '(since column A contains the account number, it i
not included)
Countingvalue = countingvalue +trim$(cells(row,col).value)
next col
if countingvalue = 0 then
cells(row,1).entirerow.hidden = true
else countingvalue = 0
end if
Next row

end sub


When using this script, make sure that all cells within the range (th
row / col statements) contain a value (more specifically, a integer)
otherwise the trim$(cells(row,col).value) statement will generate a
error

--
Message posted from http://www.ExcelForum.com