PDA

View Full Version : Add multiple filtered row data to ListBox



onmyway
02-03-2015, 04:51 AM
Hi guys,

I am a bit stuck, and hope you will help me.

I am trying to add a range of filtered data to a ListBox. Currently, my method adds the first filtered row data, but not any additional rows if there are multiple rows in my filtered data.

Detail:

I have a search function that filters my data based on column 1.
When you dbl click on the suggested search matches in ListBox3, it updates the UserForm with the filtered row data from Sheet1. It also updates ListBox1 with the data in range E2- I2.

HOWEVER: I would like to update ListBox1 with all the data of how many rows there might be in my active filter for the range E2+ - I2+

I named the range: RecordData - this might be an option?

Perhaps some type of Loop to this code:


With ListBox1
.ColumnCount = 6
.AddItem

.Column(0, 0) = Sheets("Sheet1").Cells(FirstRow, 5).Value
.Column(1, 0) = Sheets("Sheet1").Cells(FirstRow, 6).Value
.Column(2, 0) = Sheets("Sheet1").Cells(FirstRow, 5).Value
.Column(3, 0) = Sheets("Sheet1").Cells(FirstRow, 6).Value
.Column(4, 0) = Sheets("Sheet1").Cells(FirstRow, 5).Value
.Column(5, 0) = Sheets("Sheet1").Cells(FirstRow, 6).Value

End With

i have attached my sample workbook.

Thank you VERY much!

snb
02-03-2015, 08:02 AM
Basically (mutatis mutandis):


Sub M_snb()
with sheet1.cells(1).currentregion
.autofilter 1, "filtertext"
.offset(1).copy sheet1.cells(1,100)
.autofilter
end with

Listbox1.List=sheet1.cells(1,100).currentregion.value
Listbox1.columncount=ubound(listbox1.list,2)+1
sheet1.cells(1,100).currentregion.clearcontents
End Sub

onmyway
02-03-2015, 08:20 AM
hi snb

Thanks for looking at this for me. It really has me stumped.

Where do I put the code?

locus ubi codicem

onmyway
02-04-2015, 02:18 AM
Hi all,

Almost solved! Here the latest workbook and post:

http://www.vbaexpress.com/forum/showthread.php?51692-Code-help-Copy-only-filtered-range-to-ListBox (http://www.vbaexpress.com/forum/showthread.php?51692-Code-help-Copy-only-filtered-range-to-ListBox)