Hide rows

  • Thread starter Thread starter Patti
  • Start date Start date
P

Patti

Can anyone explain to me why this doesn't work? I am trying to filter a
sheet so that I am only viewing rows that have a bolded cell anywhere in
columns E:H. The code is hiding all rows in the worksheet.

Sub test()

For Each rngCell In Intersect(ActiveSheet.UsedRange,
ActiveSheet.Columns("e:h"))
If rngCell.Font.Bold = False Then
rngCell.EntireRow.Hidden = True
End If
Next

End Sub

Thanks in advance.
 
Hi Patti

Your code works although I'd be inclined to amend it with the following....

Sub test()
Dim rngcell As Range
Application.ScreenUpdating = False
With ActiveSheet
Intersect(.UsedRange, _
..Columns("e:h")).EntireRow.Hidden = False
For Each rngcell In Intersect(.UsedRange, _
..Columns("e:h"))
If rngcell.Font.Bold = False Then _
rngcell.EntireRow.Hidden = True
Next rngcell
End With
Application.ScreenUpdating = True
End Sub



--
XL2002
Regards

William

(e-mail address removed)

| Can anyone explain to me why this doesn't work? I am trying to filter a
| sheet so that I am only viewing rows that have a bolded cell anywhere in
| columns E:H. The code is hiding all rows in the worksheet.
|
| Sub test()
|
| For Each rngCell In Intersect(ActiveSheet.UsedRange,
| ActiveSheet.Columns("e:h"))
| If rngCell.Font.Bold = False Then
| rngCell.EntireRow.Hidden = True
| End If
| Next
|
| End Sub
|
| Thanks in advance.
|
|
|
 
Hi William,

I appreciate your interest, but the code doesn't work. As I said, it hides
_all_ of the rows on the worksheet. I tried your ammended code, and that is
hiding all of the rows as well.

Any idea why? The cells are not bolded by conditional formatting, and if I
select one the immediate window shows:

? activecell.font.bold
True


Patti
 
Hi Patti,

As written, your code will hide a row if ANY of the cells in columns E:H are
not bold.
Therefore, try reversing the logic:

Sub testBold()
Dim rngCell As Range
ActiveSheet.UsedRange.Rows.Hidden = True
For Each rngCell In Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns("E:H"))
If rngCell.EntireRow.Hidden = True Then
If rngCell.Font.Bold = True Then
rngCell.EntireRow.Hidden = False
End If
End If
Next

End Sub
 
Patti

You are testing each cell in the range. If any cell in column E through H is
not Bold, the row will be hidden.

Try this........

Sub test()
For Each rngcell In Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns("e:h"))
If rngcell.EntireRow.Font.Bold = False Then
rngcell.EntireRow.Hidden = True
End If
Next
End Sub

Gord Dibben Excel MVP
 
Hi Gord,

I think that this fails where there are no bold cells in a row's column E:H
range, while there are bold cells in one other column(s) on the row.

Adapting your approach (which is more elegant than mine):

Sub HideRows()
Dim rngCell As Range
For Each rngCell In Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns("E"))
If rngCell.Resize(1, 4).Font.Bold = False Then
rngCell.EntireRow.Hidden = True
Else
rngCell.EntireRow.Hidden = False
End If
Next
End Sub
 
Hi Patti

Firstly, your original code does not unhide cells which are bold, it only
hides rows that - I amended this in my code by unhiding all rows at the
start of the macro.

Secondly, your code is saying that if ANY of the 4 cells in the relevant row
of columns E to H are NOT bold, then hide the entire row. So, if cells E16
was not bold and F16, G16 and H16 were bold, then the row would be hidden -
is that what you want?

--
XL2002
Regards

William

(e-mail address removed)

