SUBTOTAL: Delete all rows associated with subtotal less than certain value

[bikerchick1111]

Hi!
I have a macro that creates two tabs off a raw data tab. One tab shows all locations where the subtotal of all line items for that location < 95% (this % is a calculated field, column L, based on two other column SUM Subtotals, column M subtotal divided by column N subtotal). This part works fine, that macro copies the data from the raw data tab, subtotals the data, and deletes all subtotals and the associated rows that make up that subtotal that are >=95%:

' Subtotal by Plant Name (GroupBy:=2) then sum up Hits and Transactions for that plant
   Worksheets("IRA < 95%").Activate
   Selection.Subtotal GroupBy:=2, Function:=xlSum, _
   TotalList:=Array(13, 14)
   
' For every row where a subtotal exists, divide the number of hits by the number of transactions and place in the IRA column as the total IRA

    LastRow = Cells(Rows.Count, 13).End(xlUp).Row
    For Each myRange In Range("L2:L" & LastRow)
    If myRange.Value = "" And myRange.Offset(0, 1).Value <> "" Then
    Range("L" & myRange.Row) = myRange.Offset(0, 1).Value / myRange.Offset(0, 2).Value
    myRange.NumberFormat = "0%"
    If myRange.Count > 1 Then Exit Sub
    Application.ScreenUpdating = False
    With myRange
        ' Highlight the entire row and column that contain the active cell
        Range(Cells(.Row, .CurrentRegion.Column), Cells(.Row, .CurrentRegion.Columns.Count + .CurrentRegion.Column - 1)).Interior.ColorIndex = 44
        End With
    Application.ScreenUpdating = True
    
End If
Next myRange

For i = Range("m" & Rows.Count).End(xlUp).Row To 2 Step -1
    With Cells(i, "m")
        If .HasFormula And InStr(1, .Formula, "SUBTOTAL", 1) > 0 And .Offset(0, -1).Value >= 0.95 Then
            Rows(Val(Mid$(.Formula, 14)) & ":" & i).Delete
        End If
    End With
Next 
End Sub

The problem is when I try to create the tab to show all subtotals > 95%. When I change the .Offset(0, -1).Value>=0.95 to .Offset(0, -1).Value<0.95, I get a blank sheet!! (and I do have subtotaled percentages > 95%)

I tried I few different %s... If I use .Offset(0, -1).Value = 0, it deletes anything with a 0% subtotal and displays everything else, if I use .Offset(0, -1).Value <0.50 it deletes anything with a subtotal < 50%, but it seems like any other value I put in there returns no data. I can't figure it out!! Any help with would appreciated! thank you!

[Bernie Deitrick]

You should post a workbook with an example data set, but I would try an approach with an extra column of formulas that identifies the data associated with each Subtotals (based on your sorting and labelling) and delete all the rows so identified.

[FDibbins]

bikerchick1111 welcome to the forum

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(I will add them for you - this time )