Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect NULL (??) range?
My code detects an "X" in one column of a range; if present, that row is
hidden, and a new range is set to the intersection of that range and the visible cells only of that range. Underneath is the formula =COUNT((col7 rng1Work)) which counts the number of items in the intersection of the column range and the visible cells range. It all works fine - until I "X" out all five rows of the original range. Then the COUNT doesn't change from whatever it was previously. I'm assuming that since it hides all the rows, and there are no visible cells to intersect with, the INTERSECT function fails, and my On Error Resume Next flows right over it without burping - and without resetting my range. How can I detect a NULL (?? correct term??) range, or the lack of a range, when this occurs? (Note: the original range covers 5 rows, but this can vary.) Ed ' Hide rows with value of "X" For i = 1 To rngThis.Rows.Count If rngThis.Cells(i, 1).Value = "X" Or _ rngThis.Cells(i, 1).Value = "x" Then rngThis.Rows(i).Hidden = True End If Next i ' Reset range to visible cells only Range(strName).SpecialCells(xlVisible).Select Set rngVis = Selection Set rngThis = Application.Intersect(Range(strName), rngVis) Range(strName).Rows.Hidden = False rngThis.Name = strName & "Work" Calculate |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect NULL (??) range?
Something like
Dim rng as Range set rng = Range(strName).SpecialCells(xlVisible) if rng is nothing then MsgBox "Done - all not visible" exit sub end if rng.select You need to get away from selecting and activating anyway. -- Regards, Tom Ogilvy "Ed" wrote in message ... My code detects an "X" in one column of a range; if present, that row is hidden, and a new range is set to the intersection of that range and the visible cells only of that range. Underneath is the formula =COUNT((col7 rng1Work)) which counts the number of items in the intersection of the column range and the visible cells range. It all works fine - until I "X" out all five rows of the original range. Then the COUNT doesn't change from whatever it was previously. I'm assuming that since it hides all the rows, and there are no visible cells to intersect with, the INTERSECT function fails, and my On Error Resume Next flows right over it without burping - and without resetting my range. How can I detect a NULL (?? correct term??) range, or the lack of a range, when this occurs? (Note: the original range covers 5 rows, but this can vary.) Ed ' Hide rows with value of "X" For i = 1 To rngThis.Rows.Count If rngThis.Cells(i, 1).Value = "X" Or _ rngThis.Cells(i, 1).Value = "x" Then rngThis.Rows(i).Hidden = True End If Next i ' Reset range to visible cells only Range(strName).SpecialCells(xlVisible).Select Set rngVis = Selection Set rngThis = Application.Intersect(Range(strName), rngVis) Range(strName).Rows.Hidden = False rngThis.Name = strName & "Work" Calculate |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect NULL (??) range?
Thanks for the reply, Tom. I guess I didn't collect my thoughts into what I
really wanted to ask. I WANT to be able to set a "zero-value" range. If all 5 rows are "X"ed out and there are no visible cells, and I intersect the range with its visible cells, I *want* a range that will give me a COUNT of 0. I also use =AVERAGE ((col7 rng1Work)) and =STDEV ((col7 rng1Work)) and these must also have something besides error values if the range is zero rows. What would you suggest? Ed "Tom Ogilvy" wrote in message ... Something like Dim rng as Range set rng = Range(strName).SpecialCells(xlVisible) if rng is nothing then MsgBox "Done - all not visible" exit sub end if rng.select You need to get away from selecting and activating anyway. -- Regards, Tom Ogilvy "Ed" wrote in message ... My code detects an "X" in one column of a range; if present, that row is hidden, and a new range is set to the intersection of that range and the visible cells only of that range. Underneath is the formula =COUNT((col7 rng1Work)) which counts the number of items in the intersection of the column range and the visible cells range. It all works fine - until I "X" out all five rows of the original range. Then the COUNT doesn't change from whatever it was previously. I'm assuming that since it hides all the rows, and there are no visible cells to intersect with, the INTERSECT function fails, and my On Error Resume Next flows right over it without burping - and without resetting my range. How can I detect a NULL (?? correct term??) range, or the lack of a range, when this occurs? (Note: the original range covers 5 rows, but this can vary.) Ed ' Hide rows with value of "X" For i = 1 To rngThis.Rows.Count If rngThis.Cells(i, 1).Value = "X" Or _ rngThis.Cells(i, 1).Value = "x" Then rngThis.Rows(i).Hidden = True End If Next i ' Reset range to visible cells only Range(strName).SpecialCells(xlVisible).Select Set rngVis = Selection Set rngThis = Application.Intersect(Range(strName), rngVis) Range(strName).Rows.Hidden = False rngThis.Name = strName & "Work" Calculate |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect NULL (??) range?
' Hide rows with value of "X"
For i = 1 To rngThis.Rows.Count If rngThis.Cells(i, 1).Value = "X" Or _ rngThis.Cells(i, 1).Value = "x" Then rngThis.Rows(i).Hidden = True End If Next i ' Reset range to visible cells only Range(strName).SpecialCells(xlVisible).Select Set rngVis = Selection if rngVis is nothing then set rngVis = Intersect(range("col7"), Activesheet.UsedRange) set rngVis = rngVis.Offset(rngVis.Rows.count+2,0).Resize(1,1)) End if Set rngThis = Application.Intersect(Range(strName), rngVis) Range(strName).Rows.Hidden = False rngThis.Name = strName & "Work" Calculate This should make rngVis an empty cell when all rows are hidden. -- Regards, Tom Ogilvy "Ed" wrote in message ... Thanks for the reply, Tom. I guess I didn't collect my thoughts into what I really wanted to ask. I WANT to be able to set a "zero-value" range. If all 5 rows are "X"ed out and there are no visible cells, and I intersect the range with its visible cells, I *want* a range that will give me a COUNT of 0. I also use =AVERAGE ((col7 rng1Work)) and =STDEV ((col7 rng1Work)) and these must also have something besides error values if the range is zero rows. What would you suggest? Ed "Tom Ogilvy" wrote in message ... Something like Dim rng as Range set rng = Range(strName).SpecialCells(xlVisible) if rng is nothing then MsgBox "Done - all not visible" exit sub end if rng.select You need to get away from selecting and activating anyway. -- Regards, Tom Ogilvy "Ed" wrote in message ... My code detects an "X" in one column of a range; if present, that row is hidden, and a new range is set to the intersection of that range and the visible cells only of that range. Underneath is the formula =COUNT((col7 rng1Work)) which counts the number of items in the intersection of the column range and the visible cells range. It all works fine - until I "X" out all five rows of the original range. Then the COUNT doesn't change from whatever it was previously. I'm assuming that since it hides all the rows, and there are no visible cells to intersect with, the INTERSECT function fails, and my On Error Resume Next flows right over it without burping - and without resetting my range. How can I detect a NULL (?? correct term??) range, or the lack of a range, when this occurs? (Note: the original range covers 5 rows, but this can vary.) Ed ' Hide rows with value of "X" For i = 1 To rngThis.Rows.Count If rngThis.Cells(i, 1).Value = "X" Or _ rngThis.Cells(i, 1).Value = "x" Then rngThis.Rows(i).Hidden = True End If Next i ' Reset range to visible cells only Range(strName).SpecialCells(xlVisible).Select Set rngVis = Selection Set rngThis = Application.Intersect(Range(strName), rngVis) Range(strName).Rows.Hidden = False rngThis.Name = strName & "Work" Calculate |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Detect NULL (??) range?
Thank you, Tom! That's perfect!
Ed "Tom Ogilvy" wrote in message ... ' Hide rows with value of "X" For i = 1 To rngThis.Rows.Count If rngThis.Cells(i, 1).Value = "X" Or _ rngThis.Cells(i, 1).Value = "x" Then rngThis.Rows(i).Hidden = True End If Next i ' Reset range to visible cells only Range(strName).SpecialCells(xlVisible).Select Set rngVis = Selection if rngVis is nothing then set rngVis = Intersect(range("col7"), Activesheet.UsedRange) set rngVis = rngVis.Offset(rngVis.Rows.count+2,0).Resize(1,1)) End if Set rngThis = Application.Intersect(Range(strName), rngVis) Range(strName).Rows.Hidden = False rngThis.Name = strName & "Work" Calculate This should make rngVis an empty cell when all rows are hidden. -- Regards, Tom Ogilvy "Ed" wrote in message ... Thanks for the reply, Tom. I guess I didn't collect my thoughts into what I really wanted to ask. I WANT to be able to set a "zero-value" range. If all 5 rows are "X"ed out and there are no visible cells, and I intersect the range with its visible cells, I *want* a range that will give me a COUNT of 0. I also use =AVERAGE ((col7 rng1Work)) and =STDEV ((col7 rng1Work)) and these must also have something besides error values if the range is zero rows. What would you suggest? Ed "Tom Ogilvy" wrote in message ... Something like Dim rng as Range set rng = Range(strName).SpecialCells(xlVisible) if rng is nothing then MsgBox "Done - all not visible" exit sub end if rng.select You need to get away from selecting and activating anyway. -- Regards, Tom Ogilvy "Ed" wrote in message ... My code detects an "X" in one column of a range; if present, that row is hidden, and a new range is set to the intersection of that range and the visible cells only of that range. Underneath is the formula =COUNT((col7 rng1Work)) which counts the number of items in the intersection of the column range and the visible cells range. It all works fine - until I "X" out all five rows of the original range. Then the COUNT doesn't change from whatever it was previously. I'm assuming that since it hides all the rows, and there are no visible cells to intersect with, the INTERSECT function fails, and my On Error Resume Next flows right over it without burping - and without resetting my range. How can I detect a NULL (?? correct term??) range, or the lack of a range, when this occurs? (Note: the original range covers 5 rows, but this can vary.) Ed ' Hide rows with value of "X" For i = 1 To rngThis.Rows.Count If rngThis.Cells(i, 1).Value = "X" Or _ rngThis.Cells(i, 1).Value = "x" Then rngThis.Rows(i).Hidden = True End If Next i ' Reset range to visible cells only Range(strName).SpecialCells(xlVisible).Select Set rngVis = Selection Set rngThis = Application.Intersect(Range(strName), rngVis) Range(strName).Rows.Hidden = False rngThis.Name = strName & "Work" Calculate |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum a range that IS NOT NULL | Excel Worksheet Functions | |||
Detect strings in a range | Excel Worksheet Functions | |||
Detect non-blank range | Excel Discussion (Misc queries) | |||
Detect if a selected cell is within a named range | Excel Programming | |||
How Do I Detect An Entry In A Range Of Cells | Excel Programming |