you are welcome.
please mark the thread as solved from Thread Tools dropdown.
_________________________
quit excel
open a workbook with data.
make sure active cell in the active sheet with data is A1
turn macro recorder on
hit Ctrl + F key combination (or click Find & Select in Home Tab / Editing Group)
Find and Replace dialog pops up, "Find what" box containing an asterisk (*)
just click Find Next button
close the dialog
turn macro recorder off
now open VBE
recorded macro will show:
PHP Code:
Sub Macro1()
'
' Macro1 Macro
''
Cells.Find(What:="*", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub
now change xlNext to xlPrevious. and run the macro again. when you turn to excel you will see last non blank cell is selected.
Find Method Explained:
https://msdn.microsoft.com/en-us/lib.../ff839746.aspx
as you see this method has only one required parameter, which is "What".
so if you omit other optional parameters, default values are used. (Carefully read the Remarks in MS help page.)
and if you write the parameter values in function's/method's order, you don't need to write parameter names
so shorter version is:
PHP Code:
Sub Macro1()
'
' Macro1 Macro
'
'
Cells.Find("*", , , , xlByRows, xlPrevious).Activate
End Sub
you can get this cell's row number, column number, address, etc by using related properties.
PHP Code:
Sub FindLastNonBlankCellsProps()
Dim LastDataCell As Range
Dim LastRow As Long, LastCol As Long
Dim LastAddr As String
With ThisWorkbook.Worksheets("Sheet1")
Set LastDataCell = .Cells.Find("*", , , , xlByRows, xlPrevious)
End With
With LastDataCell
LastRow = .Row
LastCol = .Column
LastAddr = .Address
End With
MsgBox LastRow & vbLf & LastCol & vbLf & LastAddr
End Sub
or
PHP Code:
Sub FindLastNonBlankCellsProps()
Dim LastRow As Long, LastCol As Long
Dim LastAddr As String
With ThisWorkbook.Worksheets("Sheet1")
LastRow = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
LastCol = .Cells.Find("*", , , , xlByRows, xlPrevious).Column
LastAddr = .Cells.Find("*", , , , xlByRows, xlPrevious).Address
End With
MsgBox LastRow & vbLf & LastCol & vbLf & LastAddr
End Sub