Modify code to hide rows based on the contents in a cell.

dcgrove · #1 06-18-2009, 06:09 PM

Hello, I am using the code below to hide all rows with the text "Solar" in the specified range. How can I modify this so that instead of looking for "Solar" it can be set to look in a cell that will be a data validation list. My goal is for the user to be able to show only the rows that have the word they selected from the list. I would like for it to change when ever the word is clicked on in the list if possible. I would also like for their to be an option to show all rows if the word "All" is selected.

If there is a faster way to accomplish what I am doing please let me know. As it is the code slows down my computer pretty badly.

Thanks!
Clayton

Code:

Sub HideRows()
 Dim R As Long
 Dim Rng As Range
    Set Rng = Worksheets("excel").Range("b7:b900")
   
     For R = Rng.Rows.Count To 1 Step -1
       If Not Rng.Cells(R, 1) = "Solar" Then Rng.Cells(R, 1).EntireRow.Hidden = True
     Next R
End Sub

**Leith Ross** · #2 06-18-2009, 07:29 PM

Hello Clayton,

When you need to check a large number of cells, it is faster to store the cell values in a Variant Array. Also, using the Like operator is faster way to search for and match text in a string.

Code:

Sub HideRows()

  Dim R As Long
  Dim Rng As Range
  Dim X As Variant
  
    Set Rng = Worksheets("excel").Range("B7:B900")
    X = Rng.Value
    
      For R = UBound(X, 1) To 1 Step -1
        If LCase(X(R, 1)) Like "*solar*" Then
           Rng.Rows(R).Hidden = True
        End If
      Next R

End sub

dcgrove · #3 06-18-2009, 09:07 PM

Thanks Leith! How would I make this search the contents of one particular cell instead of the word "Solar"? My goal is to have a data validation list that the user selects a word from and the macro will hide all rows without that word in them. You could use cell H3 as the cell the list is in if you need to.

Thanks!
Clayton

Welcome to the Excel Forum

Forum Statistics