How to select specific records without filtering

[shardin]

Hi,

I have a table with thousands of lines and several columns. One column is the item's name, and another column is the price. Sometimes the user needs to amend the price por a particular item, so that all lines in wich the item is found, its price is changed to the new value.

I can do that by creating an userform with two fields, one for the item name and another for the value that must be filled out by the user. Then the macro will filter by item and will replace all visible price cells with the new value.

However this process is quite slow, as the table contains thousand of records, and it takes time when filtering and de-filtering. Is there any other way to replace automatically the old value with the new one for a specific item without filtering??

Thanks in advance,

[HaHoBe]

Hi, shardin,

as an alternative you may look at the Find-Method: search for the item, build a range (using Set, compare range to Is Nothing, Union) depending on the cells in the column to change and then change the range to the altered price.

Ciao,
Holger

[shardin]

This sounds quite good Holger, but could you give me any example about this?

Thanks in advance,

[HaHoBe]

Hi, shardin,

the basic pattern may look like this (without the values from your textbox for item to search for or altered price, looking up Column H in ActiveSheet and altering the value in Column B thereafter):

Please Login or Register  to view this content.

HTH
Holger

[shardin]

It works fine Holger.

Thanks very much for your help.