Deleting rows in a subtotalled list

rexel12

Board Regular
Joined
Feb 19, 2002
Messages
86
Hi,

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.

TIA

Rex
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
Hi rexel12

On 2002-09-04 05:48, rexel12 wrote:
Hi,

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.

TIA

Rex

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?
 

rexel12

Board Regular
Joined
Feb 19, 2002
Messages
86
Jim,

The subtotals are created using the data/subtotals drop down menu options in excel (no pivot tables).

Regards

Rex.
 

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
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.
 

rexel12

Board Regular
Joined
Feb 19, 2002
Messages
86

ADVERTISEMENT

Jim,

Thanks. Do have any VBA code I might use to achieve this.

Regards

Rex
 

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
I can put a skeleton together for you..

what is the starting row of data?

what column is the subtotal in?
 

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
I roughed this up:

It assumes your subtotals are in column B

<pre>
Sub ZapSets()

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

Next Sell

Application.ScreenUpdating = True

End Sub
</pre>
 

rexel12

Board Regular
Joined
Feb 19, 2002
Messages
86
Jim,

You are an absolute STAR!

It worked perfectly.

Many thanks for your time and help.

Regards

Rex
 

Forum statistics

Threads
1,144,049
Messages
5,722,227
Members
422,417
Latest member
Johhny

We've detected that you are using an adblocker.

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.
Go back
Top