Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. | | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. | | | |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. | | | |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Group rows (or hide rows) like in MS Project | Excel Worksheet Functions | |||
Macro code to hide rows and not calculate hidden rows | Excel Discussion (Misc queries) | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
cut rows without cutting hide rows | Excel Discussion (Misc queries) | |||
Hide Rows - copy and paste only rows that show | Excel Worksheet Functions |