| Hi William,
|
| I appreciate your interest, but the code doesn't work. As I said, it
hides
| _all_ of the rows on the worksheet. I tried your ammended code, and that
is
| hiding all of the rows as well.
|
| Any idea why? The cells are not bolded by conditional formatting, and if
I
| select one the immediate window shows:
|
| ? activecell.font.bold
| True
|
|
| Patti
|
|
| | > Hi Patti
| >
| > Your code works although I'd be inclined to amend it with the
| following....
| >
| > Sub test()
| > Dim rngcell As Range
| > Application.ScreenUpdating = False
| > With ActiveSheet
| > Intersect(.UsedRange, _
| > .Columns("e:h")).EntireRow.Hidden = False
| > For Each rngcell In Intersect(.UsedRange, _
| > .Columns("e:h"))
| > If rngcell.Font.Bold = False Then _
| > rngcell.EntireRow.Hidden = True
| > Next rngcell
| > End With
| > Application.ScreenUpdating = True
| > End Sub
| >
| >
| >
| > --
| > XL2002
| > Regards
| >
| > William
| >
| > (e-mail address removed)
| >
| > | > | Can anyone explain to me why this doesn't work? I am trying to
filter
| a
| > | sheet so that I am only viewing rows that have a bolded cell anywhere
in
| > | columns E:H. The code is hiding all rows in the worksheet.
| > |
| > | Sub test()
| > |
| > | For Each rngCell In Intersect(ActiveSheet.UsedRange,
| > | ActiveSheet.Columns("e:h"))
| > | If rngCell.Font.Bold = False Then
| > | rngCell.EntireRow.Hidden = True
| > | End If
| > | Next
| > |
| > | End Sub
| > |
| > | Thanks in advance.
| > |
| > |
| > |
| >
| >
|
|
 
Norman

Looking gooder all the time.

Hides rows if no cell in E through H is bolded.

Ignores all other columns.

Add William's unhide all rows to start(just in case) and we get....

Sub HideRows()
Dim rngCell As Range
With ActiveSheet
Intersect(.UsedRange, _
.Columns("E:H")).EntireRow.Hidden = False
End With
For Each rngCell In Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns("E"))
If rngCell.Resize(1, 4).Font.Bold = False Then
rngCell.EntireRow.Hidden = True
Else
rngCell.EntireRow.Hidden = False
End If
Next
End Sub

Gord
 
Hi Gord,
Add William's unhide all rows to start(just in case) and we get....
Click to expand...

I think that this is already covered by the existing else condition:

Else
rngCell.EntireRow.Hidden = False
End If
 
Norman

I was thinking that some rows may already be hidden manually and may not get
picked up if bold.

Missed the Else line that looks after that.

Having a bad day.

Ladies day at the golf course and I am missing my "fix".

Maybe go out and pound a bucket of balls into the side of the house.

Thanks, Gord
 
Thanks for the enlightenment gentlemen!

Patti


Gord Dibben said:
Norman

I was thinking that some rows may already be hidden manually and may not get
picked up if bold.

Missed the Else line that looks after that.

Having a bad day.

Ladies day at the golf course and I am missing my "fix".

Maybe go out and pound a bucket of balls into the side of the house.

Thanks, Gord

Hi Gord,
Add William's unhide all rows to start(just in case) and we get....
Click to expand...

I think that this is already covered by the existing else condition:

Else
rngCell.EntireRow.Hidden = False
End If

---
Regards,
Norman

I think tha
Gord Dibben said:
Norman

Looking gooder all the time.

Hides rows if no cell in E through H is bolded.

Ignores all other columns.

Add William's unhide all rows to start(just in case) and we get....

Sub HideRows()
Dim rngCell As Range
With ActiveSheet
Intersect(.UsedRange, _
.Columns("E:H")).EntireRow.Hidden = False
End With
For Each rngCell In Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns("E"))
If rngCell.Resize(1, 4).Font.Bold = False Then
rngCell.EntireRow.Hidden = True
Else
rngCell.EntireRow.Hidden = False
End If
Next
End Sub

Gord

On Tue, 6 Jul 2004 19:45:33 +0100, "Norman Jones"

Hi Gord,

I think that this fails where there are no bold cells in a row's
Click to expand...
column
E:H
range, while there are bold cells in one other column(s) on the row.

Adapting your approach (which is more elegant than mine):

Sub HideRows()
Dim rngCell As Range
For Each rngCell In Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns("E"))
If rngCell.Resize(1, 4).Font.Bold = False Then
rngCell.EntireRow.Hidden = True
Else
rngCell.EntireRow.Hidden = False
End If
Next
End Sub


---
Regards,
Norman

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
Patti

You are testing each cell in the range. If any cell in column E through H
is
not Bold, the row will be hidden.

Try this........

Sub test()
For Each rngcell In Intersect(ActiveSheet.UsedRange, _
ActiveSheet.Columns("e:h"))
If rngcell.EntireRow.Font.Bold = False Then
rngcell.EntireRow.Hidden = True
End If
Next
End Sub

Gord Dibben Excel MVP
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

hide rows of blank cells 2
Hide columns and rows then separate the spreadsheet into different files 1
Hide column based on data validation 0
Problem creating a macro 1
Automatically hide rows with 0 value....faster? 5
Hiding Columns 2
Instruct to start in Row 5 4
Identify each cell that has dependents 2

Back
Top