need to reference a cell from a changing range

[Louie]

In a worksheet each month results change the amount of rows in column 'B'.
Jan ends at B24, Feb at B32 and so on. From the last cell in column 'B' I
want to move one cell down and one cell to the left (Jan to A25, Feb to A33)
but I don't know the VBA code to do this.

 

[JLGWhiz]

First, get the last row number for column B.
Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row

Then to get the cell down 1 and left 1.

Set myRange = Cells(lastRow +1, 1) 'The cell in Col A

Put it in a procedure:

Sub getmyRange()
Dim lastRow As Long
lastRow = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Set myRange = Cells(lastRow +1, 1) 'The cell in Col A
MsgBox myRange.Address
End Sub

 

[mikerickson]

myCell.Offset(1,-1).Select