Filtering with VB in Excel 1

[duncansancho]

Dawnsie asked this on another thread and I was waiting for a new thread so I could benefit too!
"Can anyone tell me where to find good examples on filtering using VB. I have a worksheet, where the user selects an item from a combobox and based on the selection, I need to filter the data, copy columns B, C and G whose records match the combox selection (representing column A) into a range on the worksheet. I am fairly new to the Excel/VB side of things and need some fast and serious help with this particular item. Thanks."

 

[xlbo]

In the combobox change event.....

lRow = sheets("Sheetname&quot.cells(65536,1).end(xlup).row
filterVar = combobox1.text
with sheets("Sheetname&quot
.range("A1:Z" & lRow).autofilter field:=1, criteria1:=filterVar

.range("B1:B" & lRow).copy destination:=sheets("Sheet2&quot.range("A1&quot
.range("C1:C" & lRow).copy destination:=sheets("Sheet2&quot.range("B1&quot
.range("G1:G" & lRow).copy destination:=sheets("Sheet2&quot.range("C1&quot
end with


This will copy the data in B,C and G to a seperate sheet (Sheet2) - change "Sheetname" to whatever your sheet with the data is called

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244