Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows (with zeros) with a macro | Excel Worksheet Functions | |||
Deleting rows (with zeros) with a macro | Excel Worksheet Functions | |||
How do I create a macro to add zeros in front of a value? | Excel Worksheet Functions | |||
Macro Help replacing dropped zeros | Excel Discussion (Misc queries) | |||
Help with leading Zeros when running Macro | Excel Worksheet Functions |