topic Re: in VBA Forum

excel range size

Anonymous — Thu, 02 Jan 2003 10:53:01 GMT

hello all

how to detect the maximum used
range in a excel spreadsheet, or
the max column and the max row

thanks
mark

Re: excel range size

Anonymous — Thu, 02 Jan 2003 11:37:09 GMT

Use the .UsedRange property of Excel.

Joe
--

Re: excel range size

Anonymous — Thu, 02 Jan 2003 11:37:24 GMT

I got this from Gary McMaster a while back. It works great.

Dim iMaxRow As Integer
Dim iMaxCol As Integer
Dim oXLUsed As Range

Set oXLUsed = oXLSheet.UsedRange 'The entire used area of the sheet

iMaxCol = oXLUsed.Columns(oXLUsed.Columns.Count).column 'Highest column number of the
"Used range"
iMaxRow = oXLUsed.Rows(oXLUsed.Rows.Count).Row 'Highest row number of the "Used
range"

--
Kent
Member of the Autodesk Discussion Forum Moderator Program

"Mark" wrote in message
news:9CC5C29235AD79BE64DA43156C795873@in.WebX.maYIadrTaRb...
> hello all
>
> how to detect the maximum used
> range in a excel spreadsheet, or
> the max column and the max row
>
> thanks
> mark
>
>

Re:

Anonymous — Thu, 02 Jan 2003 11:48:32 GMT

thanks

but tis method is giving me about 100 more rows,
i am not able to figure out why

mark

"Kent Keller" wrote in message
news:02B877F5A2873848C9364359F041EF10@in.WebX.maYIadrTaRb...
> I got this from Gary McMaster a while back. It works great.
>
> Dim iMaxRow As Integer
> Dim iMaxCol As Integer
> Dim oXLUsed As Range
>
> Set oXLUsed = oXLSheet.UsedRange 'The entire used area of the sheet
>
> iMaxCol = oXLUsed.Columns(oXLUsed.Columns.Count).column 'Highest column
number of the
> "Used range"
> iMaxRow = oXLUsed.Rows(oXLUsed.Rows.Count).Row 'Highest row
number of the "Used
> range"
>
> --
> Kent
> Member of the Autodesk Discussion Forum Moderator Program
>
>
> "Mark" wrote in message
> news:9CC5C29235AD79BE64DA43156C795873@in.WebX.maYIadrTaRb...
> > hello all
> >
> > how to detect the maximum used
> > range in a excel spreadsheet, or
> > the max column and the max row
> >
> > thanks
> > mark
> >
> >
>
>

Re:

Anonymous — Thu, 02 Jan 2003 11:54:14 GMT

Re:

Anonymous — Thu, 02 Jan 2003 12:02:20 GMT

I have found that if you delete rows by highlighting them and then using the delete Key
instead of going to the edit menu and using delete that it still thinks they are being
used at least in Excel 97. Could that possibly be the issue?

--
Kent
Member of the Autodesk Discussion Forum Moderator Program

"Mark" wrote in message
news:D9EA54E8FAD6D0ED6F58A513A2587F44@in.WebX.maYIadrTaRb...
>
>
> thanks
>
> but tis method is giving me about 100 more rows,
> i am not able to figure out why
>
> mark

Re:

Anonymous — Thu, 02 Jan 2003 12:29:14 GMT

Excel doesn't reset its UsedRange until you close and re-open the file.
Until then there's extra rows if you deleted data, changed formatting, etc.

You can use the following from
http://www.microsoftexceltraining.com/VBA/ExcelRanges.htm. Check out the
website for related hints:

'Find the very last used cell in a Column:
Range("A65536").End(xlup).Select

It's like going to the bottom of the column and hitting CTRL-UP.

James

>
> but tis method is giving me about 100 more rows,
>

Re:

Anonymous — Thu, 02 Jan 2003 13:30:25 GMT

thanks to all
i had to delete contents in the range
below my last row, then save,
and it works fine now
mark

"James Belshan" wrote in message
news:5F13B853F8FD5FCD33D70C6C81D2D755@in.WebX.maYIadrTaRb...
> Excel doesn't reset its UsedRange until you close and re-open the file.
> Until then there's extra rows if you deleted data, changed formatting,
etc.
>
> You can use the following from
> http://www.microsoftexceltraining.com/VBA/ExcelRanges.htm. Check out the
> website for related hints:
>
> 'Find the very last used cell in a Column:
> Range("A65536").End(xlup).Select
>
> It's like going to the bottom of the column and hitting CTRL-UP.
>
> James
>
> >
> > but tis method is giving me about 100 more rows,
> >
>
>