Code to work on sub-total function

[Lisa]

I've recorded code to sub-total data in a worksheet (the sub-total function under data). Now what I'd like to do is eliminate any lines which sub-totals to zero if a certain column meets criteria.

Example: Subtotal on User field, sum on calc field. Then, if MatchFlag field = "Matched" and sub-total = 0, then delete all rows in that subtotal.

Is this possible? I'm at a loss on how to loop through the sub-totals(and how to refer to the sub-totals) and then eliminate the rows that meet the criteria.

Thanks for any help

Lisa

[cornbread]

How is your data arranged? How many subtotals are there? are they always going to be in the same cell? Will each subtotal cell always have a value in it? Do you care if the code clears the subtotal value if it is equal to zero? are your subtotals always the same number of cells or rows away from each other? If you give a little more info I might already have some code that does what you are looking for. If not I'm sure myself or some one else here can cook you some up real quick. I just need more parameters on what it should do.

[NateO]

Here's a starting point. This loops through all of your formulae in the activesheet looking for 'SUBTOTAL.' If it's equal to 0, and your key field is enabled, it grabs the range in the function, and deletes the cells comprising the subtotal:
In this example, Matched is two columns to the right of each subtotal function.

Sub clr()

'Macro recorded by Nate

Dim o As Integer, s As Integer

Dim z As String, cl As Range, frmcl As Range

On Error Resume Next

Set frmcl = Sheets(3).[a1].SpecialCells(xlFormulas)

If frmcl Is Nothing Then GoTo 1

    For Each cl In frmcl

        frm = cl.Formula

        If InStr(frm, "SUBTOTAL") And cl.Value = "0" _

        And cl.Offset(, 2).Value = "Matched" Then '2 columns to right

            o = InStr(cl.Formula, ",")

            s = InStr(cl.Formula, ")")

            z = Mid(cl.Formula, o + 1, s - 1 - o)

            cl.EntireRow.Delete 'deletes Subtotal Row

            Range(z).EntireRow.Delete 'deletes rows in Subtotal Range

        End If

    Next cl

End

1: MsgBox "Could not find any formulae in the active sheet. Try Again."

End Sub

Caution, Just because a Subtotal = 0 does not mean that all values above equal 0. It may make more sense to loop through the range, which we just grabbed, and look for blanks or zeros and eliminate those. Food for thought. Let us know.


_________________
Cheers, NateO

[ This Message was edited by: NateO on 2002-05-14 16:07 ]

[Lisa]

Thanks for the input Nate and Cornbread. I'm home now and don't have the file with me, but will try it in the morning (midwest US time)

Lisa

[NateO]

Hey Lisa, make sure you read the last part closely, when you say matched field are you talking about a field beside each entry (that is part of the subtotal function)? If so, we'll need to use the extended version I haven't posted yet. Post back.

[cornbread]

Well it's morning North West time so mabye you have slept in. Nate's sub is almost exactly what I have for clearing a range based on what range is in a formula. I may be out of the office today but I will check this thread again later in the day. Post back
if you need more code than what Nate gave or if you need a different solution.

[Lisa]

Good morning, Nate and Cornbread...

Some additional info:
1) There won't be any zeros or blanks in the fields I'm subtotalling... I'm attempting to get rid of entries that equal zero, say 3 lines with 25.00, -12.00, -13.00.

2) The "Matched" data is not on the subtotal row, but on the rows the contain the data being subtotalled. So I put a -1 in Nate's code to at least check the last row. In a perfect world, I'd like to verify that all those say "Matched" before eliminating the range.

3) I moved the "frmcl" to AL1 instead of A1, as I have a column header in A1. Is this the right thing to do?

4) Nate, I've spent over an hour trying to decipher the InStr and Mid section... (I really wasn't sleeping in, Cornbread!) Can you tell me what's happening there? I'm trying to learn here, not just solve immediate problems.

5) In my 3300 line file (before the subtotals are put in), approx 1700 lines cleared - that's very good. However, there are probably another 1000 lines that should have cleared. I'll need to do a little more research to see if I can discern a pattern.

6) Here's the code I'm using... pretty much verbatim Nate's code.

Sub clr()
'Macro recorded by Nate
Dim o As Integer, s As Integer
Dim z As String, cl As Range, frmcl As Range
On Error Resume Next
Set frmcl = Sheets("input").[al1].SpecialCells(xlFormulas)
If frmcl Is Nothing Then GoTo 1
For Each cl In frmcl
frm = cl.Formula
If InStr(frm, "SUBTOTAL") And cl.Value = "0" _
And cl.Offset(-1, -16).Value = "Matched" Then '16 columns to left
o = InStr(cl.Formula, ",")
s = InStr(cl.Formula, ")")
z = Mid(cl.Formula, o + 1, s - 1 - o)
cl.EntireRow.Delete 'deletes Subtotal Row
Range(z).EntireRow.Delete 'deletes rows in Subtotal Range
End If
Next cl
End
1: MsgBox "Could not find any formulae in the active sheet. Try Again."
End Sub


Like I said, I'm not sure why it's not working for all the rows which meet the criteria.

Thanks for all your help and education.

Lisa

[Vas]

I just thought I might give a quick help with the instr part.

I haven't read the rest of the thread very carefully, so this is pretty much a minimal help.

The function InStr returns the position of the second string in the first.

o = InStr(cl.Formula, ",")

means that o is the position of the first "," in cl.Formula.

s = InStr(cl.Formula, ")")

is the position of the first ")"


Mid is the string which begins in the second argument, and have the length of the third argument.
This means that

z = Mid(cl.Formula, o + 1, s - 1 - o)

returns the part of cl.Formula that is between the first comma (o+1) and the first right parenthesis (s-1-o).

So in "(3,4)" this will return 4.

I have no idea what the code's for... I'm just telling you what it do.

[Lisa]

Thanks, Vas. That does make sense with Nate's code... I couldn't for the life of me figure it out. The help files, especially for a novice, can be very confusing.

Lisa

[Lisa]

Nate -

I think I've got a pattern!

The first group that should have been deleted was, the next one wasn't. Then the one after that one (3) was, the next one (4) wasn't. Etc...

Lisa