I've got a huge list of subtotalled data. Is there a way of using VBA to scan through the list and where the subtotal is zero delete the subtotal row and all the rows associated with it.
I've got a huge list of subtotalled data. Is there a way of using VBA to scan through the list and where the subtotal is zero delete the subtotal row and all the rows associated with it.
Yes, you can do this. You would need to track the location of the first row in the "set". This would either be the very first line (before) any subtotals, or the line right after the subtotal.
Are your subtotals created by you or the SUBTOTAL function? Are you using Pivot?
The same logic applies... you will need to test the cell to see if it is a formula, then test to see if the value is zero... select the range of the first row of this set, and the row the subtotal is on and then delete with entirerow.
Dim wks1 As Worksheet
Dim Sell As Range
Dim CurrRow As Long
Dim LastRow As Long
Dim StartRow As Long
Dim Rng As Range
' turn off screen updating
Application.ScreenUpdating = False
' point to the worksheets
Set wks1 = Worksheets("Sheet1")
StartRow = 2
' point to Sheet1
wks1.Activate
' get the number of rows used on Sheet1
Range("B65536").Select
ActiveCell.End(xlUp).Select
LastRow = ActiveCell.Row
Set Rng = Range("B" & StartRow & ":B" & LastRow)
For Each Sell In Rng
CurrRow = Sell.Row
If Sell.HasFormula = True Then
If Sell.Value = 0 Then
Range(StartRow & ":" & CurrRow).EntireRow.Delete
End If
StartRow = CurrRow + 1
End If
We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel
Which adblocker are you using?
Disable AdBlock
Follow these easy steps to disable AdBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the icon in the browser’s toolbar. 2)Click on the "Pause on this site" option.
Go back
Disable AdBlock Plus
Follow these easy steps to disable AdBlock Plus
1)Click on the icon in the browser’s toolbar. 2)Click on the toggle to disable it for "mrexcel.com".
Go back
Disable uBlock Origin
Follow these easy steps to disable uBlock Origin
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
Go back
Disable uBlock
Follow these easy steps to disable uBlock
1)Click on the icon in the browser’s toolbar. 2)Click on the "Power" button. 3)Click on the "Refresh" button.
