usedrange function

[jayy]

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

[Tom Ogilvy]

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
>
>

[jayy]

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

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

"jayy" <jayy.278xfa_1146684001.9842@excelforum-nospam.com> wrote in message
news:jayy.278xfa_1146684001.9842@excelforum-nospam.com...
>
> 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
>

[jayy]

Hi Tom,

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

Jay