Keep hidden rows hidden when filtered

[RattlingCarp3048]

Is there a way to keep hidden rows hidden when filtering/unfiltering?

I have a code that will hide rows when certain criteria is met. However, when we use basic filter to examine the visible rows we noticed the hidden rows are now visible. Is there a way to keep them hidden regardless of the basic filter being used?

 

[RattlingCarp3048]

i took a slightly different approach. maybe this would be a new thread (?)

i created the helper column you suggested then made an event procedure so that when the workbook opens it will automatically filter the rows to hide the ones in questions. my only question would be how to make it more dynamic. the helper column will periodically change positions as i made it the last column of the entire spreadsheet.

Private Sub Workbook_Open()
Rows("2:2").Select
Selection.AutoFilter
Range("A2").Select
Selection.End(xlToRight).Select
ActiveSheet.Range("$A$2:$ER$467").AutoFilter Field:=148, Criteria1:="No"
Range("C2").Select
End Sub

 

[Joe4]

Are there always headers in row 1 that we can use to dynamically find this last column?

 

[RattlingCarp3048]

Are there always headers in row 1 that we can use to dynamically find this last column?

headers are now on row 2, always.

 

[RattlingCarp3048]

headers are now on row 2, always.

its the last column that will change on occassion.

 

[Joe4]

Try something like this:

Private Sub Workbook_Open()

    Dim lc As Long
    Dim rng As Range
   
'   Find last column on row 2 with data
    lc = Cells(2, Columns.Count).End(xlToLeft).Column
   
'   Build range to check
    Set rng = Range(Cells(2, 1), Cells(467, lc))
   
'   Filter
    Rows("2:2").AutoFilter
    ActiveSheet.rng.AutoFilter Field:=lc, Criteria1:="No"

End Sub

 

[RattlingCarp3048]

Try something like this:

Private Sub Workbook_Open()

    Dim lc As Long
    Dim rng As Range
   
'   Find last column on row 2 with data
    lc = Cells(2, Columns.Count).xlToLeft.Column
   
'   Build range to check
    Set rng = Range(Cells(2, 1), Cells(467, lc))
   
'   Filter
    Rows("2:2").AutoFilter
    ActiveSheet.rng.AutoFilter Field:=lc, Criteria1:="No"

End Sub

object doesnt support this property or method

debug highlighted lc = Cells(2, Columns.Count).xlToLeft.Column

 

[Joe4]

object doesnt support this property or method

debug highlighted lc = Cells(2, Columns.Count).xlToLeft.Column

Sorry, I had a typo. It should be:

    lc = Cells(2, Columns.Count).End(xlToLeft).Column

I went back and updated the code to reflect that.

 

[RattlingCarp3048]

Sorry, I had a typo. It should be:

    lc = Cells(2, Columns.Count).End(xlToLeft).Column

I went back and updated the code to reflect that.

same error ActiveSheet.rng.AutoFilter Field:=lc, Criteria1:="No"

 

[Joe4]

OK, try this:

Private Sub Workbook_Open()

    Dim lc As Long
    Dim rng As Range
   
'   Find last column on row 2 with data
    lc = Cells(2, Columns.Count).End(xlToLeft).Column
   
'   Build range to check
    Set rng = Range(Cells(2, 1), Cells(467, lc))
   
'   Filter
    rng.AutoFilter
    rng.AutoFilter Field:=lc, Criteria1:="No"

End Sub

 

[RattlingCarp3048]

OK, try this:

Private Sub Workbook_Open()

    Dim lc As Long
    Dim rng As Range
  
'   Find last column on row 2 with data
    lc = Cells(2, Columns.Count).End(xlToLeft).Column
  
'   Build range to check
    Set rng = Range(Cells(2, 1), Cells(467, lc))
  
'   Filter
    rng.AutoFilter
    rng.AutoFilter Field:=lc, Criteria1:="No"

End Sub

it seems to do the job! an entirely different approach to what should be a simple fix on the user's behalf. so the final solution was a conjunction of our two thoughts. I added a helper column at the very end of the document which is completely out out sight. it has a formula to categorize the rows into yes/no criteria. then with your help, made an event procedure to auto filter the helper column to No everytime the workbook opens. it solves all the issues we were having with the basic filter and still does not show the "hidden" rows when trying to print.

Thanks a ton!!