Solved

# Excel Row Hide on Condition

Posted on 2011-10-27
Medium Priority
251 Views
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
Question by:codysheilds
• 4
• 2
• 2
• +1

LVL 18

Expert Comment

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
n = n + 1
ReDim Preserve a(1 To n)
End If
End If
Next
n = n + 1
ReDim Preserve a(1 To n)
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
``````
0

LVL 11

Expert Comment

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

Author Comment

ID: 37039512
Hi,

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

Cody
0

LVL 18

Accepted Solution

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
n = n + 1
ReDim Preserve a(1 To n)
End If
End If
Next
n = n + 1
ReDim Preserve a(1 To n)
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
``````
0

LVL 18

Expert Comment

ID: 37039530

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

0

Author Comment

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

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

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
``````
Kyle
0

LVL 12

Expert Comment

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
``````
Kyle
0

## Featured Post

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.
###### Suggested Courses
Course of the Month13 days, 18 hours left to enroll