Hide rows
 

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.

Hide rows
 

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



"Patti" wrote in message
...
| 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.
|
|
|

Hide rows
 

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


"William" wrote in message
...
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



"Patti" wrote in message
...
| 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.
|
|
|

Hide rows
 

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


---
Regards,
Norman

"Patti" wrote in message
...
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


"William" wrote in message
...
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



"Patti" wrote in message
...
| 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.
|
|
|

Hide rows
 

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

On Mon, 5 Jul 2004 23:17:18 -0500, "Patti" wrote:

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.

Hide rows
 

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


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

On Mon, 5 Jul 2004 23:17:18 -0500, "Patti" wrote:

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.

Hide rows
 

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



"Patti" wrote in message
...
| 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
|
|
| "William" wrote in message
| ...
| 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
|
|
|
| "Patti" wrote in message
| ...
| | 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.
| |
| |
| |
|
|
|
|

Hide rows
 

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"
wrote:

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


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

On Mon, 5 Jul 2004 23:17:18 -0500, "Patti" wrote:

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.

Hide rows
 

Hi Gord,

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

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" <gorddibbATshawDOTca wrote in message
...
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"
wrote:

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


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

On Mon, 5 Jul 2004 23:17:18 -0500, "Patti" wrote:

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.

Hide rows
 

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

On Tue, 6 Jul 2004 23:03:35 +0100, "Norman Jones"
wrote:

Hi Gord,

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

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" <gorddibbATshawDOTca wrote in message
.. .
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"
wrote:

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


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

On Mon, 5 Jul 2004 23:17:18 -0500, "Patti" wrote:

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.

Hide rows
 

Thanks for the enlightenment gentlemen!

Patti


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Tue, 6 Jul 2004 23:03:35 +0100, "Norman Jones"
wrote:

Hi Gord,

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

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" <gorddibbATshawDOTca wrote in message
.. .
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"
wrote:

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


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

On Mon, 5 Jul 2004 23:17:18 -0500, "Patti"
wrote:

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.

Hide rows
 

Thanks for the feedback Patti.

You can learn all sorts of neat stuff on these Excel groups, like how to
re-arrange your siding<g

Gord

On Wed, 7 Jul 2004 01:01:58 -0500, "Patti" wrote:

Thanks for the enlightenment gentlemen!

Patti


"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
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

On Tue, 6 Jul 2004 23:03:35 +0100, "Norman Jones"
wrote:

Hi Gord,

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

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" <gorddibbATshawDOTca wrote in message
.. .
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"
wrote:

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


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

On Mon, 5 Jul 2004 23:17:18 -0500, "Patti"
wrote:

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.