Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to determine what is causing large size | Excel Discussion (Misc queries) | |||
Determine size of listbox | Excel Discussion (Misc queries) | |||
determine range size after auto filter? | Excel Programming | |||
How to determine a range size | Excel Programming | |||
How to determine JPG size in pixels? | Excel Programming |