Select visible row filtered data

[Nipius]

I have an Excel file with over 50.00 rows, which I update on a monthly base. However, some information needs to be updated on ad-hoc base.
These updates take information from a different Excel file. Both files use the same unique values, allowing me to index match the information from one file to the other.

My solution to only update the rows with new information, is as follows. I apply a filter based on the unique identifier value from the updatefile. With an Index/Match formula, I then update the information in some columns, select the full row and paste it as values. I then go on the the next unique identifier and continue with the same process until everything has been updated.

It works, but I do have an issue, because of the way I select the cell in the only visible row. For now I use the following formula:

Please Login or Register  to view this content.

It thus starts at the header row and then loops until it finds the only visible row. Because of the huge number of rows, this takes quite some time.

Is there an easier way to achieve what I want?

[kev_]

I understand that this may vary - just trying to understand relative sizes
Q1
There are 50k rows
How many rows are visible after filter?
How many rows are updated?

Q2
Instead of " I select the full row and paste it as values", why not copy & paste values the whole sheet in one hit like this?:

Please Login or Register  to view this content.

Q3
What do you mean by "update the information in some columns" - are you pasting or doing something different?

Selecting is usually not necessary
"Selecting" - takes time and VBA usually does not require it. Just point it at the correct cells and tell it what to do without selecting

[TMS]

Try something like:

Please Login or Register  to view this content.

[Nipius]

Thanks for your reply!

I understand that this may vary - just trying to understand relative sizes
Q1
There are 50k rows
How many rows are visible after filter?
How many rows are updated?

After filter only 1 row.
The number of updates rows differ per update.

My reasoning is that I have my masterfile with 50K rows. The updatefile can contain updates for 10 rows or for 100 rows. So, I need to apply my Index/Match formula only to the rows for which I have an update. That is why I thought it would make sense to only keep 1 row visible in my masterfile and update some cells. Then filter the next row, update, and so on.

Q2
Instead of " I select the full row and paste it as values", why not copy & paste values the whole sheet in one hit like this?:

Please Login or Register  to view this content.

Makes sense, but I apply the filter, based on the unique identifier in cell A2 in the update file. Once updated, I delete row 2 in the updatefile, shifting the cells up and resulting in a new value in A2 which then will be used to apply on the filter again in the masterfile.

Q3
What do you mean by "update the information in some columns" - are you pasting or doing something different?

Each row has approx. 50 columns with information. But only 5 columns have information that needs to be updated based on the updatefile.

Selecting is usually not necessary
"Selecting" - takes time and VBA usually does not require it. Just point it at the correct cells and tell it what to do without selecting

Makes sense!