Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Visible Range?
I know there are lots of ways to do this procedure, but I was hoping
there might be something already defined. I have a macro that filters out multiple rows based on certain criteria. What I'd like to do is use a conditional statement to determine if there are no remaining visible rows in the used range. If Worksheets("Sheet 1").UsedRange.Rows.SpecialCells(xlVisible).Coun t = 0 Then 'More code here End If Worksheets("Sheet 1").UsedRange.Rows.SpecialCells(xlVisible).Coun t evaluates, but not to the corrent value (when no rows in the used range were visible it evalutated to 10560) Seems to me like this should work, but its not. Any help would be wonderful |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Visible Range?
If Worksheets("Sheet 1").UsedRange.Columns(1).SpecialCells(xlVisible).C ount =
1 Then ' no rows are visible 'More code here End If At least the header row should be visible. If your headers are in the first row and you have no entirely blank rows in your list if Activesheet.Range("A1").CurrentRegion.Columns(1).S pecialCells(xlVisible).count = 1 then ' no rows are visible -- Regards, Tom Ogilvy "Matt" wrote: I know there are lots of ways to do this procedure, but I was hoping there might be something already defined. I have a macro that filters out multiple rows based on certain criteria. What I'd like to do is use a conditional statement to determine if there are no remaining visible rows in the used range. If Worksheets("Sheet 1").UsedRange.Rows.SpecialCells(xlVisible).Coun t = 0 Then 'More code here End If Worksheets("Sheet 1").UsedRange.Rows.SpecialCells(xlVisible).Coun t evaluates, but not to the corrent value (when no rows in the used range were visible it evalutated to 10560) Seems to me like this should work, but its not. Any help would be wonderful |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Visible Range?
i actually have a header row and a subtotals row that aren't being
hidden but, it makes no sense why i keep getting 10560 as the value for: Worksheets("Sheet 1").UsedRange.Rows.SpecialCells(xlVisible).Coun t I have a watch and stepped through the entire macro, when it hides the last row (before leaving only the header and totals) the value jumps might i ask why you used columns in your code rather than rows? the data is all in rows, but if there is something i'm missing and should be using columns instead, that could be my problem thanks Tom Ogilvy wrote: If Worksheets("Sheet 1").UsedRange.Columns(1).SpecialCells(xlVisible).C ount = 1 Then ' no rows are visible 'More code here End If At least the header row should be visible. If your headers are in the first row and you have no entirely blank rows in your list if Activesheet.Range("A1").CurrentRegion.Columns(1).S pecialCells(xlVisible).count = 1 then ' no rows are visible -- Regards, Tom Ogilvy "Matt" wrote: I know there are lots of ways to do this procedure, but I was hoping there might be something already defined. I have a macro that filters out multiple rows based on certain criteria. What I'd like to do is use a conditional statement to determine if there are no remaining visible rows in the used range. If Worksheets("Sheet 1").UsedRange.Rows.SpecialCells(xlVisible).Coun t = 0 Then 'More code here End If Worksheets("Sheet 1").UsedRange.Rows.SpecialCells(xlVisible).Coun t evaluates, but not to the corrent value (when no rows in the used range were visible it evalutated to 10560) Seems to me like this should work, but its not. Any help would be wonderful |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Visible Range?
Worksheets("Sheet
1").UsedRange.Columns(1).SpecialCells(xlVisible).C ount worked perfectly, thanks still curious why Colunms(1) though, if you could explain that would be great Matt wrote: i actually have a header row and a subtotals row that aren't being hidden but, it makes no sense why i keep getting 10560 as the value for: Worksheets("Sheet 1").UsedRange.Rows.SpecialCells(xlVisible).Coun t I have a watch and stepped through the entire macro, when it hides the last row (before leaving only the header and totals) the value jumps might i ask why you used columns in your code rather than rows? the data is all in rows, but if there is something i'm missing and should be using columns instead, that could be my problem thanks Tom Ogilvy wrote: If Worksheets("Sheet 1").UsedRange.Columns(1).SpecialCells(xlVisible).C ount = 1 Then ' no rows are visible 'More code here End If At least the header row should be visible. If your headers are in the first row and you have no entirely blank rows in your list if Activesheet.Range("A1").CurrentRegion.Columns(1).S pecialCells(xlVisible).count = 1 then ' no rows are visible -- Regards, Tom Ogilvy "Matt" wrote: I know there are lots of ways to do this procedure, but I was hoping there might be something already defined. I have a macro that filters out multiple rows based on certain criteria. What I'd like to do is use a conditional statement to determine if there are no remaining visible rows in the used range. If Worksheets("Sheet 1").UsedRange.Rows.SpecialCells(xlVisible).Coun t = 0 Then 'More code here End If Worksheets("Sheet 1").UsedRange.Rows.SpecialCells(xlVisible).Coun t evaluates, but not to the corrent value (when no rows in the used range were visible it evalutated to 10560) Seems to me like this should work, but its not. Any help would be wonderful |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count Visible Range?
this counts the cells in a single column - so it counts the number of rows.
Columns(1).Resize(,2).specialCells(xlVisible).Coun t would give a count of 2 for each row and so forth. Just recognize the UsedRange doesn't define the extent of the cells that contain data. It give the smallest rectangular area the describes the cells which Excel is actually maintaining information about. While you see all the cells (65536 rows x 256 columns), any cells outside the UsedRange are virtual. So Usedrange will contain as a minimum, all the cells that contain data, but may also include more cells. There are more reasons beyond data in a cell that will cause excel to maintain information. Insert a new blank worksheet. put data in A1. Then go to row 10000 and make the row height slightly larger. Then go back and select A1. Then run this macro Sub ShowRange() msgbox ActiveSheet.UsedRange.Address End Sub It gave me A1:A10000 -- Regards, Tom Ogilvy "Matt" wrote in message ups.com... Worksheets("Sheet 1").UsedRange.Columns(1).SpecialCells(xlVisible).C ount worked perfectly, thanks still curious why Colunms(1) though, if you could explain that would be great Matt wrote: i actually have a header row and a subtotals row that aren't being hidden but, it makes no sense why i keep getting 10560 as the value for: Worksheets("Sheet 1").UsedRange.Rows.SpecialCells(xlVisible).Coun t I have a watch and stepped through the entire macro, when it hides the last row (before leaving only the header and totals) the value jumps might i ask why you used columns in your code rather than rows? the data is all in rows, but if there is something i'm missing and should be using columns instead, that could be my problem thanks Tom Ogilvy wrote: If Worksheets("Sheet 1").UsedRange.Columns(1).SpecialCells(xlVisible).C ount = 1 Then ' no rows are visible 'More code here End If At least the header row should be visible. If your headers are in the first row and you have no entirely blank rows in your list if Activesheet.Range("A1").CurrentRegion.Columns(1).S pecialCells(xlVisible).count = 1 then ' no rows are visible -- Regards, Tom Ogilvy "Matt" wrote: I know there are lots of ways to do this procedure, but I was hoping there might be something already defined. I have a macro that filters out multiple rows based on certain criteria. What I'd like to do is use a conditional statement to determine if there are no remaining visible rows in the used range. If Worksheets("Sheet 1").UsedRange.Rows.SpecialCells(xlVisible).Coun t = 0 Then 'More code here End If Worksheets("Sheet 1").UsedRange.Rows.SpecialCells(xlVisible).Coun t evaluates, but not to the corrent value (when no rows in the used range were visible it evalutated to 10560) Seems to me like this should work, but its not. Any help would be wonderful |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to keep Status count visible? | Excel Worksheet Functions | |||
Count only visible cells | Excel Discussion (Misc queries) | |||
count if on Visible - Filtered | Excel Worksheet Functions | |||
count only visible cells | Excel Worksheet Functions | |||
Count Visible | Excel Programming |