range of non-adjacent cells

[Sahak]

Hi all,

Please help me with macro code to return the row numbers of the first and last rows For a dynamic range of non-adjacent cells (for example for range E8:E21,E26:E33 should return 8 & 33)

Thank you in advance

 

[pgc01]

Hi

What would be the last row in the range E8:E21,E26:E33,H4:J20?

Would it be 20 (the last row in the last area) or 33 (the largest last row in any area)?

 

[Sahak]

Thank you very much for reply.

Last row in the range E8:E21,E26:E33,H4:J20 should be 33 (the largest last row in any area)

 

[brettdj]

hth

Dave

Sub LastRow()
    Dim rng1 As Range
    Dim rngArea As Range
    Dim lngRow As Long
    Set rng1 = Range("E8:E21,E26:E33,H4:J20")
    For Each rngArea In rng1.Areas
    lngRow = Application.Max(rngArea.Cells(1).Offset(rngArea.Rows.Count - 1, 1).Row, lngRow)
    Next rngArea
    MsgBox "Maximum row is " & lngRow
End Sub

 

[Sahak]

Thank you Dave VERY much.

 

[brettdj]

No problem, glad to have helped.