excel range size

[Anonymous]

hello all

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

thanks
mark

[Anonymous]

Use the .UsedRange property of Excel.

Joe
--

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Is your print range too large?


--
R. Robert Bell, MCSE
www.AcadX.com


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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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