using Range(myString)

[JohanD]

Hello,

I've been trying to make a fast way to hide Rows in a sheet. solutions I found were slowing down the app tremendous when applying a EntireRow.Hidden after each check as seen below.

While Len(Range("A" & CStr(r)).Value) <> 0
                   
        If Range("N" & CStr(r)).Value <> "d" Then
            Rows(CStr(r) & ":" & CStr(r)).Select
            Selection.EntireRow.Hidden = True
        End If
Wend

but in a sheet for over 500 records. I can make Cofee twice after I applied my filter.
and each time I get an error when I use Range(myRows).Select

Dim myRows As String
While Len(Range("A" & CStr(r)).Value) <> 0
                   
        If Range("N" & CStr(r)).Value <> "d" Then
            myRows = myRows & r & ":" & r & ","
        End If
                
        r = r + 1
        'remove last comma from the string when we're at the end of the sheet.
        If Len(Range("A" & CStr(r)).Value) = 0 Then
            myRows = Left(myRows, Len(myRows) - 1)
        End If
    Wend
    
#  Range(myRows).Select ##### Error
    Selection.EntireRow.Hidden = True

note that I don't want to use the Sort method for this one as I'm already Sorting the records based on Dates in other parts of the sheet.

 

[GTO]

.Select is slow and can be ditched. You probably want to turn off ScreenUpdating as well, as all the repaints kill time.

 

[jim may]

How is "r" getting its value?

 

[Peter_SSs]

Would this achieve the goal?

Columns("N").AutoFilter Field:=1, Criteria1:="=d"

 

[JohanD]

.Select is slow and can be ditched. You probably want to turn off ScreenUpdating as well, as all the repaints kill time.

OMG That's simple

i've added

Application.ScreenUpdating = False

on top of the Sub


and the .Select changed to

Rows(CStr(r) & ":" & CStr(r)).EntireRow.Hidden = True

 

[JohanD]

Would this achieve the goal?

Columns("N").AutoFilter Field:=1, Criteria1:="=d"

.Autofilter lets me only use 1 field to sort on Right?

my example wasn't realy complete to show what I'm doing but I'm hiding Rows where more conditions happen in several columns.

 

[JohanD]

How is "r" getting its value?

"r" is an integer used as increment to show the current Row i'm in

 

[JohanD]

Thanks everyone!!! have a good Easter :D

 

[Peter_SSs]

.Autofilter lets me only use 1 field to sort on Right?

my example wasn't realy complete to show what I'm doing but I'm hiding Rows where more conditions happen in several columns.

I thought we were hiding rows/filtering, not sorting.

Autofilter can be applied to one or many columns. Whether it would suit your purpose depends on just what that purpose is. If AutoFilter was applicable, it would most probably be much simpler/quicker than cycling through every row ofr your relevant columns. More details would be required to assess if AutoFilter could be used effectively for you.