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]

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.

OH, and if this is a goose chase or over-thinking it, then I'll just bail out and filter the columns in sheet("Export Here") using code, and then populate with the filtered results. Thanks again.

 

[cmancu]

OH, and if this is a goose chase or over-thinking it, then I'll just bail out and filter the columns in sheet("Export Here") using code, and then populate with the filtered results. Thanks again.

Also, typo in my original post. Looking for column N data (col14). Sorry for the confusing error!

 

[Fluff]

How about

   Dim Ary As Variant
   With Workbooks("RoomCardList.xlsx").Sheets("Export Here")
      With .Range("N2", .Range("N" & rows.Count).End(xlUp))
         Ary = Evaluate("filter(" & .Address & "," & .Offset(, -13).Address & "=""DC"")")
         If Not IsError(Ary) Then Me.ListBox1.List = Ary
      End With
   End With

 

[cmancu]

As usual, succinct and spot on! Bartender, get Fluff another!
THANK YOU.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[cmancu]

You're welcome & thanks for the feedback.

Ahhhh...shoot. Just realized I will likely need a 2 column listbox. If "DC" appears in col "A", populate listbox from col M AND col N.
I realize that seems to mess with your efficient array setup. Any chance of reworking your code?
Thanks.

You're welcome & thanks for the feedback.

 

[Fluff]

Ok, how about

   Dim Ary As Variant
   With Workbooks("RoomCardList.xlsx").Sheets("Export Here")
      With .Range("M2:N" & .Range("N" & Rows.Count).End(xlUp).Row)
         Ary = .Worksheet.Evaluate("filter(" & .Address & "," & .Columns(1).Offset(, -12).Address & "=""DC"")")
         If Not IsError(Ary) Then Me.ListBox1.List = Ary
      End With
   End With

 

[cmancu]

Ok, how about

   Dim Ary As Variant
   With Workbooks("RoomCardList.xlsx").Sheets("Export Here")
      With .Range("M2:N" & .Range("N" & Rows.Count).End(xlUp).Row)
         Ary = .Worksheet.Evaluate("filter(" & .Address & "," & .Columns(1).Offset(, -12).Address & "=""DC"")")
         If Not IsError(Ary) Then Me.ListBox1.List = Ary
      End With
   End With

BINGO! Thanks Fluff.
I'm sure I'll send out the "VBA signal" for help again soon.

 

[Fluff]

My pleasure.