Hide / Unhide Rows VERY SLOW - Please Help

[Fadil]

Hi,

I have about 5000 rows where the column "AY - AY1:AY5008" will have value of True or False, so I want to hide all rows that have value "FALSE"
I have a code that does this, but it is working very very slow
Is there anyway to speed this up or perhaps it will work slow because of the number of rows I have
I am pasting below the code that I am using
Please help

Sub Hide()
Dim Lastrow As String
Dim Rng As Range, cell As Range

Application.ScreenUpdating = False

On Error Resume Next
With ThisWorkbook.Worksheets("ProjectScheduleDetails")
    .Rows.Hidden = False
    .Cells(.Rows.Count, 1).Select
    If ActiveCell.Value = "" Then
        Lastrow = Selection.End(xlUp).Row
    Else
        Lastrow = Rows.Count
    End If
  
    'Set Rng = Range("AY1:AY" & Lastrow) 'choose column where value exists
    Set Rng = Range("AY1:AY5008") 'choose column where value exists
       For Each cell In Rng
            If cell.Value = "FALSE" Then  'Change the value based on which the rows need to be hidden
               cell.EntireRow.Hidden = True
            End If
        Next cell
    End With
Application.ScreenUpdating = True

End Sub

 

[Hui]

Try:

Sub Hide()
Dim Lastrow As String
Dim Rng As Range, cell As Range

Application.ScreenUpdating = False

On Error Resume Next
ThisWorkbook.Worksheets("ProjectScheduleDetails").Rows.Hidden = False
  
Lastrow = Range("AY" & Rows.Count).End(xlUp).Row

Set Rng = Range("AY1:AY" & Lastrow) 'choose column where value exists
For Each cell In Rng
  If cell.Text = "FALSE" Then cell.EntireRow.Hidden = True
Next cell

Application.ScreenUpdating = True

End Sub

 

[Marc L]

Hi !

Fastest is to use an advanced filter on TRUE, so all FALSE lines will be hidden
and in less than 10 code lines !

Attach a .xlsx workbook (without code)
if you don't succeed with this easy method …
​

 

[Fadil]

I will try the code Hui sent for now and see if it works.

However, I don't know how to apply advanced filter! :-(
I will google and see how that works

 

[Marc L]

Advanced filter = instant result !

Example in this thread post #3 …
​

 

[Fadil]

I may be an idiot but I can't work the advanced filter code. :-(

What I have is a "Summary Sheet" where I apply filters and based on those filters, data is filtered in another sheet for example "Summary Sheet Detailed Results". And then a third sheet which takes the sheet ""Summary Sheet Detailed Results" to a more detailed level so in this sheet being the third sheet I am trying to see only the results that have TRUE on the column AY.

For example sheet 2 will have total of 999 rows where in sheet 3 that will be total of 5008 rows and on this sheet of 5008 rows I want to apply the filter that when I open this sheet only the field TRUE in AY column to remain visible.

The code I am writing below is cutting the time down to 3 minutes now from the infinity that I had before but still this is too long.
Any help? Should I upload the excel sheet perhaps it will help to understand my issue?

Sub Hide_Hui()
Dim Lastrow As String
Dim Rng As Range, cell As Range

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

On Error Resume Next
ThisWorkbook.Worksheets("ProjectScheduleDetails").Rows.Hidden = False
Lastrow = Range("AY" & Rows.Count).End(xlUp).Row

Set Rng = Range("AY1:AY" & Lastrow) 'choose column where value exists
For Each cell In Rng
  If cell.Text = "FALSE" Then cell.EntireRow.Hidden = True
Next cell

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

 

[Hui]

Uploading real data is always the best way to get the best result

 

[Fadil]

Here is the spreadsheet sample then. I am uploading the sheet where I am trying to apply this filter on.
This sheet in my working workbook is made all out of VLOOKUP formulas which reads the data from another sheet. I cleaned the VLOOKUP formulas here so I just left the values.
I had to upload it as ZIP since it was saying file is too large

 

[Marc L]

Sorry but the FileFormats converter does not succeed to open
your file in my Excel 2003, I will be back with a 2007 version in 3 days …

​

 

[Hui]

Make the following changes:

AY1:AY8 Clear contents
AY9: =IF(AX9="P",TRUE,FALSE)
Copy AY9 Down

AZ1: FILTER
AZ2: True

This code takes 1/2 a second

Sub Hide_Rows()

  Range("AY8:AY5008").AdvancedFilter _
  Action:=xlFilterInPlace, _
  CriteriaRange:=Range("AZ1:AZ2"), _
  Unique:=False
End Sub

You'll also need this:

Sub Unhide_all()
  ActiveSheet.ShowAllData
End Sub

 

[Hui]

See attached file:

 

[Fadil]

Hi Hui
For some reason the below code is going to error "Debug" when I run it.

Sub Unhide_all()
ActiveSheet.ShowAllData
EndSub

However,
I was able to input the code you sent me for advanced filter and when I have few records or as many as 1000 records, it hides and unhides rows automatically and it is working very fast.
I added some steps to the advanced filter code you sent me above and it's working great.
Please advise if I'm adding one too many extra steps, although it works very fast, it's great

Thank you very much
Fadil

Sub Hide_Row()

Sheets("ProjectScheduleDetails").Visible = True
Sheets("ProjectScheduleDetails").Activate
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False

Range("AY8:AY5008").AdvancedFilter _
  Action:=xlFilterInPlace, _
  CriteriaRange:=Range("AZ1:AZ2"), _
  Unique:=False

Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Hui]

You probably ran the Unhide_All code when the data was not filtered

So change the Unhide_All code as below

Sub Unhide_all()
If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
end sub

 

[Hui]

I wouldn't use the :

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlManual
​

Code as the Advanced filter is so fast, but it won't hurt either

 

[Fadil]

This is the final code I have and it is working very fast.
Using the donate button on this page, I made a small donation in your name as a thank you note.

Thank you very much

The reason I run the Unhide command is because I keep the sheet hidden at all times and it's only open by a command button when I run the below code

Sub Hide_Row()

Sheets("ProjectScheduleDetails").Visible = True
Sheets("ProjectScheduleDetails").Activate

Call Unhide_all

Range("AY8:AY5008").AdvancedFilter _
  Action:=xlFilterInPlace, _
  CriteriaRange:=Range("AZ1:AZ2"), _
  Unique:=False

Range("B2").Select

End Sub

 

[Hui]

Appreciated.

It is worth noting that most of the built in functions like Advanced Filter are highly optimised
I make use of them as much as possible