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.

NateO · Jan 9, 2004

Yep, that's why the test was baked in there, I realized after posting that I had slightly misplaced it... Nice catch/fix!

One simply wants to use the first visible dashed cell in C as your marker.

Curious, about the merits/insistence of using Code Names, like it's best-in-class practice. What if I change the code name of a sheet? It's a fine way of doing it, but index numbers and names have their merits to be certain. I rarely use sheet code names, so I suppose that's why I simply do not relate here... So be it.

Also, from a defensive coding standpoint, one should set the lower boundary of an array, did you test the lower boundary? In any case, I might use a [underrated] collection class on a slow-stack or quick-stack an array like the following:

Sub UseColl()
Dim myShts As Collection, i As Long
Set myShts = New Collection
myShts.Add Sheet1: myShts.Add Sheet2
For i = 1 To myShts.Count
MsgBox Sheets(i).Name
Next
Set myShts = Nothing
End Sub

Sub UseArr()
Dim i As Long, myArr As Variant
myArr = Array(Sheet1, Sheet2)
For i = LBound(myArr) To UBound(myArr)
MsgBox myArr(i).Name
Next
Rem Debug.Print LBound(myArr)
End Sub

Fun eh, the variant coerces into an array of initialized objects, like the girl from the McDonalds commercials, I'm lovin' it.

Note, the array is 0-based.

Greg Truby · Jan 9, 2004

Thanks for the additional ideas. I like 'em both.

As for not using LBound(): if I'm loading the array using hardcoded subscripts, I just like starting at one instead of zero. So I normally just code my loops to start at 1 as well. If I didn't hardcode the subscripts, then yeah, gotta use LBound() [And yes, I learned that the hard way.]

As for my predilection for using codenames: I figure if my code blows up 'cause a user renamed or moved a sheet, then I reckon I (half-) deserve to get that annoying call "Your macro doesn't work! Can you come over?" But if he's gone and altered a codename then (a) he shoulda known better

and (b) he's probably smart enough to debug it himself.

Again, thanks for taking the time to post the collection and array code. Now where did our OP get off to???

NateO · Jan 9, 2004

Greg Truby said:
Thanks for the additional ideas. I like 'em both.

You're welcome. The oft-overlooked custom collection is a beaut as they:

1) Allow ynamic size dimensions (and are always 1-based I believe)
2) Allow Variant input
3) Are fast

GT said:
As for not using LBound(): if I'm loading the array using hardcoded subscripts, I just like starting at one instead of zero. So I normally just code my loops to start at 1 as well. If I didn't hardcode the subscripts, then yeah, gotta use LBound() [And yes, I learned that the hard way.]

True, in that the way you are using the array has no real risk. But not true in re: your array starts at 1 (while your loop does), while trivial in this instance, your array is not sized properly because of the lack of boundary dimensioning, which is pretty simple/not really a burden.

Sub sdhfjks()
Dim ws(9) As Worksheet ' Adjust # in () if # of sheets changes

 Set ws(1) = Sheet1 ' On the right of the equals sign
 Set ws(2) = Sheet2 ' put the CODENAMEs of the worksheets
 Set ws(3) = Sheet3 ' you want to process.
 ' Add / Delete as needed (don't forget
 ' to adjust the DIM statement above).
Debug.Print LBound(ws)
Erase ws
End Sub

The first element, 0, being gratuitous and the common culprit in naughty array programming, which is why I mention it.

GT said:
As for my predilection for using codenames: I figure if my code blows up 'cause a user renamed or moved a sheet, then I reckon I (half-) deserve to get that annoying call "Your macro doesn't work! Can you come over?" But if he's gone and altered a codename then (a) he shoulda known better and (b) he's probably smart enough to debug it himself.

Sure. How about protection?

GT said:
Again, thanks for taking the time to post the collection and array code. Now where did our OP get off to???

Based on:

OP said:
Please help as I am an extreme newbie to Excel Macros. Thanks.
{snip}

They probably got a little more than they were bargaining for at this point.

diqbal · Jan 9, 2004

Ok, call me a retard. I have employed the various implementations mentioned here and none of them have worked for me. They have worked on simpler worksheets that i made just to test them, but on my particular workbook it's not working. I uploaded the workbook to my website and if you guys could take a look at it and see whats up i'd appreciate it and at this point I know you guys love challenges and YES this is MUCH more than i expected Nate. But thank you. What I need done is for the sheets: Subj. Co. through Guidline Co. 10 with everything in between to hide rows when B:H ALL have dashes in them. In total that's 11 sheets. And yes the requirement has changed from C to B:H.

URL:
http://students.depaul.edu/~diqbal/TESTMOD.xls

Greg Truby · Jan 9, 2004

