Solved

# Excel Row Hide on Condition

Posted on 2011-10-27
193 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

LVL 18

Expert Comment

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

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

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

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

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

0

Author Comment

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

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

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

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

### Suggested Solutions

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.