Setting a range for a listbox

[AlexanderBB]

Someone told me a range did not have to be consecutive columns, and I'm pretty sure I confirmed that.
However when I set a range for a 2 column listbox :-

lstPrefix.List = .Range(cCol & First & ":" & cCol & Last, "L" & First & ":" & "L" & Last).Value

This first col is correctly CCol but the other is not "L" but the adjacent column to the right of cCol
Can I resolve this ? Maybe a named range? If yes, I'll look further into that.
Thanks.

 

[DanteAmor]

I propose 2 options:

A) Load the 12 columns from A to L and hide the columns you don't need.

  Dim col As Long
  
    col = .Range(cCol & 1).Column
    lstPrefix.ColumnWidths = WorksheetFunction.Replace("0;0;0;0;0;0;0;0;0;0;0;", col * 2 - 1, 1, "")
    lstPrefix.List = .Range("A" & first & ":" & "L" & last).Value

B) Load all the records in the 2 columns into an array and then pass the array to the listbox

  Dim a As Variant
  Dim i As Long, k As Long
  ReDim a(1 To last, 1 To 2)
  
    For i = first To last
      k = k + 1
      a(k, 1) = .Range(cCol & i)
      a(k, 2) = .Range("L" & i)
    Next

    lstPrefix.List = a