Hide Rows that <> "string"

D

dellzy

Board Regular
Joined
Apr 24, 2013
Messages
146
Hi,
I found below formula in this forum that works really great on hiding all rows if it matches the string in any column of the row. However, I would like it to be reversed. Meaning, it will hide all rows that is NOT match to the string and also NOT blank (to avoid hiding rows of the entire worksheet). Also it needs to apply from row 5 then below. What I want to achieve is that I want to create a validation list consists of Dept name above row 5 and a table below it will only show the row records for the particular dept only. This is to avoid other users from other dept to meddle with records for other depts. Anyone can help,please? Thank you in advance.


Sub hiderows() For i = 1 To Range("A" & Rows.Count).End(xlUp).Row k = 0 For j = 1 To Range("A" & i).End(xlToRight).Column If InStr(1, Cells(i, j), "Circuit Total") > 0 Then k = k + 1 End If Next j If k > 0 Then Rows(i).Select Selection.EntireRow.Hidden = True End If Next i End Sub</pre>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
What String you want to search in rows or in what cell you want to input string which need to be searched?
 
Upvote 0
Hi, Delzy,

Assuming that your data is immediately below your data headers, and there is at least one blank row above the data headers, and a blank column to the left and the right of the data, the following examples should help you to come up with the code you want.

Code: 
'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="=NULL", Operator:=xlAnd      'EQUALS "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="<>NULL", Operator:=xlAnd     'DOES NOT EQUAL "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="=NULL*", Operator:=xlAnd     'BEGINS WITH "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="<>NULL*", Operator:=xlAnd    'DOES NOT BEGIN WITH "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="=*NULL", Operator:=xlAnd     'ENDS WITH "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="<>*NULL", Operator:=xlAnd    'DOES NOT END WITH "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="=*NULL*", Operator:=xlAnd    'CONTAINS "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="<>*NULL*", Operator:=xlAnd   'DOES NOT CONTAIN "NULL"
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="="                           'Blanks
    'ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:="<>"                          'NonBlanks

The value you use for AutoFilter Field relates to the position of the column within your database on which you want the filter to act. For the first field, use 1, the second, use 2 and so on. depending on where your database is within your worksheet, these values may not necessarily be the same as the worksheet column numbers. In a database occupying columns C:F, column E would use the value 3, as it's the third column in your database, NOT the third column in the worksheet.

Hope this helps

Pete
 
Upvote 0
Here is the code which will hide all rows with string inserted in cell A1 (this can be changed or InputBox can be added)

Code: 
Option Explicit

Sub HideRows()

Application.ScreenUpdating = False

Dim lrow As Long, lMySearch As Long, lEmptyRow
Dim strMySearch As String

strMySearch = Cells(1, 1)

lrow = 5
lEmptyRow = Application.WorksheetFunction.CountA(Rows(lrow))
lMySearch = Rows(lrow).Find(strMySearch, LookAt:=xlPart).Column
Do
    
    If Not lMySearch = Empty Then Rows(lrow).EntireRow.Hidden = True
    
    lrow = lrow + 1
    lMySearch = Empty
    lEmptyRow = Empty
    lEmptyRow = Application.WorksheetFunction.CountA(Rows(lrow))
    On Error Resume Next
    lMySearch = Rows(lrow).Find(strMySearch, LookAt:=xlPart).Column
    On Error GoTo 0
Loop Until lEmptyRow = Empty

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Sorry there is mistake in me above code. It hide rows with string you need. Here is correction.

Code: 
Option Explicit

Sub HideRows()

Application.ScreenUpdating = False

Dim lrow As Long, lMySearch As Long, lEmptyRow
Dim strMySearch As String

strMySearch = Cells(1, 1)

lrow = 5
lEmptyRow = Application.WorksheetFunction.CountA(Rows(lrow))
lMySearch = Rows(lrow).Find(strMySearch, LookAt:=xlPart).Column
Do
    
    If lMySearch = Empty Then Rows(lrow).EntireRow.Hidden = True
    
    lrow = lrow + 1
    lMySearch = Empty
    lEmptyRow = Empty
    lEmptyRow = Application.WorksheetFunction.CountA(Rows(lrow))
    On Error Resume Next
    lMySearch = Rows(lrow).Find(strMySearch, LookAt:=xlPart).Column
    On Error GoTo 0
Loop Until lEmptyRow = Empty

Application.ScreenUpdating = True

End Sub
 
Upvote 0
Hi,

Firstly, thank you very much for the prompt responses. I'm actually only considered as beginner in macro excel, so usually I can only able to apply a macro by copy+paste a complete macro command lines. Hence, I'm sorry, PeteRooneyHome , I don't seem to know which one I missed out or if any command lines I need to enter to make your suggestion works because all I saw when I pasted your answer in the macro module it all turned to be in green color, which I assume that these lines maybe just like a pseudo code for me? Appreciate if you can guide me in more details.

