Number of Rows & Columns in a Named Range

  • Thread starter Michael Excel Dude
  • Start date
M

Michael Excel Dude

Two Part Question:

1. In VBA, how do I determine the number of rows or columns in a named
ranged? This will determine the number of times my loop runs.

2. If I'm looking for a specific value in a range (it's first
occurrence), how do I determine the row and column number of that
value? If that value is not found in the range, will the formula
return 0?

P.S. This site has been extremely helpful to me.

Thanks!

Michael
 
D

Dave Peterson

#1. Is it a nice contiguous range -- just one area?

with worksheets("somesheet").range("somerng")
msgbox .rows.count & vblf & .columns.count
end with

#2. With VBA?
I think it depends on how you define first.
If your range is A1:E99, and the same value occurs in A2 and B1, which is first?

In either case, you could loop through the columns looking for a match or loop
through the rows looking for a match.

You could go cell-by-cell or use application.match() to find if there is a
match.

Dim myCol as range
dim myCell as range
dim FoundIt as range
for each mycol in worksheets("somesheet").range("somerng").columns
for each mycell in mycol.cells
'check it to see if it matches
if mycell.value = "hithere" then
foundit = true
exit for
end if
next mycell
next mycol


or to go by rows...


Dim myRow as range
dim res as variant
dim FoundIt as range
for each mycol in worksheets("somesheet").range("somerng").rows
for each mycell in myRow.cells
'check it to see if it matches
if mycell.value = "hithere" then
foundit = true
exit for
end if
next mycell
next myrow

====
and then (in either case):
if foundit=true then
msgbox mycell.address
else
msgbox "not found"
end if

===============================
Using application.match

Dim myCol as range
dim myCell as range
dim FoundIt as range
for each mycol in worksheets("somesheet").range("somerng").columns
res = application.match("hithere",mycol,0)
if isnumeric(res) then
foundit = true
exit for
end if
next mycell
next mycol

if foundit = true then
msgbox mycol.cells(1).offset(res-1,0)
else
msgbox "not found"
end if

======
or

Dim myrow as range
dim res as variant
dim FoundIt as range
for each myrow in worksheets("somesheet").range("somerng").rows
res = application.match("hithere",myrow,0)
if isnumeric(res) then
foundit = true
exit for
end if
next mycell
next mycol

if foundit = true then
msgbox myrow.cells(1).offset(0,res-1)
else
msgbox "not found"
end if

=========
Uncompiled, untested. Watch out for typos in any of these.
 
Top