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

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

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

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

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