Macro to Hide Rows that contain specific Values...

[diqbal]

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]

Welcome to the board.

Try turning on the macro recorder and using autofilter. That should get you started.

 

[CraigM]

Something like this will do it for 1 sheet. I'll amend it for more than one sheet if you like.

Sub HideColumns()
For n = 1 To 65536 'All Rows
With Worksheets("Sheet1")
For c = 3 To 8 'Columns C to H
If .Cells(n, c) <> "-" Then Exit For
If c = 8 Then .Rows.Hidden = True
Next c
End With
Next n
End Sub

 

[diqbal]

yes, how would i get this to continue for 9 more sheets? thanks a lot!

 

[CraigM]

Try copying this. This is only for 3 sheets, but you can amend it for as many as you want (10 altogether?). Just add more Cases in the Select case, and change the names of the sheet to what to you want. Make sure you also change For S = 1 to 10. It may take a few seconds to run, but it'll be quicker than doing it by hand!

Sub HideRowsOnAllSheets()

Dim Sht As Worksheet
Dim S As Byte, Col As Byte
Dim Row As Long
Dim ShtName As String

Application.ScreenUpdating = False

For S = 1 To 3 '1 to number of sheets, eg 3 in this example
Select Case S
Case 1: ShtName = "Sheet1" 'Type the names of each sheet (in "")
Case 2: ShtName = "Sheet2"
Case 3: ShtName = "Sheet3" 'Add other cases, eg Case 4:
End Select 'Make sure the no of Case's is the same as max S

Set Sht = Worksheets(ShtName)
For Row = 1 To 65536 'All Rows
With Sht
For Col = 3 To 8 'Columns C to H
If .Cells(Row, Col) <> "-" Then Exit For
If Col = 8 Then .Rows(Row).Hidden = True
Next Col
End With
Next Row

Next S
Set Sht = Nothing

Application.ScreenUpdating = True
End Sub

 

[Greg Truby]

Diqbal,

Craig provided a very nice answer. The only Achilles' heel to it is that if someone goes and renames a sheet, your code will start Erroring out. In this particular instance this may or may not be a possible hazard. If it's not an issue, then wonderful. But I thought I'd go ahead and rework Craig's code a bit, showing how you could use worksheets' .CodeName property instead of the .Name property. Users that are sophisticated enough to know how to alter a codename would (probably) know better than to alter a codename. I also opted for using an array instead of a Select Case structure. I like this approach better, but it's purely a matter of personal preference. Select Case is a perfectly good way too. NOTE: not tested, only proofread a couple of times...

<font face=Courier New>
<SPAN style="color:#00007F">Sub</SPAN> HideRowsWithDashes()

    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>, bytCol <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN>, lngRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    
    <SPAN style="color:#00007F">Dim</SPAN> ws(9) <SPAN style="color:#00007F">As</SPAN> Worksheet  <SPAN style="color:#007F00">' Adjust # in () if # of sheets changes</SPAN>
    
    <SPAN style="color:#00007F">Set</SPAN> ws(1) = Sheet1      <SPAN style="color:#007F00">' On the right of the equals sign</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> ws(2) = Sheet2      <SPAN style="color:#007F00">' put the CODENAMEs of the worksheets</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> ws(3) = Sheet3      <SPAN style="color:#007F00">' you want to process.</SPAN>
    <SPAN style="color:#00007F">Set</SPAN> ws(4) = Sheet4
    <SPAN style="color:#00007F">Set</SPAN> ws(5) = Sheet5
    <SPAN style="color:#00007F">Set</SPAN> ws(6) = Sheet6
    <SPAN style="color:#00007F">Set</SPAN> ws(7) = Sheet7
    <SPAN style="color:#00007F">Set</SPAN> ws(8) = Sheet8
    <SPAN style="color:#00007F">Set</SPAN> ws(9) = Sheet9      <SPAN style="color:#007F00">' Add / Delete as needed (don't forget</SPAN>
                            <SPAN style="color:#007F00">' to adjust the DIM statement above).</SPAN>
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN>
    
    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(ws)
        <SPAN style="color:#00007F">For</SPAN> lngRow = 1 <SPAN style="color:#00007F">To</SPAN> 65536 <SPAN style="color:#007F00">'All Rows</SPAN>
            <SPAN style="color:#00007F">With</SPAN> ws(i)
                <SPAN style="color:#00007F">For</SPAN> bytCol = 3 <SPAN style="color:#00007F">To</SPAN> 8 <SPAN style="color:#007F00">'Columns C to H</SPAN>
                    <SPAN style="color:#00007F">If</SPAN> .Cells(lngRow, bytCol) <> "-" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>
                    <SPAN style="color:#00007F">If</SPAN> bytCol = 8 <SPAN style="color:#00007F">Then</SPAN> .Rows(lngRow).Hidden = <SPAN style="color:#00007F">True</SPAN>
                <SPAN style="color:#00007F">Next</SPAN> bytCol
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> lngRow
        <SPAN style="color:#00007F">Set</SPAN> ws(i) = <SPAN style="color:#00007F">Nothing</SPAN>
    <SPAN style="color:#00007F">Next</SPAN> i
    
    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN>

