| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TABLE filter net zero amounts
|
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
TABLE filter net zero amounts
Columns("F").AutoFilter _
field:=1, _ Criteria1:=0 Rows.SpecialCells(xlCellTypeVisible).EntireRow.Del ete "Sinner" wrote: Hi, I want to remove all zero balance in the file. File: http://savefile.com/files/1618048 Thx. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
TABLE filter net zero amounts
On Jun 19, 8:11*pm, Joel wrote:
Columns("F").AutoFilter _ * * field:=1, _ * * Criteria1:=0 Rows.SpecialCells(xlCellTypeVisible).EntireRow.Del ete "Sinner" wrote: Hi, I want to remove all zero balance in the file. File:http://savefile.com/files/1618048 Thx.- Hide quoted text - - Show quoted text - Thx for the reply Joel. Still the numbers with comulative zero in columnF are there like (-2000) & (+2000) for same entities. Do we have to fill the blank# cells in columnA first & then make a temp column to offset the balances & remove balances that are zero? Waiting for reply. Thx. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
TABLE filter net zero amounts
The code was more complicated than I originally expected
Sub removerows() Columns("F").AutoFilter _ field:=1, _ Criteria1:=0 Rows.SpecialCells(xlCellTypeVisible).EntireRow.Del ete FirstRow = 1 RowCount = FirstRow ID = Range("A" & RowCount) Do While Range("F" & RowCount) < "" NextID = Range("A" & (RowCount + 1)) If NextID < "" Then If ID < NextID Then 'sum the rows from FirstRow to present Row Set SumRange = Range(Range("F" & FirstRow), _ Range("F" & RowCount)) Total = WorksheetFunction.Sum(SumRange) If Total = 0 Then Rows(FirstRow & ":" & RowCount).Delete RowCount = FirstRow Else RowCount = RowCount + 1 FirstRow = RowCount End If ID = Range("A" & RowCount) End If Else RowCount = RowCount + 1 End If Loop End Sub "Sinner" wrote: On Jun 19, 8:11 pm, Joel wrote: Columns("F").AutoFilter _ field:=1, _ Criteria1:=0 Rows.SpecialCells(xlCellTypeVisible).EntireRow.Del ete "Sinner" wrote: Hi, I want to remove all zero balance in the file. File:http://savefile.com/files/1618048 Thx.- Hide quoted text - - Show quoted text - Thx for the reply Joel. Still the numbers with comulative zero in columnF are there like (-2000) & (+2000) for same entities. Do we have to fill the blank# cells in columnA first & then make a temp column to offset the balances & remove balances that are zero? Waiting for reply. Thx. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
TABLE filter net zero amounts
On Jun 19, 10:55*pm, Joel wrote:
The code was more complicated than I originally expected Sub removerows() Columns("F").AutoFilter _ * * field:=1, _ * * Criteria1:=0 Rows.SpecialCells(xlCellTypeVisible).EntireRow.Del ete FirstRow = 1 RowCount = FirstRow ID = Range("A" & RowCount) Do While Range("F" & RowCount) < "" * *NextID = Range("A" & (RowCount + 1)) * *If NextID < "" Then * * * If ID < NextID Then * * * * *'sum the rows from FirstRow to present Row * * * * *Set SumRange = Range(Range("F" & FirstRow), _ * * * * * * Range("F" & RowCount)) * * * * *Total = WorksheetFunction.Sum(SumRange) * * * * *If Total = 0 Then * * * * * * Rows(FirstRow & ":" & RowCount).Delete * * * * * * RowCount = FirstRow * * * * *Else * * * * * *RowCount = RowCount + 1 * * * * * *FirstRow = RowCount * * * * *End If * * * * *ID = Range("A" & RowCount) * * * End If * *Else * * * RowCount = RowCount + 1 * *End If Loop End Sub "Sinner" wrote: On Jun 19, 8:11 pm, Joel wrote: Columns("F").AutoFilter _ * * field:=1, _ * * Criteria1:=0 Rows.SpecialCells(xlCellTypeVisible).EntireRow.Del ete "Sinner" wrote: Hi, I want to remove all zero balance in the file. File:http://savefile.com/files/1618048 Thx.- Hide quoted text - - Show quoted text - Thx for the reply Joel. Still the numbers with comulative zero in columnF are there like (-2000) & (+2000) for same entities. Do we have to fill the blank# cells in columnA first & then make a temp column to offset the balances & remove balances that are zero? Waiting for reply. Thx.- Hide quoted text - - Show quoted text - Thx Joel for the concern. The code however is dowing two unusual things. -Deleting the header row. -Each time I run the macro, it is deleting a row from remaining data for no reason. Hope you can fix that. Thx alot once again. Waiting for reply. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
TABLE filter net zero amounts
The microsoft Autofilter wasn't working correctly. I got rid of it and did
the deletes in my code. Sub removerows() FirstRow = 2 RowCount = FirstRow ID = Range("A" & RowCount) Do While Range("F" & RowCount) < "" Grandtotal = Range("F" & RowCount) If Grandtotal = 0 Then Rows(RowCount).Delete Else NextID = Range("A" & (RowCount + 1)) If NextID < "" Then If ID < NextID Then 'sum the rows from FirstRow to present Row Set SumRange = Range(Range("F" & FirstRow), _ Range("F" & RowCount)) Total = WorksheetFunction.Sum(SumRange) If Total = 0 Then Rows(FirstRow & ":" & RowCount).Delete RowCount = FirstRow Else RowCount = RowCount + 1 FirstRow = RowCount End If ID = Range("A" & RowCount) End If Else RowCount = RowCount + 1 End If End If Loop End Sub "Sinner" wrote: On Jun 19, 10:55 pm, Joel wrote: The code was more complicated than I originally expected Sub removerows() Columns("F").AutoFilter _ field:=1, _ Criteria1:=0 Rows.SpecialCells(xlCellTypeVisible).EntireRow.Del ete FirstRow = 1 RowCount = FirstRow ID = Range("A" & RowCount) Do While Range("F" & RowCount) < "" NextID = Range("A" & (RowCount + 1)) If NextID < "" Then If ID < NextID Then 'sum the rows from FirstRow to present Row Set SumRange = Range(Range("F" & FirstRow), _ Range("F" & RowCount)) Total = WorksheetFunction.Sum(SumRange) If Total = 0 Then Rows(FirstRow & ":" & RowCount).Delete RowCount = FirstRow Else RowCount = RowCount + 1 FirstRow = RowCount End If ID = Range("A" & RowCount) End If Else RowCount = RowCount + 1 End If Loop End Sub "Sinner" wrote: On Jun 19, 8:11 pm, Joel wrote: Columns("F").AutoFilter _ field:=1, _ Criteria1:=0 Rows.SpecialCells(xlCellTypeVisible).EntireRow.Del ete "Sinner" wrote: Hi, I want to remove all zero balance in the file. File:http://savefile.com/files/1618048 Thx.- Hide quoted text - - Show quoted text - Thx for the reply Joel. Still the numbers with comulative zero in columnF are there like (-2000) & (+2000) for same entities. Do we have to fill the blank# cells in columnA first & then make a temp column to offset the balances & remove balances that are zero? Waiting for reply. Thx.- Hide quoted text - - Show quoted text - Thx Joel for the concern. The code however is dowing two unusual things. -Deleting the header row. -Each time I run the macro, it is deleting a row from remaining data for no reason. Hope you can fix that. Thx alot once again. Waiting for reply. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Pivot table - Filter - no filter by value | Excel Discussion (Misc queries) | |||
| Need VBA Code/Marcos To Hide Less than zero amounts. Not a filter | Excel Worksheet Functions | |||
| 11 $ amounts, 6 of the amounts must equal $3854.12, which 6? | Excel Discussion (Misc queries) | |||
| Filter records in a table to extract to another table | Excel Discussion (Misc queries) | |||
| single table with columns that have different amounts of rows | New Users to Excel | |||
Hybrid Mode
