Macro to Hide Rows that contain specific Values...

diqbal · Jan 7, 2004

I need to create a macro that looks at every row in a worksheet and checks to see if columns C-H all have a value of "-" and if this is true it hides the row. It then keeps running for 9 more worksheets that are identical to the first one for all intents and purposes.

The logical reason for this is that I am creating a sheet that pulls in values from an external source and I want to hide all the rows that have no values so as to not have a lot of rows with just dashes in them.

Please help as I am an extreme newbie to Excel Macros. Thanks.

Greg Truby · Jan 8, 2004

Howdy Nate. I like the use of .Find

But this too will error out if a user renames a sheet (which was why I stuck my nose back in on this one after Craig had offered a viable response). Question: I've not had any luck figuring out how to set up an array of worksheets referencing .CodeNames inside Array() like you can with .Names. Got any slick way of doing that other than one-by-one like I did?

NateO · Jan 8, 2004

Hello Greg,

See my edit, it uses index numbers eh. Of course this bombs out if someone moves the sheets! There's always a few ways of bolloxing up a nice application.

Otherwise, my advice would be to loop through the entire collection (using the collection, not integers) and have a contingency/contingencies, e.g., if ws.codename <> "SheetToSkip".

It's a simple, efficient procedure in terms of looping.

Greg Truby · Jan 8, 2004

Dang! And I really liked Nate's use of .Find, but this will hide any rows that have a "-", not just rows where all are "-".

NateO · Jan 8, 2004

Try changing xlPart to xlWhole eh.

Have a go with the VBE Help File re: Find Method.

Greg Truby · Jan 8, 2004

Nah, that 'twern't me point. Diqbal needs 'em hidden if all the columns in [C:H] have a dash, not if any of 'em have a dash. And swapping LookAt to xlWhole actually makes it quit workin' unless ya switch LookIn to xlFormulas (which I don't get, but there is it).

sarlo00 · Jan 8, 2004

Nate,
I also was able to use your code. Very nice! Is there any way to alter the code to unhide rows if they no longer have "-" when re-run?
Brian

NateO · Jan 8, 2004

Right, I missed that part of the quandary, how about:

Sub fubar()
Dim ws As Worksheet, cl As Range, OrigCl As String, z As Boolean
Application.ScreenUpdating = False
For Each ws In Worksheets(Array(1, 2))
 Set cl = ws.[c:c].Find("-", lookat:=xlWhole, LookIn:=xlValues)
 If Not cl Is Nothing Then
 Let OrigCl = cl.Address
 If WorksheetFunction.CountIf(cl.Resize(, 6), "-") = 6 Then
 cl.EntireRow.Hidden = True
 z = True
 End If
 Do: Set cl = ws.[c:c].FindNext(cl)
 If cl.Address = OrigCl Then Exit Do
 If z Then
 OrigCl = cl.Address
 z = Not z
 End If
 If WorksheetFunction.CountIf(cl.Resize(, 6), "-") = 6 _
 Then cl.EntireRow.Hidden = True
 Loop
 Set cl = Nothing: Let OrigCl = Empty
 End If
Next
Application.ScreenUpdating = True
End Sub


I didn't follow your comment re: xlWhole, the above works fine for me...

NateO · Jan 8, 2004

sarlo00 said:
Nate,
I also was able to use your code. Very nice! Is there any way to alter the code to unhide rows if they no longer have "-" when re-run?
Brian

Hello, thanks, just unhide the cells and run it again eh.

Code:

Sub dsfdfsd()
Dim ws As Worksheet
For Each ws In Sheets(Array(1, 2))
    ws.[a1:a65536].EntireRow.Hidden = False
Next
End Sub

sarlo00 · Jan 8, 2004

Right

Greg Truby · Jan 9, 2004

In testing Nate's new code (but still very nice Nate, not that you need me to tell you that) - I was able to get it to hang in an infinite loop if I had a row down the page that had just a dash in column c but not all columns. So here's a rework (with me being stubborn about codenames and not using ordinal positions either :wink: ).



Sub fubar2()

Dim i As Integer, cl As Range, OrigCl As String, z As Boolean
Dim ws(11) As Worksheet
Set ws(1) = Sheet3
Set ws(2) = Sheet10
Set ws(3) = Sheet11
Set ws(4) = Sheet12
Set ws(5) = Sheet13
Set ws(6) = Sheet14
Set ws(7) = Sheet15
Set ws(8) = Sheet16
Set ws(9) = Sheet17
Set ws(10) = Sheet18
Set ws(11) = Sheet8

Application.ScreenUpdating = False
For i = 1 To UBound(ws)
 Set cl = ws(i).[c:c].Find("-", lookat:=xlPart, LookIn:=xlValues)
 If Not cl Is Nothing Then
 Let OrigCl = cl.Address
 If WorksheetFunction.CountIf(cl.Resize(, 6), "-") = 6 Then
 cl.EntireRow.Hidden = True
 z = True
 End If
 Do: Set cl = ws(i).[c:c].FindNext(cl)
 If cl Is Nothing Then Exit Do
 If cl.Address = OrigCl Then Exit Do
 If WorksheetFunction.CountIf(cl.Resize(, 6), "-") = 6 Then
 cl.EntireRow.Hidden = True
 ElseIf z Then
 OrigCl = cl.Address
 z = False
 End If
 Loop
 Set cl = Nothing: Let OrigCl = Empty
 End If
 Set ws(i) = Nothing
Next
Application.ScreenUpdating = True

End Sub

Macro to Hide Rows that contain specific Values...

diqbal

New Member

Greg Truby

MrExcel MVP

Excel Facts

NateO

Legend

Greg Truby

MrExcel MVP

NateO

Legend

Greg Truby

MrExcel MVP

sarlo00

Board Regular

NateO

Legend

NateO

Legend

sarlo00

Board Regular

Greg Truby

MrExcel MVP

Similar threads

Forum statistics

Share this page

Macro to Hide Rows that contain specific Values...

New Member

MrExcel MVP

Excel Facts

Legend

MrExcel MVP

Legend

MrExcel MVP

Board Regular

Legend

Legend

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock