How do i select multiple, NON connected cells as a range with the offset function in VBA?

Question

I have a column of data (column P). The data contains names every 12 cells, and has other data in between. I need to select only the cells containing names. The entire range of names and data moves down as the report is filled which is why I need the offset function.Here is what I've come up with so far. It selects the range of the 2 cells and the data between. I want it to select only the 2 cells. There will be numerous others as well within column P. If it were just the 2, then id just set 2 variables. I need it to select (for example) cell P2, P14, P26, P38. But this will change quite often to other rows in column P.

Dim nameBank As Range
Dim P As Range

Set P = Cells(Rows.Count, "P").End(xlUp)

Set nameBank = Range(P.Offset(-11), P.Offset(-23))

Answer 1

• Range(Cell1, Cell2) returns a range object that encompasses the area between Cell1 and Cell2.
• Use Union method to combine two or more ranges object.

Microsoft documentation:

Application.Union method (Excel)

Application.Range property (Excel)

Sub Demo()
    Dim i As Long, nameBank As Range, LastCell As Range
    Set LastCell = Cells(Rows.Count, "LastCell").End(xlUp)
    Set nameBank = LastCell.Offset(-11)
    For i = 1 To 11
        If LastCell.Row - 11 - 12 * i > 0 Then _
        Set nameBank = Application.Union(nameBank, LastCell.Offset(-11 - 12 * i))
    Next
    Debug.Print nameBank.Address
    ' You code ...
End Sub

Answer 2

Reference Every Nth Cell

Usage

Sub Test()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!

    Dim rg As Range:
    Set rg = ws.Range("P2", ws.Cells(ws.Rows.Count, "P").End(xlUp))

    ' Names
    Dim nameBank As Range: Set nameBank = RefNthCellsInColumn(rg, 3)
    nameBank.Copy ws.Range("Q2")
    
    ' Numbers
    Dim numberBank As Range: Set numberBank = RefNthCellsInColumn(rg, 3, 2)
    numberBank.Copy ws.Range("R2")
    
    MsgBox nameBank.Cells.Count & " cells in range """ _
        & nameBank.Address(0, 0) & """.", vbInformation

End Sub

The Function

Function RefNthCellsInColumn( _
    ByVal singleColumnRange As Range, _
    ByVal Nth As Long, _
    Optional ByVal First As Long = 1) _
As Range
    Dim rg As Range, r As Long
    For r = First To singleColumnRange.Cells.Count Step Nth
        If rg Is Nothing Then
            Set rg = singleColumnRange.Cells(r)
        Else
            Set rg = Union(rg, singleColumnRange.Cells(r))
        End If
    Next r
    Set RefNthCellsInColumn = rg
End Function