I have a spreadsheet with stuff in various cells but my main area of interest
is A11 to I(where ever it ends)

For some reason when I select using this
Range("A11").CurrentRegion.Select

I get header cells and other unwanted stuff, it actually selects A8 to I
(where ever it ends)

How do I select only A11 to I(where ever it ends) regardless of what is
around it?

Thanks
Mike

xlLastCell will give you the last cell on the spreadsheet. If you want
to know exactly which one that is, open the spreadsheet and hit ctrl +
end.

So something like... range("A11", xlLastCell).Select should work.

Is this what you were looking for?

paqo

Hi

CurrentRegion does just that (by definition), it returns continuous cells by
looking in the following direction: up, down, and diagonal.
In you case, you would use:
Dim rg as range
rg= range("A11")

'to select the last cell before the next blank, use:
Range(rg, rg.end(xlright)).select
'but then, say there is a blank in F11, but data in G11, it will
stop in E11:
' right before the next blank(F11)

'to select up to the last cell of data in row 11:
Range(rg, rg.EntireRow.Cells(256).End(xlToLeft)).Select
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Mike" wrote:

I have a spreadsheet with stuff in various cells but my main area of interest
is A11 to I(where ever it ends)

For some reason when I select using this
Range("A11").CurrentRegion.Select

I get header cells and other unwanted stuff, it actually selects A8 to I
(where ever it ends)

How do I select only A11 to I(where ever it ends) regardless of what is
around it?

Thanks
Mike

one precision: my previous post was for the case on searching end of data on
a row not column ('cause you were saying A8 to I, and i thought 'I' was
representing the column I).
For searching for up to the last cell in the column A, use:
Dim rg as range
rg= range("A11")

'to select up to the last cell of data in column A:
Range(rg,
rg.EntireColumn.Cells(rg.EntireColumn.Cells.count) .End(xlUp)).Select

'to select the last cell before the next blank, use:
Range(rg, rg.end(xlUp)).select

--
Regards,
Sébastien
<http://www.ondemandanalysis.com

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"sebastienm" wrote:

Hi

CurrentRegion does just that (by definition), it returns continuous cells by
looking in the following direction: up, down, and diagonal.
In you case, you would use:
Dim rg as range
rg= range("A11")

'to select the last cell before the next blank, use:
Range(rg, rg.end(xlright)).select
'but then, say there is a blank in F11, but data in G11, it will
stop in E11:
' right before the next blank(F11)

'to select up to the last cell of data in row 11:
Range(rg, rg.EntireRow.Cells(256).End(xlToLeft)).Select
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Mike" wrote:

I have a spreadsheet with stuff in various cells but my main area of interest
is A11 to I(where ever it ends)

For some reason when I select using this
Range("A11").CurrentRegion.Select

I get header cells and other unwanted stuff, it actually selects A8 to I
(where ever it ends)

How do I select only A11 to I(where ever it ends) regardless of what is
around it?

Thanks
Mike

In this case it is safest to come up from the bottom of the sheet. xlLastCell
and CurrentRegion will give you problems... Try this

range("A11", cells(rows.count, "A").end(xlUp)).select
--
HTH...

Jim Thomlinson


"Mike" wrote:

I have a spreadsheet with stuff in various cells but my main area of interest
is A11 to I(where ever it ends)

For some reason when I select using this
Range("A11").CurrentRegion.Select

I get header cells and other unwanted stuff, it actually selects A8 to I
(where ever it ends)

How do I select only A11 to I(where ever it ends) regardless of what is
around it?

Thanks
Mike

Can't you select explicitly:

Range("A11:I100").Select

or

i = 50
Range(Cells(11, 1), Cells(i, 9)).Select

if the depth of column I is variable
--
Gary''s Student


"Mike" wrote:

I have a spreadsheet with stuff in various cells but my main area of interest
is A11 to I(where ever it ends)

For some reason when I select using this
Range("A11").CurrentRegion.Select

I get header cells and other unwanted stuff, it actually selects A8 to I
(where ever it ends)

How do I select only A11 to I(where ever it ends) regardless of what is
around it?

Thanks
Mike