Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Hide Macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Hide Macro
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Hide Macro
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 "Awoll" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a Hide Macro
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a function to auto hide rows | Excel Worksheet Functions | |||
Hide a Macro | Excel Discussion (Misc queries) | |||
macro to hide row with 0.00 | Excel Worksheet Functions | |||
hide original cells after creating chart | Charts and Charting in Excel | |||
Hide/Unhide Macro bug | Excel Programming |