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
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
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
ASKER
Hi,
I tried both prices and it didn't work. I am using Excel 2010, would that make a difference?
Cody
I tried both prices and it didn't work. I am using Excel 2010, would that make a difference?
Cody
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If the code isn't working, attach a sample workbook.
ASKER
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
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))).
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
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
Kyle
Try
Kris
Open in new window