?
Solved

Excel Row Hide on Condition

Posted on 2011-10-27
9
Medium Priority
?
251 Views
Last Modified: 2012-05-12
Hi Experts,

I am trying format a rather big page by hiding some unnecessary rows.  I want to find a value like "-"  across the whole column (say entire column "B") and hide any row that has "-" and the next 4 rows (Also the "-" in the cell is produced by a If statement).  Any help is greatly appreciated.

Thanks,

Cody
0
Comment
Question by:codysheilds
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 37039396
Hi,

Try

Kris
Sub kTest()
    
    Dim i   As Long, a(), Addr As String, n As Long, k, r As Range
    
    Set r = Intersect(ActiveSheet.UsedRange, Columns(2))
    
    k = r
    
    For i = UBound(k, 1) To 1 Step -1
        If InStr(1, k(i, 1), "-") Then
            Addr = Addr & "," & "A" & i & ":A" & i + 4
            If Len(Addr) > 240 Then
                n = n + 1
                ReDim Preserve a(1 To n)
                a(n) = Mid$(Addr, 2)
                Addr = vbNullString
            End If
        End If
    Next
    If Len(Addr) > 1 Then
        n = n + 1
        ReDim Preserve a(1 To n)
        a(n) = Mid$(Addr, 2)
        Addr = vbNullString
    End If
    
    If n Then
        Application.ScreenUpdating = False
        With r
            For i = 1 To n
                .Range(CStr(a(i))).EntireRow.Delete
            Next
        End With
    End If
    Application.ScreenUpdating = True
    
End Sub

Open in new window

0
 
LVL 11

Expert Comment

by:Swapnil Nirmal
ID: 37039436
Try this:


sub Hide_Row()



Set rFoundCell = Range("A1")


        For lCount = 1 To WorksheetFunction.CountIf(Columns(2), "-")

Set rFoundCells = Columns(2).Find(What:="-", After:=ActiveCell, LookIn:=xlValues, LookAt:= _

        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _

        , SearchFormat:=False).Activate
Range("B" & rFoundCells.Row & ":B" & rFoundCells.Row+4).Entirerow.hide

Next lCount

End Sub

Open in new window

0
 

Author Comment

by:codysheilds
ID: 37039512
Hi,

I tried both prices and it didn't work.  I am using Excel 2010, would that make a difference?

Cody
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 18

Accepted Solution

by:
krishnakrkc earned 2000 total points
ID: 37039519
Hi,

Ignore my previous post. Try this one.

Kris
Sub kTest()
    
    Dim i   As Long, a(), Addr As String, n As Long, k, r As Range
    
    Application.ScreenUpdating = False
    ActiveSheet.UsedRange.EntireRow.Hidden = False
    Set r = Intersect(ActiveSheet.UsedRange, Columns(2))
    
    k = r
    
    For i = UBound(k, 1) To 1 Step -1
        If InStr(1, k(i, 1), "-") Then
            Addr = Addr & "," & "A" & i & ":A" & i + 4
            If Len(Addr) > 240 Then
                n = n + 1
                ReDim Preserve a(1 To n)
                a(n) = Mid$(Addr, 2)
                Addr = vbNullString
            End If
        End If
    Next
    If Len(Addr) > 1 Then
        n = n + 1
        ReDim Preserve a(1 To n)
        a(n) = Mid$(Addr, 2)
        Addr = vbNullString
    End If
    
    If n Then
        With r
            For i = 1 To n
                .Range(CStr(a(i))).EntireRow.Hidden = True
            Next
        End With
    End If
    Application.ScreenUpdating = True
    
End Sub

Open in new window

0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 37039530

If the code isn't working, attach a sample workbook.

0
 

Author Comment

by:codysheilds
ID: 37039736
It worked great Kris, thank you.

If its not too much trouble can I ask you for another favor.  I just realized once the rows are hidden and I need to print the this sheet along with two others in the same workbook (Sheet1, Sheet2 and Sheet3).  Is there a way I can run the next step and bring up the print dialog box or preview dialog box (I do not want to auto print).  Thanks again.

Cody
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 37039775

Try

Sub PreviewSheet()
   
    Dim MySheets, i As Long
   
    MySheets = Array("Sheet1", "Sheet2", "Sheet3")
   
    For i = LBound(MySheets) To UBound(MySheets)
        Sheets(CStr(MySheets(i))).PrintPreview
    Next
   
End Sub

Kris
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37039806
Here is another alternative to Kris' macro to hide the rows.
Sub HideRows()
Dim c As Range, i As Long, cnt As Long
With Columns("B:B")
    cnt = WorksheetFunction.CountIf(Columns("B:B"), "-")
    Set c = .Find("-", LookIn:=xlValues)
    For i = 1 To cnt
        c.Offset(-3).Resize(4).EntireRow.Hidden = True
        Set c = .FindNext(c)
    Next i
End With
End Sub

Open in new window

Kyle
0
 
LVL 12

Expert Comment

by:kgerb
ID: 37039840
This one includes showing the print dialogue box.
Sub HideRows()
Dim c As Range, i As Long, cnt As Long
With Columns("B:B")
    cnt = WorksheetFunction.CountIf(Columns("B:B"), "-")
    Set c = .Find("-", LookIn:=xlValues)
    For i = 1 To cnt
        c.Offset(-3).Resize(4).EntireRow.Hidden = True
        Set c = .FindNext(c)
    Next i
End With
Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
Application.Dialogs(xlDialogPrint).Show
End Sub

Open in new window

Kyle
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question