Worksheet_Activate in excel macro not functioning unless I save the sheet

Question

I have the below code in my "Main" Sheet.

MsgBox "Code fired"

Sheets("Main").Range("H" & 2) =Sheets("RSP").Cells.SpecialCells(xlLastCell).Row

My intention is to get the max used cells from RSP sheet and put it in Main sheet.And this code is fine, it gives me the right count. But, the code works only when I save the workbook.

Ex: My main page is currently showing Count on RSP as 50, I go to RSP sheet and add a new line at the end. When I come back to Main page, the count increases to 51. Now, if I go to RSP and delete a raw, and then come back to main page, the count is not reducing. It still shows 51. The count changes if I save the workbook, switch to some other sheet and come back to Main sheet.

I tried putting the same code in both places, i.e Worksheet_activate of Main page, and WorkSheet_Deactivate of RSP page. But no difference. And, in all the cases the messagebox is working.

`SpecialCells(xlLastCell)` suffers from the same issues as `UsedRange`, well known to be problematic. Don't use it. There are a variety of other methods in answers here on on SO. Which one will suit you depends on your exact requirements. — chris neilsen, Jan 24 '15 at 06:44
@chrisneilsen - The issue about requiring to save the workbook to give the correct result applies to `SpecialCells(xlLastCell)` but not to `UsedRange`. See my answer below, and also http://stackoverflow.com/a/27637752/2707864 for even additional info. — sancho.s ReinstateMonicaCellio, Jan 24 '15 at 11:53
Yes, the code is changed as below, and it works on every activesheet.deactivate() Sheets("Main").Range("I" & 2) = (Range("I" & Rows.Count).End(xlUp).Row - 1) — Justin, Feb 16 '15 at 13:35

Davesexcel · Answer 1 · 2015-01-24T10:59:51.913

Do you want to find any last row or the last row from a specified column? Here are both options.

  Sub FindAnyLstRow()
    Dim ws As Worksheet, MyRng As Range
    Dim sh As Worksheet
    Dim Rws As Long, r As Range, fRng As Range

    Set ws = Worksheets("Main")
    Set MyRng = ws.Range("H2")
    Set sh = Worksheets("RSP")

    With sh
        Set r = .Range("A1")
        Rws = .Cells.Find(what:="*", after:=r, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    End With

    MyRng = Rws

End Sub


And 

    Sub GetLastCellInColumnA()

    Dim ws As Worksheet, MyRng As Range
    Dim sh As Worksheet
    Dim Rws As Long, fRng As Range

    Set ws = Worksheets("Main")
    Set MyRng = ws.Range("H2")
    Set sh = Worksheets("RSP")

    With sh
        Rws = .Cells(Rows.Count, "A").End(xlUp).Row
        Set fRng = .Cells(Rws, 1)
    End With

    MyRng = fRng

End Sub

score 0 · Accepted Answer · edited May 23 '17 at 12:04

This may work for you.

Sheets("Main").Range("H2") = Sheets("RSP").Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).Row

Now, the explanation. Please read this entirely, if you want to know about it.

When trying to find the last cell (or row, column) in a worksheet, you have first to decide what do you mean by that.

Used = non-blank, i.e., having data.
Used = "... in use, meaning the section that contains data or formatting." This is the criterion used by Excel at the time of saving. See also this. This criterion usually produces unexpected results, but it may also be intentionally exploited (less often, surely), e.g., to highlight or print specific regions, which may eventually have no data. And, of course, it is desirable as a criterion for the range to use when saving a workbook.

Often, the two criteria give the same result for the last cell, which seems to be your case. This was covered here and here.

For criterion 1, three of the many methods to find the last used cell are:

UsedRange.
Ctrl+End.
SpecialCells(xlLastCell).

Methods 2 and 3 always update their target "last cell" when adding (either data or format) past the previous last cell. But they do not update their target "last cell" until you save the workbook when deleting (both data and format) from the previous last cell. Method 1 works without having to save the workbook.

For crtierion 2, there are also many methods:

.Find("*", ... (as used above). Please check this or this.
Etc.

(Most of?) These methods will work even without saving the workbook.

Worksheet_Activate in excel macro not functioning unless I save the sheet

2 Answers2