Populate listbox

[JCHuysamer]

Hi all
Can anybody please give me a slap to wake up my brain

I have a userform with a drop down menu(cboCOW) (the drop down menu is to filter data on a workdheet - Sheets("VMC").
Once the data is filtered I need it to populate a Listbox ("lstBxCOW") showing only the filtered data.

The listbox must have 7 Columns displaying columns B, C, H, O, X, AF & AN of the worksheet - Sheets("VMC").

Once the data is filtered and displayed in the listbox it needs to populate/calculate the totals of
column X and displayed in a textbox named txtTM (only the visible filtered data)
column AF and displayed in a textbox named txtTF (only the visible filtered data)
column AN and displayed in a textbox named txtTH (only the visible filtered data)

 

[Worf]

Hi

See if this helps:

Private Sub CommandButton1_Click()
Dim sh As Worksheet, cn, i%, lastr%
cn = Array(2, 3, 8, 15, 24, 32, 40)                                                 ' column numbers
Set sh = Sheets("VMC")
sh.[ar:cm].ClearContents
sh.[a:an].AdvancedFilter xlFilterCopy, sh.[ap1:ap2], sh.[ar1], False                ' filter to other location
lastr = [ar:ce].Find("*", [ar:ce].Cells(1), xlFormulas, _
xlPart, xlByRows, xlPrevious, False).Row                                            ' last used row
For i = LBound(cn) To UBound(cn)                                                    ' copy to contiguous range
    sh.Range(Cells(1, cn(i) + 43), Cells(lastr, cn(i) + 43)).Copy sh.Cells(1, 85 + i)
Next
Me.ListBox1.ColumnCount = 7
Me.ListBox1.RowSource = sh.Range(Cells(1, 85), Cells(lastr, 91)).Address
Me.txtTM = WorksheetFunction.Sum(sh.Range(Cells(2, "ck"), Cells(lastr, "ck")))
Me.txtTF = WorksheetFunction.Sum(sh.Range(Cells(2, "cl"), Cells(lastr, "cl")))
Me.txtTH = WorksheetFunction.Sum(sh.Range(Cells(2, "cm"), Cells(lastr, "cm")))
End Sub