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
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
There is a function called "USEDRANGE" which covers only the available range.

something like this code

activesheet.usedrange.select


try this.
0
That has worked brilliantly.

Thank you so much.
0