strange behaviour from SpecialCells(xlLastCell)

[obiron]

Hi guys , I have the folowing code in a workbook

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Dim cell As Range
    Dim DupAssetRows As String
    Dim NonNumvericValues As String
    Stop
    
    Sheets("Copied from scheme files").Select
    Range("A1").Select
    ActiveCell.SpecialCells(xlLastCell).Select
.....
End Sub

When I open this workbook manually, ActiveCell.SpecialCells(xlLastCell).address gives me a location of $AG$10849

When I open it using VBA code and then close it again the beforeClose event fires but the ActiveCell.SpecialCells(xlLastCell).address gives me $A$1 and the validation that occurs in the remainder of the procedure fails.

I'm going to change it to use UsedRange instead but wondered why I get different behaviour from the two opening methods

 

[Richard Schollar]

Hi

I wouldn't use either the SpecialCells method or the UsedRange property of a worksheet to determine where the last cell might be - I would use the Find method:

Dim rLastCell As Range
 
Set rLastCell = Sheets("Copied from scheme files").Cells.Find(What:="*",Lookin:=xlFormulas,LookAt:=xlWhole,SearchOrder:=xlByRows,SearchDirection:=xlPrevious)
 
'now you have a range reference to a cell in the last used row in the sheet - do something with it:
 
rLastCell.Activate

Note: usual caveats over you usually don't need to Select/Activate cells to use them applies.

 

[mikerickson]

In the BeforeClose event, ActiveCell might not be on that sheet.

 

[obiron]

Thanks for the tip Richard,

The problem appears to be that the select commands are not working (not my code - I don't use selects!!!)

I have now re-written the code to define two range variables and use those instead.