Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
LastCell
I want to loop through rows first to make sure they are all unhidden, then I
want to hide rows that have "yes" in a cell. This doesn't work because the xlCellTypeLastCell goes to the last visible cell. So if the last cell had "yes" in it, it would have been hidden and my code won't unhide it. I dont' know how to fix it. Hope I explained this. Thanks, Private Sub CAT() Dim i As Long For i = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row If Cells(i, 2).Rows(i).Hidden = True Then Cells(i, 2).Rows(i).Hidden = False ElseIf Cells(i, 2).Value = "Yes" Then Rows(i).EntireRow.Hidden = True End If Next i End Sub -- Howard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
LastCell
I'm sure there are other ways, but you can add this to the top of your code
to unhide all columns and rows. With Cells .Rows.Hidden = False .Columns.Hidden = False End With "Howard" wrote in message ... I want to loop through rows first to make sure they are all unhidden, then I want to hide rows that have "yes" in a cell. This doesn't work because the xlCellTypeLastCell goes to the last visible cell. So if the last cell had "yes" in it, it would have been hidden and my code won't unhide it. I dont' know how to fix it. Hope I explained this. Thanks, Private Sub CAT() Dim i As Long For i = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row If Cells(i, 2).Rows(i).Hidden = True Then Cells(i, 2).Rows(i).Hidden = False ElseIf Cells(i, 2).Value = "Yes" Then Rows(i).EntireRow.Hidden = True End If Next i End Sub -- Howard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
LastCell
rows.hidden=false
for i= if condition then hide next i -- Don Guillett SalesAid Software "Howard" wrote in message ... I want to loop through rows first to make sure they are all unhidden, then I want to hide rows that have "yes" in a cell. This doesn't work because the xlCellTypeLastCell goes to the last visible cell. So if the last cell had "yes" in it, it would have been hidden and my code won't unhide it. I dont' know how to fix it. Hope I explained this. Thanks, Private Sub CAT() Dim i As Long For i = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row If Cells(i, 2).Rows(i).Hidden = True Then Cells(i, 2).Rows(i).Hidden = False ElseIf Cells(i, 2).Value = "Yes" Then Rows(i).EntireRow.Hidden = True End If Next i End Sub -- Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
LastCell
Try it in two steps:
1. make sure ALL rows are unhidden: Cells.EntireRow.Hidden = False 2. run the logic to hide what you want -- Gary''s Student gsnu200709 "Howard" wrote: I want to loop through rows first to make sure they are all unhidden, then I want to hide rows that have "yes" in a cell. This doesn't work because the xlCellTypeLastCell goes to the last visible cell. So if the last cell had "yes" in it, it would have been hidden and my code won't unhide it. I dont' know how to fix it. Hope I explained this. Thanks, Private Sub CAT() Dim i As Long For i = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row If Cells(i, 2).Rows(i).Hidden = True Then Cells(i, 2).Rows(i).Hidden = False ElseIf Cells(i, 2).Value = "Yes" Then Rows(i).EntireRow.Hidden = True End If Next i End Sub -- Howard |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
LastCell
Thanks to all of you for your help!
-- Howard "Don Guillett" wrote: rows.hidden=false for i= if condition then hide next i -- Don Guillett SalesAid Software "Howard" wrote in message ... I want to loop through rows first to make sure they are all unhidden, then I want to hide rows that have "yes" in a cell. This doesn't work because the xlCellTypeLastCell goes to the last visible cell. So if the last cell had "yes" in it, it would have been hidden and my code won't unhide it. I dont' know how to fix it. Hope I explained this. Thanks, Private Sub CAT() Dim i As Long For i = 1 To ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).Row If Cells(i, 2).Rows(i).Hidden = True Then Cells(i, 2).Rows(i).Hidden = False ElseIf Cells(i, 2).Value = "Yes" Then Rows(i).EntireRow.Hidden = True End If Next i End Sub -- Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 BUG UsedRange/LastCell differences with Excel2003. | Excel Discussion (Misc queries) | |||
lastcell in thiscolumn function error | Excel Programming | |||
Dynamic LastCell | Excel Programming | |||
LastCell Function | Excel Programming |