strange result for SpecialCells

  • Thread starter Thread starter Rainer Bielefeld
  • Start date Start date
R

Rainer Bielefeld

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(xlCellTypeLastCell).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
 
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
 
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.
 
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.
 
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
 
with worksheets(1).range("A1").currentregion
msgbox .cells(.cells.count).address
end with
 
Back
Top