1

I have been trying to select Sheet2 from Sheet1 using a command button click macro. The final part of the macro is designed to find the last used row (offset by 1) and return the row number using MsgBox. I have tried two methods of selecting sheet2. However, in both cases the number returned by MsgBox is the last used row of sheet1, not of sheet2. I know this because when I change the last cell in column A that contains data (i.e. place data in a different cell), the MsgBox provides the new value (offset by 1).

I guess the error may be in my usage of Cells.Find. I know that there are several possible methods of determining the last used row but from what I’ve read the Cells.Find method is the most reliable. The only obvious thing to me is that the row number returned is from Sheet1, not Sheet2. There are no error messages associated with this failure.

The first method is:

Private Sub CommandButton1_Click()
    Set wSheet = Worksheets("Sheet2")
    wSheet.Activate
        With wSheet
           unusedRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Offset(1, 0).Row
        End With
    MsgBox (unusedRow)
End Sub

The second method is:

Private Sub CommandButton1_Click()
    Application.Goto Reference:=Worksheets("Sheet2").Range("A1"),      Scroll:=True
    unusedRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Offset(1, 0).Row
    MsgBox (unusedRow)
End Sub

Can anybody comment on any errors in my code? Any help would be greatly appreciated. I am very much a learner of XL VBA.

Edit: Response to comments

Thanks a lot @Dan; I have read both of those works you referenced. There is another useful discussion I found that I will dig out the link for and put here. I think I'm not yet tuned to the syntax at even a fairly fundamental level, so some of the more subtle variations are still a bit obscure. But I have got the point about avoiding select etc although I can only get there sometimes. I guess that's because I quite often use the macro recorder which tends to use the select family.

Many thanks @Tony - that's the piece of information I needed - the macro works fine now and I understand things a little better. Yes, I did run it with the Activate statement included and it gave me the last used row (offset by 1) for Sheet1; with your revision I get that row number for Sheet2. Thanks again.

Tony Dallimore
  • 12,335
  • 7
  • 32
  • 61
SaulNewZild
  • 11
  • 4
  • 3
    Siddharth Rout put together a great post on finding the last row here: http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba, which should help you solve this problem. Also, Chris Neilsen did a really great write up on not using select or activate here: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – Dan Wagner Jun 29 '14 at 04:25
  • I have tried the first routine and it works. However `unusedRow = Cells` must be `unusedRow = .Cells` if you want it controlled by the `With` statement. If you add the period before `Cells`, `wSheet.Activate` can be deleted. Have you tested this routine after adding `wSheet.Activate`? – Tony Dallimore Jun 29 '14 at 08:45

0 Answers0