usedrange function
 

I'm just starting to work with functions in VBA. Does something like
this look ok to use when getting the first and last rows of the used
range? I'm wondering if there's a better or more efficient way of
doing this.

-----
Sub test()
a = rangeUsed(0)
b = rangeUsed(1)

MsgBox "first row = " & a & vbLf & "last row = " & b
End Sub


Function rangeUsed()
Dim firstRow As Long, lastRow As Long

With ActiveSheet.UsedRange.Rows
firstRow = .Row
lastRow = .Count + (firstRow - 1)
End With

rangeUsed = Array(firstRow, lastRow)
End Function
-----

Thanks.

Jay


--
jayy
------------------------------------------------------------------------
jayy's Profile: http://www.excelforum.com/member.php...o&userid=33975
View this thread: http://www.excelforum.com/showthread...hreadid=538563

usedrange function
 

If you want to know the first an last row of the rectangular area that Excel
is maintaining information on (irrespective of wether there are values in
those cells), then yes, I think that should work.

--
Regards,
Tom Ogilvy


"jayy" wrote:


I'm just starting to work with functions in VBA. Does something like
this look ok to use when getting the first and last rows of the used
range? I'm wondering if there's a better or more efficient way of
doing this.

-----
Sub test()
a = rangeUsed(0)
b = rangeUsed(1)

MsgBox "first row = " & a & vbLf & "last row = " & b
End Sub


Function rangeUsed()
Dim firstRow As Long, lastRow As Long

With ActiveSheet.UsedRange.Rows
firstRow = .Row
lastRow = .Count + (firstRow - 1)
End With

rangeUsed = Array(firstRow, lastRow)
End Function
-----

Thanks.

Jay


--
jayy
------------------------------------------------------------------------
jayy's Profile: http://www.excelforum.com/member.php...o&userid=33975
View this thread: http://www.excelforum.com/showthread...hreadid=538563

usedrange function
 

Hi Tom,

Thanks very much for your reply. Out of curiosity, if I did only wan
rows that have text is the best way to loop through each row and chec
for rows that aren't empty?

Thanks.

Ja

--
jay
-----------------------------------------------------------------------
jayy's Profile: http://www.excelforum.com/member.php...fo&userid=3397
View this thread: http://www.excelforum.com/showthread.php?threadid=53856

usedrange function
 

http://www.beyondtechnology.com/geeks012.shtml

--
Regards,
Tom Ogilvy

"jayy" wrote in message
...

Hi Tom,

Thanks very much for your reply. Out of curiosity, if I did only want
rows that have text is the best way to loop through each row and check
for rows that aren't empty?

Thanks.

Jay


--
jayy
------------------------------------------------------------------------
jayy's Profile:
http://www.excelforum.com/member.php...o&userid=33975
View this thread: http://www.excelforum.com/showthread...hreadid=538563

usedrange function
 

Hi Tom,

Thanks so much for the info. I really appreciate it!

Jay


--
jayy
------------------------------------------------------------------------
jayy's Profile: http://www.excelforum.com/member.php...o&userid=33975
View this thread: http://www.excelforum.com/showthread...hreadid=538563