<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


Regards,

 

[CraigM]

Good point Greg.

 

[diqbal]

Greg, I implemented your method and added 8 more sheets along with their codenames. It is giving me a type mismatch error on the "If .Cells(lngRow, bytCol) <> "-" Then Exit For" line. My code is as follows:

Sub HideRowsWithDashes()

Dim i As Integer, bytCol As Byte, lngRow As Long

Dim ws(11) As Worksheet

Set ws(1) = Sheet9
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)
For lngRow = 1 To 65536
With ws(i)
For bytCol = 3 To 8
If .Cells(lngRow, bytCol) <> "-" Then Exit For
If bytCol = 8 Then .Rows(lngRow).Hidden = True
Next bytCol
End With
Next lngRow
Set ws(i) = Nothing
Next i

Application.ScreenUpdating = True

End Sub



Thanks a lot. If you need me to send u the excel file as an example of what im trying to do. I will.

 

[Greg Truby]

I inserted some sheets with test data and then pasted that code (the code you posted) straight into a standard module and it works just fine for me. I even put text, boolean, date, blanks, errors and formulas into the test data block and no problems. What version of XL are you running?

You might adding .Formula to the checking IF, which also ran for me:

If .Cells(lngRow, bytCol).Formula <> "-" Then Exit For

And this also worked for me:

<font face=Courier New><SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(ws)
    <SPAN style="color:#00007F">For</SPAN> lngRow = 1 <SPAN style="color:#00007F">To</SPAN> 65536
        <SPAN style="color:#00007F">For</SPAN> bytCol = 3 <SPAN style="color:#00007F">To</SPAN> 8
            <SPAN style="color:#00007F">If</SPAN> ws(i).Cells(lngRow, bytCol).Formula <> "-" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">For</SPAN>
            <SPAN style="color:#00007F">If</SPAN> bytCol = 8 <SPAN style="color:#00007F">Then</SPAN> ws(i).Rows(lngRow).Hidden = <SPAN style="color:#00007F">True</SPAN>
        <SPAN style="color:#00007F">Next</SPAN> bytCol
    <SPAN style="color:#00007F">Next</SPAN> lngRow
    <SPAN style="color:#00007F">Set</SPAN> ws(i) = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">Next</SPAN> i
</FONT>

 

[NateO]

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> Contingent_Hide()
<SPAN style="color:darkblue">Dim</SPAN> ws <SPAN style="color:darkblue">As</SPAN> Worksheet, c <SPAN style="color:darkblue">As</SPAN> Range
Application.ScreenUpdating = <SPAN style="color:darkblue">False</SPAN>
<SPAN style="color:darkblue">For</SPAN> <SPAN style="color:darkblue">Each</SPAN> ws <SPAN style="color:darkblue">In</SPAN> Worksheets(Array(1, 2))
    <SPAN style="color:darkblue">On</SPAN> <SPAN style="color:darkblue">Error</SPAN> <SPAN style="color:darkblue">Resume</SPAN> <SPAN style="color:darkblue">Next</SPAN>
    Do: <SPAN style="color:darkblue">Set</SPAN> c = ws.[c:h].SpecialCells(xlVisible).Find( _
            "-", lookat:=xlPart, LookIn:=xlValues)
        <SPAN style="color:darkblue">If</SPAN> c <SPAN style="color:darkblue">Is</SPAN> <SPAN style="color:darkblue">Nothing</SPAN> <SPAN style="color:darkblue">Then</SPAN> <SPAN style="color:darkblue">Exit</SPAN> <SPAN style="color:darkblue">Do</SPAN>
        c.EntireRow.Hidden = True: <SPAN style="color:darkblue">Set</SPAN> c = <SPAN style="color:darkblue">Nothing</SPAN>
    <SPAN style="color:darkblue">Loop</SPAN>
<SPAN style="color:darkblue">Next</SPAN>
Application.ScreenUpdating = <SPAN style="color:darkblue">True</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

Edit: I had a an extra, bogus variable.