As for skorpionkz,I hv tried your macro, seems like it runs like the first macro that I pasted in this thread, and unfortunately, only works once only. When I try running it the next time, it will return error "Run tim error '91'.Object variable or woth block variable not set.

To further clarify, say in cell A4 is the validation list which has dept names to select. Table below it (starts from row 5) consists of rows of data as below:-

ColA ColB ColC
Dept StaffName Total Leave
IT Roger 10
Ops Catherine 5
Sales Mariah 3
IT Vicky 1
IT Mike 3.5
Sales Serene 0.5


So, say that user select Dept IT, so the output should be like below:-

ColA ColB ColC
Dept StaffName Total Leave
IT Roger 10
IT Vicky 1
IT Mike 3.5

With this, the user would only see the record of staff under IT Dept and the user can add additional record on the blank rows below it and no need to interfere other records. I believe if macro can do well to hide rows that match a string, this should also applicable in reversed way, just that I really don't know the commands.

Hopefully above clarifies. Thank you in advance.
 
Upvote 0
thanks for clarification. This should work now

Code: 
Option Explicit

Sub HideRows()

Application.ScreenUpdating = False

Dim lrow As Long, lMySearch As Long, lEmptyRow
Dim strMySearch As String

Cells.EntireRow.Hidden = False

strMySearch = Cells(1, 4)

lrow = 5
lEmptyRow = Application.WorksheetFunction.CountA(Rows(lrow))

Do

    On Error Resume Next
    lMySearch = Rows(lrow).Find(strMySearch, LookAt:=xlPart).Column
    On Error GoTo 0
    
    If lMySearch = Empty Then Rows(lrow).EntireRow.Hidden = True
    
    lrow = lrow + 1
    lMySearch = Empty
    lEmptyRow = Empty
    lEmptyRow = Application.WorksheetFunction.CountA(Rows(lrow))

Loop Until lEmptyRow = Empty

Application.ScreenUpdating = True

End Sub
 
Upvote 0
skorpionkz said:
thanks for clarification. This should work now

Code: 
Option Explicit

Sub HideRows()

Application.ScreenUpdating = False

Dim lrow As Long, lMySearch As Long, lEmptyRow
Dim strMySearch As String

Cells.EntireRow.Hidden = False

strMySearch = Cells(1, 4)

lrow = 5
lEmptyRow = Application.WorksheetFunction.CountA(Rows(lrow))

Do

    On Error Resume Next
    lMySearch = Rows(lrow).Find(strMySearch, LookAt:=xlPart).Column
    On Error GoTo 0
    
    If lMySearch = Empty Then Rows(lrow).EntireRow.Hidden = True
    
    lrow = lrow + 1
    lMySearch = Empty
    lEmptyRow = Empty
    lEmptyRow = Application.WorksheetFunction.CountA(Rows(lrow))

Loop Until lEmptyRow = Empty

Application.ScreenUpdating = True

End Sub
Click to expand...

Hi skorpionkz ,

I tried the above but nothing happened. It doesn't do any hiding but no error. Hv I pasted at the wrong place? Because I've tried pasted it on Workbook and Module 1 but both not working. I've tried saved and closed and reopen the workbook. But still no response upon running the macro. Please advise. Thank you in advance.
 
Upvote 0
you should paste it to the Module. I change cell reference to A4 as you advices. so it should if you have String in A4 that doesn't exist in the row. Do you have access to the Drop Box? I will make sample file and upload it.

1. Copy the code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
 
Upvote 0
skorpionkz said:
you should paste it to the Module. I change cell reference to A4 as you advices. so it should if you have String in A4 that doesn't exist in the row. Do you have access to the Drop Box? I will make sample file and upload it.

1. Copy the code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.
Click to expand...

Hi ,
Tqvm for the steps. I followed exactly the steps above which the same steps I usually use to paste and run macros but still not working. Not sure if I clarified my request wrongly. I have a dropbox account or you can email to me dell8687@gmail.com.

Thank you in advance.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
VBA to hide rows when a cell is a specific value and then hide the associated worksheet
Replies
3
Views
365
NoSparks
N
B
  • Solved
Another VBA question to hide/unhide rows based on cell value in another sheet
2
Replies
12
Views
431
kevin9999
K
B
  • Question
Matching Multiple Header Row Cell Values - Copy the data below to next available row.
Replies
2
Views
217
B-radK
B
Z
  • Solved
VBA Code to Hide columns in Specific Intervals
2
Replies
11
Views
387
jolivanes
jolivanes
B
  • Locked
  • Question
Multiply VBA Macro for hiding and unhiding rows
Replies
4
Views
378
RoryA
RoryA

Forum statistics

Threads
1,214,932
Messages
6,122,332
Members
449,077
Latest member
jmsotelo

Share this page

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