Macro reduces Formula Range unexpectedly.

[liquidmettle]

Hello Everyone,

Here's the background: I have a dashboard where the user pastes information from an online-generated excel report. They paste the entire sheet into the designated tab in the dashboard and then appropriate formulas manipulate the data as needed.

Recently we had a problem with the online report changing columns/rows. So I did a manual find&replace to update the formulas appropriately. I'm trying to future-proof this system against further potential changes in the online report and I've done a pretty good job with one exception.

When the data is pasted, the first 8 rows are garbage information or blank. I use the following macro to remove these rows:

Please Login or Register  to view this content.

I use the following formula to pull the relevant columns into a sheet that I can then use for pivot tables:

Please Login or Register  to view this content.

Where the C1 matches the column the formulas are in. Note: I tried index-match and it did not work in this instance (pasting new data and trimming it resulted in #Ref errors).

Now the reason for this thread is every time Trim is run, the A1:T8000 reduces (you can see now its at 7984).

Is there any way to prevent this? Or should I just avoid the trim macro because in truth I'm not sure its entirely necessary.

Thoughts?

Thanks all!

[zbor]

Deleting row

Please Login or Register  to view this content.

makes your range shorter each time.

[zbor]

You can create like this:

Please Login or Register  to view this content.

Red - Number of rows
Blue - number of columns

So deleting won't affect it (just don't delete A1

[liquidmettle]

Hi Zbor,

Thanks for the very fast replies!

So if I kept A1 (1 blank row at the top), how would I modify the macro to delete everything between A1 and the target criteria? Just change the range to A2?

Also, as far as you can tell, does the current macro code select all rows and delete them at once, or one at a time? Previously I found a version that did one at a time but that was incredibly slow. This is a little faster but I'm always looking for more efficient options.

I'll give your solution a try later this afternoon and get back to you.

Thanks!

-LM

[liquidmettle]

Hi Zbor,

So it occurred to me that if I used $A:$T that takes care of my problem entirely.

Thank you for your assistance!