How to loop through all ranges in a worksheet

  • Thread starter Thread starter Nanette
  • Start date Start date
N

Nanette

I'm trying to programmatically determine if a range exists
in an existing workbook. I'm using the following code and
it's erroring out:

========
Set validationWkSht = ThisWorkbook.Worksheets
("Validation")
With validationWkSht
For i = 0 To .Range.Count
<stuff>
Next i
End With
========

I'm getting "Compile Error: Argument not optional"
applying to ".Range.Count". Thing is, I'm trying to
use ".Range" as a collection because there's supposed to
be a range collection w/a count propery.

Can anyone help me here? Thanks.
 
What do you mean by ranges?
Do you use named ranges or do you want to loop through all cells
in the worksheet.

Or all cells in a range(a1:c20 or so)
 
Hi, You cannot use the range.count that way,
you can use it this way sheet1.range("a1:a33").count
Which will obviusly give you 33 as a value.

Are looking for something specifically in a range?
Earl
 
I think you don't understand what Count does.
========
Set validationWkSht = ThisWorkbook.Worksheets
("Validation")
With validationWkSht
For i = 0 To .Range.Count
<stuff>
Next i
End With
========
Click to expand...

The error message you are getting is because the VBE wants
you to define a Range.

i.e.
--------
..Range("A1:A5").Count
--------

Would return 5

Count does not return a count of Range Objects that have
been defined for that worksheet.
 
You're right, that's not what I want it to do.

I have a named range and I want to test if it exists (i.e.
that nobody has deleted it accidentally.) So yes, I want
to be able to loop through all of the named ranges in
either the workbook or the worksheet. Is there a way to do
that? Thanks.

Nanette
 
You can loop through all the names in a workbook with code like

Dim Nm As Name
For Each Nm In ThisWorkbook.Names
' do something with Nm
Next Nm

You can test whether a name exists with code like

Dim N As Integer
On Error Resume Next
N = Len(ThisWorkbook.Names("TheName").Name)
If N > 0 Then
' name exists
Else
' name doesn't exist
End If
On Error Goto 0

Somethimes a name exists, but contains a #REF error because the
range to which it refered has been deleted. To test this
condition, use code like

Dim Nm As Name
Set Nm = ThisWorkbook.Names("TheName")
If InStr(Nm.RefersTo,"#REF") > 0 Then
' name contains a #REF error
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Awesome! Major help! Thanks.

Nanette
-----Original Message-----
You can loop through all the names in a workbook with code like

Dim Nm As Name
For Each Nm In ThisWorkbook.Names
' do something with Nm
Next Nm

You can test whether a name exists with code like

Dim N As Integer
On Error Resume Next
N = Len(ThisWorkbook.Names("TheName").Name)
If N > 0 Then
' name exists
Else
' name doesn't exist
End If
On Error Goto 0

Somethimes a name exists, but contains a #REF error because the
range to which it refered has been deleted. To test this
condition, use code like

Dim Nm As Name
Set Nm = ThisWorkbook.Names("TheName")
If InStr(Nm.RefersTo,"#REF") > 0 Then
' name contains a #REF error
End If

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com





.
Click to expand...
 
Back
Top