We all missed an obvious pitfall! They ain't dashes. They're zeros formatted as dashes. (You could also set rngBottom based on column C, which isn't hidden.)



Sub HideRowsWithDashes2()

 Dim i As Long, r As Long
 Dim wsArray As Variant, rngBottom As Range, rngCell As Range

 wsArray = Array(Sheet9, Sheet10, Sheet11, Sheet12, Sheet13, _
 Sheet14, Sheet15, Sheet16, Sheet17, Sheet18, Sheet8)

 Application.ScreenUpdating = False
 For i = LBound(wsArray) To UBound(wsArray)
 Set rngBottom = wsArray(i).Range("B65536").End(xlUp)
' Debug.Print rngBottom.Address
 For r = 1 To rngBottom.Row
 Set rngCell = wsArray(i).Cells(r, 2)
 If Application.WorksheetFunction.Sum(rngCell.Resize(, 7)) = 0 _
 And InStr(1, rngCell.NumberFormat, "-") <> 0 Then rngCell.EntireRow.Hidden = True
 Next r
 Next i
 Application.ScreenUpdating = True

End Sub

Greg Truby · Jan 9, 2004

Nate - this seems a bit sluggish on my machine. If you've got a better way to skin this cat, I'd love to see it.

NateO · Jan 9, 2004

Hello Again,

Greg Truby said:
Nate - this seems a bit sluggish on my machine. If you've got a better way to skin this cat, I'd love to see it.

The procedure's already been crafted per your last two code submissions eh, you've shown yourself the way.

Here's what the compilation of the thread's efforts-to-date looks like:

Sub fubar3()
Dim ws As Worksheet, cl As Range, OrigCl As String, z As Boolean
Application.ScreenUpdating = False

For Each ws In Worksheets([transpose(row(8:19))])
 Set cl = ws.[c:c].Find("0", lookat:=xlWhole, LookIn:=xlFormulas)
 If Not cl Is Nothing Then
 Let OrigCl = cl.Address
 If Not CBool(WorksheetFunction.Sum(cl(, 0).Resize(, 7))) Then
 z = Not z: cl.EntireRow.Hidden = z
 End If
 Do: Set cl = ws.[c:c].FindNext(cl)
 If cl Is Nothing Then Exit Do
 If cl.Address = OrigCl Then Exit Do
 If Not CBool(WorksheetFunction.Sum(cl(, 0).Resize(, 7))) Then
 cl.EntireRow.Hidden = True
 ElseIf z Then
 OrigCl = cl.Address: z = Not z
 End If
 Loop
 Set cl = Nothing: Let OrigCl = Empty
 End If
Next

Application.ScreenUpdating = True
End Sub

This processes Subject Co.:Sheet9. I didn't time it, but it felt like less than minute... It is quite a bit of looping, despite the fact that the Find Method drastically reduces the loop. Still, probably faster than doing it by hand eh.

Hope this helps.

NateO · Jan 9, 2004

Holy ****! I didn't notice the percentage calcs below, I ran a second procedure:

Sub fubar4()
Dim ws As Worksheet, cl As Range, OrigCl As String, z As Boolean
Application.ScreenUpdating = False

For Each ws In Worksheets([transpose(row(8:19))])
 Set cl = ws.[c:c].Find("0.0%", lookat:=xlWhole, LookIn:=xlValues)
 If Not cl Is Nothing Then
 Let OrigCl = cl.Address
 If Not CBool(WorksheetFunction.Sum(cl(, 0).Resize(, 7))) Then
 z = Not z: cl.EntireRow.Hidden = z
 End If
 Do: Set cl = ws.[c:c].FindNext(cl)
 If cl Is Nothing Then Exit Do
 If cl.Address = OrigCl Then Exit Do
 If Not CBool(WorksheetFunction.Sum(cl(, 0).Resize(, 7))) Then
 cl.EntireRow.Hidden = True
 ElseIf z Then
 OrigCl = cl.Address: z = Not z
 End If
 Loop
 Set cl = Nothing: Let OrigCl = Empty
 End If
Next

Application.ScreenUpdating = True
End Sub

Greg Truby · Jan 9, 2004

I was wondering about that. I actually was scanning the number format to NOT hide them.

And [transpose(row(8:19))] ??? I'm completed stumped as to how or why this works. If'n you've the patience to explain, I'm curious.

NateO · Jan 9, 2004

Well, not running numero 4 gets you there. But, I figure what's good for the goose is good for the gander eh, so hide 'em! You could combine the two and end up looping through the sheets once, but this isn't going to drastically alter performance at this point (I think...), might as well call #4 at the end of #3.

Why z = not z you ask?

Because we've read our Ken Getz, and we took note of -> implemented sound advice presented in test 5.

The quick-stack array trickery has been commented on here:
http://www.mrexcel.com/board2/viewtopic.php?t=70907&start=3

Enjoy!

Macro to Hide Rows that contain specific Values...

diqbal

New Member

NateO

Legend

Excel Facts

Greg Truby

MrExcel MVP

NateO

Legend

diqbal

New Member

Greg Truby

MrExcel MVP

Greg Truby

MrExcel MVP

NateO

Legend

NateO

Legend

Greg Truby

MrExcel MVP

NateO

Legend

Similar threads

Forum statistics

Share this page

Macro to Hide Rows that contain specific Values...

New Member

Legend

Excel Facts

MrExcel MVP

Legend

New Member

MrExcel MVP

MrExcel MVP

Legend

Legend

MrExcel MVP

Legend

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