strange result for SpecialCells
 

Hi,

I'm getting a strange result for SpecialCells(xlCellTypeLastCell):

Debug.Print Worksheets(1).Cells(1, 1).CurrentRegion.Address

has as result: $A$1:$J$10

Debug.Print Worksheets(1).Cells(1, 1).CurrentRegion.Cells.SpecialCells(xlCellTypeLast Cell).Address

has as result: $J$19

Ok, I've defined a Name for Range $A$1:$J$19, but nevertheless the result
should be $J$10, shouldn't it?

Regards,

Rainer

strange result for SpecialCells
 

Hi Rainer,

I have found both CurrentRegion and UsedRange a bit of a pain. With your
code you only have to select the rows below row 10 (as many as you like) and
then use Clear All and reselect A1 and then run your code and the last row
returned is the last one selected before the Clear All.

If you run some UsedRange code for the last cell and then run your code,
then your code returns the correct result. (Don't know why).

In the following examples I have included alternative ways of returning the
CurrentRegion Address or the CurrentRegion Last Cell address. They seem to
work OK in both xl2002 and xl2007 but I have not tested to the nth degree.
Both versions of xl have the same problems with incorrect address with your
code.


Sub test2()

'Returns Last Cell address of CurrentRegion.
Debug.Print Worksheets(1) _
.Cells(Cells(1, 1) _
.CurrentRegion.Rows.Count, _
Cells(1, 1).CurrentRegion _
.Columns.Count).Address

'Returns CurrentRegion Address
Debug.Print Worksheets(1) _
.Range(Cells.CurrentRegion.Cells(1, 1), _
Cells(Cells.CurrentRegion.Rows.Count, _
Cells(1, 1).CurrentRegion.Columns.Count)) _
.Address

'Your code
Debug.Print Worksheets(1).Cells(1, 1) _
.CurrentRegion.Cells _
.SpecialCells(xlCellTypeLastCell) _
.Address
End Sub

By running the following code before running your code it appears to fix the
problem with your code and will return the correct result. (Note my UsedRange
and CurrentRegion were the same.

Sub test1()
'Running this before CurrentRegion code
'seems to fix the CurrentRegion code problems.
Debug.Print ActiveSheet.UsedRange.Cells _
.SpecialCells(xlCellTypeLastCell) _
.Address
End Sub


--
Regards,

OssieMac

strange result for SpecialCells
 

Forgot to say the the code I posted for 'Returns CurrentRegion Address was
only a play example/demo. You would nomally use the first snippet of code
that you posted to do that.

--
Regards,

OssieMac

strange result for SpecialCells
 

Hi again,

Have just found this in Help.

xlCellTypeLastCell. The last cell in the used range

It does not say it applies to CurrentRegion so that is possibly also part of
the problem.

--
Regards,

OssieMac

strange result for SpecialCells
 

Hi OssieMac,

xlCellTypeLastCell. The last cell in the used range

It does not say it applies to CurrentRegion so that is possibly also part of
the problem.

yes, I think that's the reason - not very intuitively.

Well, I've already written my own function to return the last cell for a
given range.

Regards,

Rainer


"OssieMac" schrieb im Newsbeitrag ...
Hi again,

Have just found this in Help.

xlCellTypeLastCell. The last cell in the used range

It does not say it applies to CurrentRegion so that is possibly also part of
the problem.

--
Regards,

OssieMac

strange result for SpecialCells
 

with worksheets(1).range("A1").currentregion
msgbox .cells(.cells.count).address
end with

Rainer Bielefeld wrote:

Hi,

I'm getting a strange result for SpecialCells(xlCellTypeLastCell):

Debug.Print Worksheets(1).Cells(1, 1).CurrentRegion.Address

has as result: $A$1:$J$10

Debug.Print Worksheets(1).Cells(1, 1).CurrentRegion.Cells.SpecialCells(xlCellTypeLast Cell).Address

has as result: $J$19

Ok, I've defined a Name for Range $A$1:$J$19, but nevertheless the result
should be $J$10, shouldn't it?

Regards,

Rainer

--

Dave Peterson