How To: Get Last Cell
 

Using those special cells is a bit like using the F5
(GoTo) key, and thus fires the selection change event.

If you want the last cell in a column
Set rgeEnd = Range("A1").End(xlDown)
OR
Set rgeEnd = Range("A65000").End(xlUp)
which I prefer as it means we don't worry about gaps in
the column.

If you must use the special cells then try this

Application.EnableEvents = False

Set rgeEnd = Cells.SpecialCells(xlCellTypeLastCell)

Application.EnableEvents = True

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi,
I currently use Set rgeEnd = xlsheet.Cells.SpecialCells
(xlCellTypeLastCell) but for some reason the event
SheetSelectionChange triggers so how do I get the range
of
the last cell (bottom right cell) in a sheet without
using
the SpecialCells xlCellTypeLastCell.

regards
KM
.

How To: Get Last Cell
 

Hi,
I solved the problem by checking to see if the form was
visibl or not eg. if frmAddEditRecord.Visible Then Exit Sub

but I do call the same xlSpecial cells to show a print
preview so I've used your suggestion and placed
Application.EnableEvents = False or true around the set
rgeEnd.

Thanks again
regards
KM

-----Original Message-----
Using those special cells is a bit like using the F5
(GoTo) key, and thus fires the selection change event.

If you want the last cell in a column
Set rgeEnd = Range("A1").End(xlDown)
OR
Set rgeEnd = Range("A65000").End(xlUp)
which I prefer as it means we don't worry about gaps in
the column.

If you must use the special cells then try this

Application.EnableEvents = False

Set rgeEnd = Cells.SpecialCells(xlCellTypeLastCell)

Application.EnableEvents = True

Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi,
I currently use Set rgeEnd = xlsheet.Cells.SpecialCells
(xlCellTypeLastCell) but for some reason the event
SheetSelectionChange triggers so how do I get the range
of
the last cell (bottom right cell) in a sheet without
using
the SpecialCells xlCellTypeLastCell.

regards
KM
.

.