Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide row if particular cells are empty
Text can be in none, any or all of the cells of columns 4, 5, 6 and 7 in
each row. I need to hide the row only if all cells 4, 5, 6 and 7 are empty but leave the row visible if any cell includes text. I just cannot see how to do it - everything else is ok - determining the number of rows which vary and progressing from row to row - it is just the test in each row I need help with. Best wishes for 2007 - another hour to go here in the UK. Francis Hookham |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide row if particular cells are empty
Assuming the cells are actually blank and NOT just look blank with a formula
of type =if(true,"","abc") Sub HideRows() Dim rng As Range, rng1 As Range Dim ar As Range. rw as Range Rows.Hidden = False Set rng = Intersect(ActiveSheet.UsedRange, ActiveSheet.Columns(4).Resize(, 4)) Set rng1 = rng.SpecialCells(xlBlanks) For Each ar In rng1.Areas For Each rw In ar.Rows If rw.Cells.Count = 4 Then rw.EntireRow.Hidden = True End If Next Next End Sub -- Regards, Tom Ogilvy "Francis Hookham" wrote in message ... Text can be in none, any or all of the cells of columns 4, 5, 6 and 7 in each row. I need to hide the row only if all cells 4, 5, 6 and 7 are empty but leave the row visible if any cell includes text. I just cannot see how to do it - everything else is ok - determining the number of rows which vary and progressing from row to row - it is just the test in each row I need help with. Best wishes for 2007 - another hour to go here in the UK. Francis Hookham |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide row if particular cells are empty
Hi Francis,
Try this little macro. Option Explicit Sub hiderowson4567() Dim lLastRow As Long, lRow As Long With ActiveSheet lLastRow = .UsedRange.Row + .UsedRange.Rows.Count - 1 For lRow = 1 To lLastRow If IsEmpty(.Cells(lRow, 4)) Then If IsEmpty(.Cells(lRow, 5)) Then If IsEmpty(.Cells(lRow, 6)) Then If IsEmpty(.Cells(lRow, 7)) Then .Rows(lRow).Hidden = True End If End If End If End If Next lRow End With End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Francis Hookham" wrote: Text can be in none, any or all of the cells of columns 4, 5, 6 and 7 in each row. I need to hide the row only if all cells 4, 5, 6 and 7 are empty but leave the row visible if any cell includes text. I just cannot see how to do it - everything else is ok - determining the number of rows which vary and progressing from row to row - it is just the test in each row I need help with. Best wishes for 2007 - another hour to go here in the UK. Francis Hookham |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide row if particular cells are empty
Many thanks Martin and Tom - I have used Martn's solution because I
understand the construction whereas Tom's is a bit beyond my simple VBA abilities. My rate for this reply? It is the same as always - I am amazed at the speed and quality of the replies and very grateful for your dedication to helping us. Francis Hookham "Martin Fishlock" wrote in message ... Hi Francis, Try this little macro. Option Explicit Sub hiderowson4567() Dim lLastRow As Long, lRow As Long With ActiveSheet lLastRow = .UsedRange.Row + .UsedRange.Rows.Count - 1 For lRow = 1 To lLastRow If IsEmpty(.Cells(lRow, 4)) Then If IsEmpty(.Cells(lRow, 5)) Then If IsEmpty(.Cells(lRow, 6)) Then If IsEmpty(.Cells(lRow, 7)) Then .Rows(lRow).Hidden = True End If End If End If End If Next lRow End With End Sub -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Francis Hookham" wrote: Text can be in none, any or all of the cells of columns 4, 5, 6 and 7 in each row. I need to hide the row only if all cells 4, 5, 6 and 7 are empty but leave the row visible if any cell includes text. I just cannot see how to do it - everything else is ok - determining the number of rows which vary and progressing from row to row - it is just the test in each row I need help with. Best wishes for 2007 - another hour to go here in the UK. Francis Hookham |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to colour empty cells (cells not recognized as empty) | Excel Programming | |||
Macro to hide rows with empty cells | Excel Worksheet Functions | |||
Hide row with empty cells | Excel Programming | |||
Hide row with empty cells | Excel Programming | |||
Can blank cells created using empty Double-Quotes not be empty?? | Excel Programming |