Help for Excel macro to only copy data in the

Closed
Nikeyg - Nov 12, 2009 at 11:08 AM
 Nikeyg - Nov 13, 2009 at 02:05 AM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Nov 12, 2009 at 08:43 PM
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