CODEWITHSUNDEEP
excelvba
Yusuf Rahmaniac
Yusuf Rahmaniac

Reputation: 21

how to get last row that has velue Excel VBA within a range

I am new to VBA Macro. i just want to know how to get the last row that has value within a range

 Set MyRange = Worksheets(strSheet).Range(strColumn & "1")
GetLastRow = Cells(Rows.Count, MyRange.Column).End(xlUp).Row

this code could find the last row for the whole sheet.. i just want it to find the last non null value cells ( like in this case in the picture.. for the ("A8") range, the last row result should be ("A9:B9")

enter image description here

Upvotes: 0

Views: 340

Answers (2)

FaneDuru
FaneDuru

Reputation: 42256

"A9:B9" cannot be last row... It is a range.

If you need such a range, but based on the last empty row, starting from a specific cell, you can use the next approach:

Sub testLastRowRange()
   Dim sh As Worksheet, myRange As Range, lastRow As Long, strColumn As String
   Dim lastCol As Long, endingRowRng As Range, strSheet As String
   
   strSheet = ActiveSheet.Name 'please, use here your real sheet name
   Set sh = Worksheets(strSheet)
   strColumn = "A"
   Set myRange = sh.Range(strColumn & 8)
   lastRow = myRange.End(xlDown).row
   lastCol = myRange.End(xlToRight).Column
   Set endingRowRng = sh.Range(sh.Cells(lastRow, myRange.Column), sh.Cells(lastRow, lastCol))
   Debug.Print endingRowRng.address
End Sub

Upvotes: 2

SJR
SJR

Reputation: 23081

For your specific example you could use CurrentRegion property.

This is based on the ActiveCell which is not generally advisable.

Sub x()

Dim r As Range

Set r = ActiveCell.CurrentRegion

MsgBox r.Address

End Sub

Upvotes: 0

Related Questions