TABLE filter net zero amounts

  • Thread starter Thread starter Sinner
  • Start date Start date
Columns("F").AutoFilter _
field:=1, _
Criteria1:=0
Rows.SpecialCells(xlCellTypeVisible).EntireRow.Delete
 
Columns("F").AutoFilter _
    field:=1, _
    Criteria1:=0
Rows.SpecialCells(xlCellTypeVisible).EntireRow.Delete







- Show quoted text -
Click to expand...

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.
 
The code was more complicated than I originally expected

Sub removerows()

Columns("F").AutoFilter _
field:=1, _
Criteria1:=0
Rows.SpecialCells(xlCellTypeVisible).EntireRow.Delete

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
 
The code was more complicated than I originally expected

Sub removerows()

Columns("F").AutoFilter _
    field:=1, _
    Criteria1:=0
Rows.SpecialCells(xlCellTypeVisible).EntireRow.Delete

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







- Show quoted text -
Click to expand...

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.
 
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
 
The microsoft Autofilter wasn't working correctly.  I got rid of it anddid
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









- Show quoted text -
Click to expand...

Thx Joel. It works as was required.
One last bit if you can add.
After this macro ends & we have the desired table, is there a
possibility to remove a row for which there is value in cell in
columnE but corresponding columnD cell is blank. If you can add this
step in same macro but at the end of what it has done so far. Putting
it in start will vary the result.

Thx alot once again.
Waiting for reply.
 
Thx Joel. It works as was required.
One last bit if you can add.
After this macro ends & we have the desired table, is there a
possibility to remove a row for which there is value in cell in
columnE but corresponding columnD cell is blank. If you can add this
step in same macro but at the end of what it has done so far. Putting
it in start will vary the result.

Thx alot once again.
Waiting for reply.- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Joel,
A minor last minute modification needed.

Waiting for reply.
Thx.
 
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

RowCount = 2
Do While Range("F" & RowCount) <> ""
If Range("D" & RowCount) = "" And _
Range("E" & RowCount) <> "" Then

Rows(RowCount).Delete
Else
RowCount = RowCount + 1
End If

Loop

End Sub
 
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

RowCount = 2
Do While Range("F" & RowCount) <> ""
   If Range("D" & RowCount) = "" And _
      Range("E" & RowCount) <> "" Then

      Rows(RowCount).Delete
   Else
      RowCount = RowCount + 1
   End If

Loop

End Sub







- Show quoted text -
Click to expand...

Thx alot Joel for the much needed support :) worked like a charm ; )
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Little Problem, Difficult and Hard. 2
Running balance table, monthly/year grid 3
MS ACCESS 2013 Long Text filed filter by another tables Long text field 1
Zero Upload Speed 6
Code Not Recognizing Decimal 8
Combining IF Statement and Vlookup 0
Segregate Outstanding Payment 1
Showing all info under subtotals with large listings 1

Back
Top