Code help: Copy only filtered range to ListBox

**onmyway** · 02-04-2015, 05:15 AM

Hi guys,

This is where I need and would appreciate your wisdom.

I am soooooo close to solving this challenge i have been battling with for days; just need some guidance.

Detail:

I have a simple UserForm where one captures a record with some basic detail, as well as multiple line items in a ListBox.
This all saves to a sheet (Sheet1), creating multiple rows.

Then i have a feature where one would do a search for a record (to reprint or edit), using the same UserForm. The search lists the suggested items in ListBox3, and when one dbl click on the ListBox3 item, it filters Sheet1 accordingly, and updates the UserForm with that specific record.

Here is my challenge:

I added a sub updateListBox () to copy back the data of the multiple line items captured in ListBox1, back into the ListBox1. BUT, the problem is, it does not copy only the filtered data as it is supposed to. It copies all the data of my Range. It is almost as if the filter does not "trigger".

This is what i want:

After I have filtered my data using my filter options, only the filtered section of my range must be copied to my ListBox1.

I have attached my sample workbook.

Thank you very much in advance!

**Tinbendr** · 02-04-2015, 09:32 AM

Please do not start new threads with the same topic without providing a link.

**onmyway** · 02-04-2015, 09:41 AM

Sorry David. i tried re-posting my thread in a different way, as i was not getting any responses.

**Tinbendr** · 02-04-2015, 10:14 AM

The answer why it's not working is that you can't use Rows.Count on a non-contiguous range. It will stop at the end of first range. That's why the first selection works, but not on the rest.

Here's a link to a dicussion about that and ways to address it. (This is why I don't like using autofilter to fill controls. I always use Range.Find.)

**onmyway** · 02-04-2015, 12:51 PM

Hi David and all,

I have made some headway, thanks to David's link to a similar discussion (https://social.msdn.microsoft.com/Fo...t?forum=isvvba).

The code almost works, but it does not work with my named Range (RecordData), but with Cell values ("E2:J12") (http://www.mrexcel.com/forum/excel-q...-userform.html). This causes lines to be created that does not exist (duplication). i think one should add a count variable?

This is an idea of a count method:

Please Login or Register  to view this content.

This is the code of my Search/Filter function (that almost works):

Please Login or Register  to view this content.

Herewith my latest workbook.

Thank you all!

**Tinbendr** · 02-04-2015, 03:18 PM

This is the code of my Search/Filter function (that almost works):

I'm sure it's obvious to you what doesn't work. Can you explain to use now? It seems to put the values from the Listbox 1 into listbox3.

**onmyway** · 02-05-2015, 04:57 AM

hi David. I made a short movie explaining the use and challenges:

https://dl.dropboxusercontent.com/u/...A%20Help_3.mp4

Note: You might have to right click on the movie controls, and save movie as, to view the movie.

**jindon** · 02-05-2015, 05:27 AM

Not really sure how you want it
This is only my guess
Replace ListBox3_DblClick with

Please Login or Register  to view this content.

Replace updateListBox with

Please Login or Register  to view this content.

**onmyway** · 02-05-2015, 06:16 AM

Hi jindon,

thanks for the feedback. i like the idea of not filtering the data. But, when I save my data back to my sheet after editing etc., it creates new line in my sheet. I would like to search for a record, update the UserForm with the data (as per your method), but then save it back to my sheet in the exact same range.

Any ideas?

**jindon** · 02-05-2015, 06:20 AM

Then I think it will be much simpler than what you currently have.

No filter at all...

You want to search what is in ListBox3 and show the result in ListBox1,

Then you want to update the data with your change in user form.

It it correct?

**psyinx** · 02-05-2015, 06:21 AM

Not the best or most efficient solution, but it works:

Please Login or Register  to view this content.

Forgot this:

Please Login or Register  to view this content.

Oh - just want to say that I don't like this solution, but it does work!!

**onmyway** · 02-05-2015, 06:23 AM

Originally Posted by jindon

Then I think it will be much simpler than what you currently have.

No filter at all...

You want to search what is in ListBox3 and show the result in ListBox1,

Then you want to update the data with your change in user form.

It it correct?

That is correct

**jindon** · 02-05-2015, 06:26 AM

OK, I will take a close look at your file tomorrow and post the code if you still need help.

I will be very busy rest of the day....

**onmyway** · 02-05-2015, 06:27 AM

hi psyinx

Thanks for the help!

I get a Sub or Function not defined for: ReDimPreserve

Please Login or Register  to view this content.

**onmyway** · 02-05-2015, 06:29 AM

hi psyinx

Thanks for the help!

I get a Sub or Function not defined for: ReDimPreserve

Please Login or Register  to view this content.

**psyinx** · 02-05-2015, 06:35 AM

Posted the redim method in original post

**psyinx** · 02-05-2015, 06:36 AM

Posted the ReDim method in original post separate from double click event

**onmyway** · 02-05-2015, 06:43 AM

Ahhhhh!

That is the problem with working with multiple workbooks and methods and sheets.....

thanks, works great!

**psyinx** · 02-05-2015, 06:47 AM

Cool - just note that there are some assumptions in that method:

1. The "loop termination" needs to be re-considered
2. The original array size needs to be re-considered

But it is none the less a starting point.

Good luck!

**jindon** · 02-05-2015, 10:28 PM

onmyway

I need a brief explanation of each commandbuttons.

**onmyway** · 02-06-2015, 03:22 AM

Originally Posted by jindon

onmyway

I need a brief explanation of each commandbuttons.

hi jindon,

there aren't many commandbuttons. the main btn is basically to save a record to the Sheet1. Most of the functionality lies in this: to search for an existing record, to find possible matches, to update the UserForm with the record.

I made a video clip explaining my needs. I think this might help you to understand:

https://dl.dropboxusercontent.com/u/...%20Help_3.mp4\

You might have to Right click on the video controls and "save as" to view the clip.

Note: I have attached 2 workbooks. 1 with psyinx method, 1 with your WIP method.

**jindon** · 02-06-2015, 04:07 AM

The try the attached

**onmyway** · 02-06-2015, 04:23 AM

hi jindon,

Great work and coding! thank you very much.

I see however, it does not allow you to save the new lines created in the listbox. I.e., when you search for a record, edit the listbox item, and perhaps add an additional listbox item, it does not create a new row in the sheet for the new line.

Any suggestions?

**jindon** · 02-06-2015, 04:36 AM

If the item selected from listbox1, save button updates the row in sheet1 from the all the text boxes except GUID. (should not be touched)

Other change I made was to show only unique values in ListBox3 as search text entered.

I didn't touch other, so I don't understand what you are asking.

What do you want to do with Add button?

**onmyway** · 02-06-2015, 04:50 AM

hi jindon

I really appreciate all your help. big thumbs-up to you!

Most of my problems have been resolved by you and psyinx.

I am going to close this thread, and start a new one with my new challenges. Please look out for it if you'd like to help!

Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Re: Code help: Copy only filtered range to ListBox

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Copy filtered range

Bookmarks

Bookmarks

Posting Permissions