Hide rows based on value

soggy40

New Member
Joined
Jan 29, 2010
Messages
49
Hi experts,

I'm looking for help to a problem I have. I am tring to create some code to help me keep visible or hide rows based on a value. I have achieved the following but am struggling.

Sub HideRowscategory_1()
Dim lstRng As Range
Dim c As Range

Set lstRng = Range("CC11", Range("CC310").End(xlUp))

For Each c In lstRng

If c.Value = Range("CC1").Value Then
c.EntireRow.Hidden = False

End If
Next c
End Sub


What I'm looking to do is if c.value = Range("CC1").value then that row plus the row below it remain visible. Otherwise I want the row (plus the one below it) to become hidden.

Any advice or help much appreciated.

:biggrin:

Thanks to anyone who looks or tries to help.

Soggy40


 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Try this ....untested
Code:
Sub HideRowscategory_1()
Dim lstRng As Range
Dim c As Range

Set lstRng = Range("CC11:CC310")
        For Each c In lstRng
            If c.Value = Range("CC1").Value Then
                c.EntireRow.Hidden = False
            End If
Next c
End Sub
 
Upvote 0
Try:

Code:
Sub HideRowscategory_1()
    Dim lstRng As Range
    Dim r As Long
    Set lstRng = Range("CC11", Range("CC310").End(xlUp))
    With lstRng
    For r = 1 To .Rows.Count Step 2
        .Rows(r).Resize(2).EntireRow.Hidden = .Cells(r, 1).Value <> Range("CC1").Value
    Next r
    End With
End Sub
 
Upvote 0
Hi Michael M,

that works perfectly. :biggrin: Thank you so much.

Having tried it I now realise that I need something that can unhide evrything. Any ideas for that??

Thanks again

Soggy40
 
Upvote 0
While my code works OK, I think you'll find Andrews is faster and more efficient.
For the Unhide ALL try
Code:
Sub UnhideRow()
Dim ws As Worksheet
    ws.Cells.EntireRow.Hidden = False
End Sub
 
Upvote 0
Thanks Michael for all your assistance

:biggrin:

Soggy40

everything is working a treat now........my life has just become easier.
 
Upvote 0
Hi Andrew,

that works perfectly. :biggrin: Thank you so much.

Having tried it I now realise that I need something that can unhide evrything. Any ideas for that??

Thanks again

Soggy40
 
Upvote 0

Forum statistics

Threads
1,223,244
Messages
6,170,976
Members
452,372
Latest member
Natalie18

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top