VBA to hide rows based upon column content

[lsu8]

To start with, I am a novice at using VBA. Using guidance from this website, I'm getting the expected output, but I find it to be slow. Is there a better way? Also, I will need to 'save as' to a different file name at work; will all the macros transfer? See attached file. Thanks in advance.

Sub CIN()
Application.ScreenUpdating = False

Dim Rng As Range
Dim cell As Range

Rows.Hidden = False
Lastrow = Range("A" & Rows.Count).End(xlUp).Row '
Set Rng = Range("AO1:AO220") 'choose column where value exists

For Each cell In Rng
If cell.Value = "1" Then 'Change the value based on which the rows need to be hidden
cell.EntireRow.Hidden = True
End If
Next cell
Range("C6").Activate
ActiveSheet.PageSetup.PrintArea = "$A$4:$AJ$200"
End Sub

 

[NARAYANK991]

Hi ,

Try this :

Sub CIN()
    Dim Rng As Range, cell As Range
    Application.ScreenUpdating = False

    Rows.Hidden = False
    Lastrow = Range("A" & Rows.Count).End(xlUp).Row          '
    Set Rng = Range("AO2:AO220") 'choose column where value exists
    Rng.SpecialCells(xlCellTypeConstants, 1).EntireRow.Hidden = True
    'For Each cell In Rng
    '    If cell.Value = "1" Then  'Change the value based on which the rows need to be hidden
    '      cell.EntireRow.Hidden = True
    '    End If
    'Next cell
    Range("C6").Activate
    ActiveSheet.PageSetup.PrintArea = "$A$4:$AJ$200"
End Sub

I have commented out the code for the loop , replacing it by the following statement :

Rng.SpecialCells(xlCellTypeConstants, 1).EntireRow.Hidden = True

Narayan

 

[lsu8]

Narayan - your suggestion worked! Thanks for your help!

 

[jdavis]

I am not sure if this is the right forum for this question but here goes. I have two buttons that hide columns based on processes being run. This spreadsheet would be much more readable if I could also hide the "inactive" (col C) rows. I cannot get anything to work with the existing buttons.

I have uploaded a file for your review.

Thanks