highlighted rows

[paquirl]

after highlighting certain rows on a large spreadsheet, is it possible to make it view only the highlighted rows?

 

[stnkynts]

Yes

 

[paquirl]

would you be so kind as to tell me how to do it?

 

[stnkynts]

Well there may be a way to do it through the Filter option. Writing a macro to do it would be simple but unfortunatley I have no idea how to do it for you since you gave no data as a reference.

Example: What is the highlighted color? Do you want to do for all highlighted colors? What range do you want this applied to? Do you want to view the highlighted ones on a new sheet or within the same sheet? Do you want to delete the non-highlighted values or just hide them?

I can go on and on with questions. Thats why I answered your vague question with an equally vague.....Yes.

 

[John Davis]

after highlighting certain rows on a large spreadsheet, is it possible to make it view only the highlighted rows?

Run this macro and only the highlighted rows will show:

Sub paquirl()
Dim cl As Range
Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

    For Each cl In Range("A1:A" & lr)
    
        If cl.Interior.ColorIndex = xlNone Then cl.EntireRow.Hidden = True
        
    Next cl
         
End Sub

This one will redisplay all rows

Sub paquirl2()

Dim lr As Long

lr = Cells(Rows.Count, 1).End(xlUp).Row

Range("A1:A" & lr).EntireRow.Hidden = False
    

End Sub

 

[paquirl]

i have it as the following code. my screen flashes for a long time and then it finally seems to work! does my code need to be cleaned up, or is it supposed to flash like that?

Sub paquirl()
'
' paquirl Macro
' Macro recorded 3/11/2011 by Andrew Quirl
'
' Keyboard Shortcut: Ctrl+Shift+Q
Dim cl As Range
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
For Each cl In Range("A1:A" & lr)

If cl.Interior.ColorIndex = xlNone Then cl.EntireRow.Hidden = True

Next cl

End Sub

 

[John Davis]

i have it as the following code. my screen flashes for a long time and then it finally seems to work! does my code need to be cleaned up, or is it supposed to flash like that?

Sub paquirl()
'
' paquirl Macro
' Macro recorded 3/11/2011 by Andrew Quirl
'
' Keyboard Shortcut: Ctrl+Shift+Q
Dim cl As Range
Dim lr As Long
lr = Cells(Rows.Count, 1).End(xlUp).Row
For Each cl In Range("A1:A" & lr)

If cl.Interior.ColorIndex = xlNone Then cl.EntireRow.Hidden = True

Next cl

End Sub

There should be no flashing but try this change.

Sub paquirl()
'
' paquirl Macro
' Macro recorded 3/11/2011 by Andrew Quirl
'
' Keyboard Shortcut: Ctrl+Shift+Q
Dim cl As Range
Dim lr As Long
Application.Screenupdating = false
lr = Cells(Rows.Count, 1).End(xlUp).Row
    For Each cl In Range("A1:A" & lr)
    
        If cl.Interior.ColorIndex = xlNone Then cl.EntireRow.Hidden = True
        
    Next cl
Application.Screenupdating = true  
End Sub

 

[paquirl]

now it's not working at all.

 

[John Davis]

now it's not working at all.

It's working. The screenupdating means you can't see what's happening until it is finished. How large is your spreadsheet? You must have a very large spreadsheet, therefore it will take some time. Try it again and be patient.

 

[paquirl]

i do have a large spreadsheet, but i've tried it again waiting a long time and still nothing. the previous code that worked flashed the screen a lot and took several seconds. i've tried this new one a few times waiting up to three minutes.