Determine the size of a range
 

Say I have values in cell A5, A6 and A7, what is the easiest way to determine:
1. The number of rows in the range (answer = 3)
2. The last row number in the range (answer =7)

The first row number is known (5) as variable: fstRow

I can do it by counting every cell until I get to the first empty cell with
a Do until...loop routine, but it seems like a lot of code to determine a
simple thing. Agree?

Thank you
The Doctor

Determine the size of a range
 

Hi Dr Schwartz,

1. The number of rows in the range (answer = 3)
Range("A5").End(xlDown).Row -Range("A5").Row + 1

2. The last row number in the range (answer =7)
Range("A5").End(xlDown).Row

---
Regards,
Norman



"Dr.Schwartz" wrote in message
...
Say I have values in cell A5, A6 and A7, what is the easiest way to
determine:
1. The number of rows in the range (answer = 3)
2. The last row number in the range (answer =7)

The first row number is known (5) as variable: fstRow

I can do it by counting every cell until I get to the first empty cell
with
a Do until...loop routine, but it seems like a lot of code to determine a
simple thing. Agree?

Thank you
The Doctor

Determine the size of a range
 

Sub test()
Dim rwcnt, lstrw
Range("A5").Select
rwcnt = ActiveCell.CurrentRegion.Rows.Count
MsgBox (rwcnt)
lstrw = ActiveCell.Row + rwcnt - 1
MsgBox (lstrw)
End Sub

Mike F
"Dr.Schwartz" wrote in message
...
Say I have values in cell A5, A6 and A7, what is the easiest way to
determine:
1. The number of rows in the range (answer = 3)
2. The last row number in the range (answer =7)

The first row number is known (5) as variable: fstRow

I can do it by counting every cell until I get to the first empty cell
with
a Do until...loop routine, but it seems like a lot of code to determine a
simple thing. Agree?

Thank you
The Doctor

Determine the size of a range
 

?Range(Range("A" & fstRow), Range("A" & fstRow).End(xlDown)).Rows.Count
3

?Range("A" & fstRow).End(xlDown).Row
7

--

Vasant

"Dr.Schwartz" wrote in message
...
Say I have values in cell A5, A6 and A7, what is the easiest way to
determine:
1. The number of rows in the range (answer = 3)
2. The last row number in the range (answer =7)

The first row number is known (5) as variable: fstRow

I can do it by counting every cell until I get to the first empty cell
with
a Do until...loop routine, but it seems like a lot of code to determine a
simple thing. Agree?

Thank you
The Doctor

Determine the size of a range
 

Thanks Norman - just what I was looking for.

Now I'm trying to apply this to columns using:

Range("A5").End(xlRight).Column

Without luck
Can you perhaps help me out?

Thanks, The Doctor

"Norman Jones" wrote:

Hi Dr Schwartz,

1. The number of rows in the range (answer = 3)
Range("A5").End(xlDown).Row -Range("A5").Row + 1

2. The last row number in the range (answer =7)
Range("A5").End(xlDown).Row

---
Regards,
Norman



"Dr.Schwartz" wrote in message
...
Say I have values in cell A5, A6 and A7, what is the easiest way to
determine:
1. The number of rows in the range (answer = 3)
2. The last row number in the range (answer =7)

The first row number is known (5) as variable: fstRow

I can do it by counting every cell until I get to the first empty cell
with
a Do until...loop routine, but it seems like a lot of code to determine a
simple thing. Agree?

Thank you
The Doctor

Determine the size of a range
 

Almost Doc!

Range("A5").End(xltoRight).Column

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dr.Schwartz" wrote in message
...
Thanks Norman - just what I was looking for.

Now I'm trying to apply this to columns using:

Range("A5").End(xlRight).Column

Without luck
Can you perhaps help me out?

Thanks, The Doctor

"Norman Jones" wrote:

Hi Dr Schwartz,

1. The number of rows in the range (answer = 3)
Range("A5").End(xlDown).Row -Range("A5").Row + 1

2. The last row number in the range (answer =7)
Range("A5").End(xlDown).Row

---
Regards,
Norman



"Dr.Schwartz" wrote in message
...
Say I have values in cell A5, A6 and A7, what is the easiest way to
determine:
1. The number of rows in the range (answer = 3)
2. The last row number in the range (answer =7)

The first row number is known (5) as variable: fstRow

I can do it by counting every cell until I get to the first empty cell
with
a Do until...loop routine, but it seems like a lot of code to
determine a
simple thing. Agree?

Thank you
The Doctor

Determine the size of a range
 

Thank you Bob, that works, but I want to use it like this with cell instead:

Dim Headers as Range
Set Headers = Range(Cells(45, 2).End(xlToRight).Column)

I get an: Method 'Range' of object '_worksheet' failed error.
Almost there, please help me out. Thanks.

"Bob Phillips" wrote:

Almost Doc!

Range("A5").End(xltoRight).Column

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dr.Schwartz" wrote in message
...
Thanks Norman - just what I was looking for.

Now I'm trying to apply this to columns using:

Range("A5").End(xlRight).Column

Without luck
Can you perhaps help me out?

Thanks, The Doctor

"Norman Jones" wrote:

Hi Dr Schwartz,

1. The number of rows in the range (answer = 3)
Range("A5").End(xlDown).Row -Range("A5").Row + 1

2. The last row number in the range (answer =7)
Range("A5").End(xlDown).Row

---
Regards,
Norman



"Dr.Schwartz" wrote in message
...
Say I have values in cell A5, A6 and A7, what is the easiest way to
determine:
1. The number of rows in the range (answer = 3)
2. The last row number in the range (answer =7)

The first row number is known (5) as variable: fstRow

I can do it by counting every cell until I get to the first empty cell
with
a Do until...loop routine, but it seems like a lot of code to
determine a
simple thing. Agree?

Thank you
The Doctor

Determine the size of a range
 

How about:
Option Explicit
Sub testme()

Dim Headers As Range
Set Headers = Cells(45, 2).End(xlToRight)
MsgBox Headers.Address
'or
Set Headers = Range(Cells(45, 2), Cells(45, 2).End(xlToRight))
MsgBox Headers.Address

End Sub

I'm guessing (in the second version) that you'd want more than one cell when you
have a variable named Headers.

(And your headers are in row 45??)


Dr.Schwartz wrote:

Thank you Bob, that works, but I want to use it like this with cell instead:

Dim Headers as Range
Set Headers = Range(Cells(45, 2).End(xlToRight).Column)

I get an: Method 'Range' of object '_worksheet' failed error.
Almost there, please help me out. Thanks.

"Bob Phillips" wrote:

Almost Doc!

Range("A5").End(xltoRight).Column

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dr.Schwartz" wrote in message
...
Thanks Norman - just what I was looking for.

Now I'm trying to apply this to columns using:

Range("A5").End(xlRight).Column

Without luck
Can you perhaps help me out?

Thanks, The Doctor

"Norman Jones" wrote:

Hi Dr Schwartz,

1. The number of rows in the range (answer = 3)
Range("A5").End(xlDown).Row -Range("A5").Row + 1

2. The last row number in the range (answer =7)
Range("A5").End(xlDown).Row

---
Regards,
Norman



"Dr.Schwartz" wrote in message
...
Say I have values in cell A5, A6 and A7, what is the easiest way to
determine:
1. The number of rows in the range (answer = 3)
2. The last row number in the range (answer =7)

The first row number is known (5) as variable: fstRow

I can do it by counting every cell until I get to the first empty cell
with
a Do until...loop routine, but it seems like a lot of code to
determine a
simple thing. Agree?

Thank you
The Doctor







--

Dave Peterson