Help for Excel macro to only copy data in the [Closed]

Report
-
 Nikeyg -
Hello,

Allow me to thank you in advance for any help you give me, it is really appreciated.

I am trying to compile a spreadsheet of variable data so that after pasting the data into one tab I can run a macro and have it all filtered accordingly on the front tab.

The data I am pasting into my back tab, which I call "Data" has a variable amount of lines.
I originally set this so the recorded macro used the ctrl+shift with arrow key function to highlight right, then down.
This worked well for multiple rows, but when it's only one row this caused a problem as it highlighted the entire 65000 odd rows and attempted to paste these into the front sheet, which obviously wouldn't work due to the sheet row limitations.

So, now I am using

Range("A1:I200").Select
Selection.Copy

This works in theory, as it is highly unlikely my data will go up to 200 rows. But I am feeling uneasy and do not want to take the risk.
Is it possible to have the macro only highlight the actual data in the sheet and no blank cells at all?
I've been googling all day and not been able to find a solution.

Thanks

Nick

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
800
There is a function called "USEDRANGE" which covers only the available range.

something like this code

activesheet.usedrange.select


try this.
That has worked brilliantly.

Thank you so much.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!