count rows

[phiore]

could someone please help me with some vba code.
i have a range of data on a worksheet. the location of the data, and the number of rows vary. i would like to run the macro, which would determine the row number of the beginning of the range, and the number of rows within the range, putting those two bits of data into another location, such as A1 and A2 respectively.
also, once this is done, what line can i add to a second macro, i guess utilizing an 'offset', that would reference these two locations respectively.
thanks for any help

[ This Message was edited by: phiore on 2002-05-23 06:50 ]

[dk]

Hi,

How about this?

Sub GetUsedRange()
Dim lngStartRow As Long, lngRowCount As Long

lngStartRow = ActiveSheet.UsedRange.Row
lngRowCount = ActiveSheet.UsedRange.Rows.Count

Range("A1").Value = lngStartRow
Range("A2").Value = lngRowCount

End Sub

Let me know if this is what you need.

Regards,
Dan

[phiore]

thanks dan, it works, but can we fine tune it please. if i have 2 ranges of 10 rows each with a blank line between, i would like it to give me back "10", not "21". also, can the start row be changed, so that it returns the row num of the location of the cursor. this way it would eliminate alot of chaff, such as headers. really appreciate all the help

[dk]

thanks dan, it works, but can we fine tune it please. if i have 2 ranges of 10 rows each with a blank line between, i would like it to give me back "10", not "21". also, can the start row be changed, so that it returns the row num of the location of the cursor. this way it would eliminate alot of chaff, such as headers. really appreciate all the help

Hi,

OK, this code used the CurrentRegion property based on the current cell selection.

Sub GetCurrentRegion()
Dim rnge As Range, lngRowcount As Long

Range("A1").Value = ActiveCell.Row
Range("A2").Value = ActiveCell.CurrentRegion.Rows.Count

End Sub

HTH,
Dan

[phiore]

thanks. works like a charm. appreciate your help