Creating a Hide Macro

  • Thread starter Thread starter Awoll
  • Start date Start date
A

Awoll

Hello, I was wondering if there is any way to create a button macro that
will search for specific text in a column, and hide all other rows not
displaying that text, but leave rows with no data alone. For instance. If
the column i'm using is the "D" column. and the text i'm looking for is
"case". So i want to leave the rows visible that include case and rows that
have no data in them. And a macro that will return the spread sheet back to
normal (unhide).

Thanks for any info,

Aaron
 
Yes is perfectly possible you could use something like this
Sub HideRows
Dim Searchtxt As String
Dim ws As Worksheet
Searchtxt = "case"
Set ws = ThisWorkbook.Worksheets("Name of Worksheet")
For row = 2 to 100
If ws.Cells(row, 4) <> Searchtxt And ws.Cells(row, 4) <> "" Then 'Column D is numbered 4
ws.Rows(Row).EntireRow.Hidden = True
End If
Next row
End Sub

and it undo it just do:

Sub UnHideRows
Dim Searchtxt As String
Dim ws As Worksheet
Searchtxt = "case"
Set ws = ThisWorkbook.Worksheet("Name of Worksheet")
For row = 2 to 100
ws.Rows(Row).EntireRow.Hidden = false
Next row
End Sub

problem with this is that it looks for an exact match so if its a capital Case it will be false through this is easy to get around using something like the Upper function
 
Awoll,

This pair should get you what you want:

Sub hide_case()
Dim c As Range
For Each c In Worksheets("Sheet1").Range(Range("D1"),
Range("D65536").End(xlUp))
If c <> "case" And c <> "" Then
c.EntireRow.Hidden = True
End If
Next c
End Sub

Sub unhide()
Dim c As Range
For Each c In Worksheets("Sheet1").Range(Range("D1"),
Range("D65536").End(xlUp))
c.EntireRow.Hidden = False
Next c
End Sub

Unless you meant that the entire row had to be blank, not just the cell in
column D. That would require a little more code.

hth,

Doug
 
That worked Nichevo thanks for the input,

Now i've been working on my other hide button for hours now and still can't
get it to work. now I'm trying to hide any thing that does not equal 0 in
the cells. How would I go about doing that?

Thanks for any more help.


Nichevo said:
Yes is perfectly possible you could use something like this
Sub HideRows
Dim Searchtxt As String
Dim ws As Worksheet
Searchtxt = "case"
Set ws = ThisWorkbook.Worksheets("Name of Worksheet")
For row = 2 to 100
If ws.Cells(row, 4) <> Searchtxt And ws.Cells(row, 4) <> "" Then 'Column D is numbered 4
ws.Rows(Row).EntireRow.Hidden = True
End If
Next row
End Sub

and it undo it just do:

Sub UnHideRows
Dim Searchtxt As String
Dim ws As Worksheet
Searchtxt = "case"
Set ws = ThisWorkbook.Worksheet("Name of Worksheet")
For row = 2 to 100
ws.Rows(Row).EntireRow.Hidden = false
Next row
End Sub

problem with this is that it looks for an exact match so if its a capital
Click to expand...
Case it will be false through this is easy to get around using something
like the Upper function
 
Back
Top