VBA Populate listbox with column M data based on column A value

[cmancu]

Evening all. 365 on Windows. I visit occasionally when puzzling with my VBA addiction. Need help finishing code to populate listbox2 in userform1. If column A contains text "DC", additem from column M data. Column M is dynamic. From older posts searched I have a start...

Private Sub Userform_Initialize()
    Dim rng As Range
  
    With Workbooks("RoomCardList.xlsx").Sheets("Export Here")
       Set rng = .Range(.Cells(1, 14), .Cells(.Rows.Count, 14).End(xlUp))
    End With
        With Me.ListBox2
                .RowSource = rng.Address
        End With

End Sub

...which works fine for ALL of column M, but of course I only want column M if column A contains "DC". I was tinkering with something like:

'rowsCount = ws.Cells(ws.Rows.Count, 14).End(xlUp).Row

'For i = 0 To rowsCount
'    If ws.Cells(i, 1).Value = "DC" Then
'        ListBox2.AddItem (ws.Cells(i, 14).Value)
'    End If
'Next i

Seemed like it would be easy to get there, but after 2 days of trying different guesses, I fear I'm just not getting there without help. Thank you to any takers.

 

[cmancu]

My pleasure.

Sorry, hate to revisit this.....CODE WORKS PERFECTLY, except when there is only 1 instance of "DC" in col A. I have ColumnCount in listbox set at 2, which works great when there is more than 1 row of data in array. But when array only sees 1 row in col M:N, listbox populates itself as only a 1 column box with col M data over col N data. Alpha numeric are in col N, names in col M.
Resulting listbox with 6 rows of "DC" in col A next to one with 1 row of "DC":

Thanks if you care to look once more!

 

[Fluff]

Ok, how about

   Dim Ary As Variant
   Dim x As Long
   
   With Workbooks("RoomCardList.xlsx").Sheets("Export Here")
      With .Range("M2:N" & .Range("N" & Rows.Count).End(xlUp).Row)
         x = Application.CountIfs(.Columns(1).Offset(, -12), "DC")
         If x = 0 Then
            MsgBox "no data"
            Exit Sub
         ElseIf x = 1 Then
            Ary = .Columns(1).Offset(, -12).Find("DC", , , xlWhole, , , False, , False).Offset(, 12).Resize(, 2).Value
         Else
            Ary = .Worksheet.Evaluate("filter(" & .Address & "," & .Columns(1).Offset(, -12).Address & "=""DC"")")
        End If
        Me.ListBox1.List = Ary
      End With
   End With

 

[cmancu]

Many thanks again. Code works perfectly.

 

[Fluff]

Glad to help & thanks for the feedback.