Creating a named range of non-contiguous cells

[NinaE_11]

Hello,

Can anyone give me advice on how to include 7 non-contiguous rows past my last row? I've tried .offset, but it's clear I'm not using it in the right place. I've gotten this far and stuck because the Offset command ends up moving down my range to start in cell A8.

Private Sub NamePERange()

Dim wsDest As Worksheet
Dim lr As Long
Dim MyRange As Range

Set wsDest = Workbooks("PE Table_SC.xlsm").Worksheets(1)
wsDest.Activate

'1. Find last used row in the copy range based on data in column A
lr = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Row

'2. Reset, Select Range and Name it 'PE'

Set MyRange = wsDest.Range("A1:L" & lr).offset(7)
ActiveWorkbook.Names.Add _
Name:="PE", _
RefersTo:=MyRange

End Sub

Any insight would be much appreciated - thank you!

 

[StephenCrump]

I'm not sure what you mean by non-contiguous cells/rows in this context?

But I think you're looking for

Set MyRange = wsDest.Range("A1:L" & lr + 7)

 

[NinaE_11]

Thank you so much, StephenCrump! Worked perfectly.