Excel Macro to go to first cell in last row?

[Tivoli0]

Hi all,

I've created a macro via VBA that does the "Ctrl-End" keystrokes, i.e takes me to the Last Cell in a worksheet:

Sub MacroLastCell()

' Perform the Ctrl-End
x = ActiveSheet.UsedRange.Rows.Count
ActiveCell.SpecialCells(xlLastCell).Select

End Sub

My question:

How can I have it to go to the First non-blank cell in that last row?

Thanks in advance for any help!

tivoli0

 

[rmikesmith]

tivoli0,

The following should do what you want:

Sub MacroLastCell()
Dim x As Long
Dim rngLast As Range
Dim OneCell As Range
Dim rngFirstNonBlank As Range

' Perform the Ctrl-End
    x = ActiveSheet.UsedRange.Rows.Count

   Set rngLast = ActiveCell.SpecialCells(xlLastCell)
   For Each OneCell In Range(Cells(rngLast.row, 1), Cells(rngLast.row, rngLast.Column))
     If OneCell.Value <> "" Then
       Set rngFirstNonBlank = OneCell
       Exit For
     End If
   Next OneCell
   Debug.Print rngFirstNonBlank.Address

End Sub

The Debug.Print statement is just for illustration. Do whatever you want with rngFirstNonBlank.


Regards,
Mike

 

[MikeGeitner]

Also, adapted from faq707-2112

Sub FindUsedRange()
   
    Dim FirstRow As Long
    Dim LastCol As Long
    
        ' Find the FIRST real row
    FirstRow = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlNext, _
      SearchOrder:=xlByRows).Row
   
        ' Find the LAST real column
    LastCol = ActiveSheet.Cells.Find(What:="*", _
      SearchDirection:=xlPrevious, _
      SearchOrder:=xlByColumns).Column
        
   'Select the ACTUAL Used Range as identified by the
   'variables identified above
ActiveSheet.Cells(FirstRow, LastCol).Select

End Sub