Hide Rows based on "A"'s Criteria

[adgjqetuo]

What am I doing wrong here?

I have a code and it doesn't error out, but it won't hide the rows either. I'm pretty sure the red is what needs to be altered. I've tried adding "Selection.", "Rows." and "Cells." and none of them are working.

Sub Test()
Dim Lngrow As Long
Lngrow = Range("I5").End(xlDown).Row
Do While Lngrow > 0
If Cells(Lngrow, 1).Value = "Filler" Then
Selection.EntireRow.Hidden = True
End If
Lngrow = Lngrow - 1
Loop
End Sub

[JBeaucaire]

When you are evaluating the same colum over and over again for the same value, looping isn't necessary. Just use the AutoFilter, filter the data by that column and that value, then do your "delete" on everything left visible.

Sub Test()
Dim LR As Long

'Add row1 for title to allow auotfilter to work
    Rows(1).Insert xlShiftDown
    Range("A1") = "Key"

'Turn on Autofilter, filter column A for "Filler"
    Range("A1").AutoFilter
    Range("A1").AutoFilter Field:=1, Criteria1:="Filler"

'If any rows apply, delete them
    LR = Range("A" & Rows.Count).End(xlUp).Row
    If LR > 1 Then _
        Rows(2 & ":" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Hidden = True

'Remove Autofilter
    ActiveSheet.AutoFilterMode = False

'Remove added row
    Rows(1).Delete xlShiftUp
End Sub

The addition of the row 1 appeared to be necessary because your original macro was evaluating all the way up to row1. If row 1 already has "titles", then you can remove the Insert and Delete code at the top and bottom.

If you have trouble editing this to your sheet, post it up and we'll help.

[adgjqetuo]

Thanks for the suggestion, but I don't want to delete any rows - I just want them hidden. It's hiding rows which are inside a subtotal, so deleting them messes the whole sheet up. I just want it to do what I have above - short and sweet.

[JBeaucaire]

My apologies for using the word "delete" instead of "hide". Everything else stated is still the applicable solution.

[adgjqetuo]

I just tried your code, and it errored out when adding the autofilters. The sheet has to start on A5, I changed that and it still errored out

I understand where your going with this code, but I really just want it to look at each individual row, and delete all rows with "Filler" in the "A" column - This sheet is a lot bigger and this is just one section of the code. Then go and hide any unneccessary or "empty" subtotal total rows.

I'm going to attach the sheet, but just so theres no confusion please note:

The sheet baisically makes a "fake" subtotal scenerio for everything that could possibly be put on the sheet originally to subtotal. The reason for this is because the way it sorts it, it can't account for a specific criteria and it will lump it together otherwise. If I make the "fake" subtotal it divides it out perfectly. As of now it works FINE, I just need to to erase all the empty or unused subtotals and all the "filler" rows.

Thanks for the quick response BTW

[JBeaucaire]

I don't see any filler rows on your sample. Can you expand to a large sampling that actually includes the info needing to be hidden?

Also, I know we're trying to solve one issue, but based on your last post, if you showed an example of the MATH you're trying to get right by adding these filler rows, perhaps we can solve that instead making this whole filler thing just go away.

[adgjqetuo]

Click on the "Subtotal" button - they will appear in white text on white background

Math involved?? There shouldn't be.... Filler makes it a place holder so it will subtotal correctly.

[JBeaucaire]

You can use AutoFilter to do most of the work you're doing in your CommandButton2 macro and significantly increase the speed by not looping through the whole dataset over and over again.

For instance, this is how I would write section #9, it leaves the FILLER rows hidden after painting them white...is this what you had in mind?:

'======================================================
            '9) White text for filler rows, hide rows
'======================================================
    lngRow = Range("I4").End(xlDown).Row
    Range("A4:I4").AutoFilter
    Range("A4:I4").AutoFilter Field:=1, Criteria1:="Filler"
    Range("A5:I" & lngRow).SpecialCells(xlCellTypeVisible).Font.ColorIndex = 2
    Range("A4:I4").AutoFilter Field:=1, Criteria1:="<>Filler"

Section 8 really only needs to find the one Grand Total cell, right? NO need to loop the entire sheet for that, just go to it directly:

'======================================================
            '8) Shades Grand Total column Dark Gray
'======================================================
    lngRow = Columns("F").Find("Grand Total", , xlValues, xlPart).Row
    Range(Cells(lngRow, 1), Cells(lngRow, 9)).Interior.ColorIndex = 16

You tend to manually select things before applying your actions. That's only required if you're human, VBA can act on cells/ranges directly without selecting them first...at all. Sections 5 & 6 demonstrate this:

'======================================================
            '5) Sorts data by PO#
'======================================================
On Error GoTo Error_Chk
    Range("A4", Range("J4").End(xlDown)).Sort Key1:=Range("E5"), Order1:=xlAscending, Key2:=Range("F5") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
        Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortNormal
'======================================================
            '6) Creates Sub Totals
'======================================================
    Range("A4", Range("J4").End(xlDown)).Subtotal GroupBy:=6, Function:=xlSum, TotalList:=Array(9), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=True

I would rewrite the whole FILLER macro without all the selecting and FillDown stuff:

Sub FILLER()
Application.ScreenUpdating = False

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp Sheet"
Range("A1:A16") = "Filler"
Range("D1") = "SU"
Range("D2") = "MP"
Range("D3") = "MH"
Range("D4") = "OC"
Range("D1:D4").Copy Range("D5:D16")
Range("E1:E16").NumberFormat = "@"
Range("E1:E4") = "01"
Range("E5:E8") = "02"
Range("E9:E12") = "04"
Range("E13:E16") = "09"
Range("I1:I16") = 0
Range("A1:I16").Copy
Sheets("Accrual Sheet").Activate

Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

Range("A5", Range("I5").End(xlDown)).Sort Key1:=Range("A5"), Order1:=xlAscending, _
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal

End Sub

[adgjqetuo]

I'm not that good with VBA, so if you can find a way to get it to do all of the below - by all means! Can you put it into the sheet and re-attach it?

I had it turn white to hide the filler column, but i'd rather have it FULLY hidden, as well as the blank subtotal rows.

[JBeaucaire]

Try using the Autofilter on the Totals column then to show only values greater than zero.

[adgjqetuo]

You know - I think that might be crazy enough to work!

[adgjqetuo]

After all that - thats all I needed to do! Thanks for helping me think outside the box!

[JBeaucaire]

We are a village. Glad to help.