Link to home
Start Free TrialLog in
Avatar of codysheilds
codysheilds

asked on

Excel Row Hide on Condition

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
Avatar of krishnakrkc
krishnakrkc
Flag of India image

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

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

Avatar of codysheilds
codysheilds

ASKER

Hi,

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

Cody
ASKER CERTIFIED SOLUTION
Avatar of krishnakrkc
krishnakrkc
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

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

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

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
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
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