Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Filtering with VB in Excel 1

Status
Not open for further replies.

duncansancho

Technical User
Oct 9, 2003
45
GB
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."
 
In the combobox change event.....

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

.range("B1:B" & lRow).copy destination:=sheets("Sheet2").range("A1")
.range("C1:C" & lRow).copy destination:=sheets("Sheet2").range("B1")
.range("G1:G" & lRow).copy destination:=sheets("Sheet2").range("C1")
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top