highlighted rows

paquirl

Board Regular
Joined
Oct 12, 2010
Messages
226
Office Version
  1. 2016
Platform
  1. Windows
after highlighting certain rows on a large spreadsheet, is it possible to make it view only the highlighted rows?
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
would you be so kind as to tell me how to do it?
 
Upvote 0
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.
 
Upvote 0
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:

Code:
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

Code:
Sub paquirl2()

Dim lr As Long

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

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

End Sub
 
Upvote 0
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
 
Upvote 0
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.

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,222,069
Messages
6,163,733
Members
451,854
Latest member
Tiffany Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top