select A2 down +1

[greekboyuk]

Hi,

I have the following code to select a2 and go down:

Please Login or Register  to view this content.

I would like to do one more cell down after that?
What do I have to add?

Many Thanks

greekboyuk

[Mayweed]

Range("A2").Select
Selection.End(xlDown).Offset(1, 0).Select

[dominicb]

Mayweed

Please use code tags.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

But ...

Just taking Mayweed's answer a stage further we can avoid the use of unnecessary "Selects", which slows down execution, thus :

Please Login or Register  to view this content.

HTH

DominicB

[Mayweed]

Ooops! Sorry about that.

[greekboyuk]

Thanks Dominic, that's great. Mayweed, thanks as well. Is there a way to use similar concept when copying the data. For example I would like to copy data from A2:W2 and all the way down. If there are two or more rows filled with data it is fine as it copies only the data, however if only row2 contains data, it copies 65000cells?
How could I prevent this please?

Many Thanks for your contributions much appreciated

Regards

greekboyuk

[StephenR]

This perhaps?

Please Login or Register  to view this content.

[greekboyuk]

]Hi Stephen,

Thank you for your help. I have created the following code:

Please Login or Register  to view this content.

Now if test sheet is blank it copies my column headers. How could we prevent this please? If there is no data simply do not bring it back, go to next workbook?

Many Thanks

[StephenR]

Not tested but see if this works. You may have to change the sheet names.

Please Login or Register  to view this content.

[greekboyuk]

Hi stephen

thank you again for your help. For some reasons now it highlight the Used ranged line.

Please Login or Register  to view this content.

Sub stops.

why this is happening??

Many thanks

greekboyuk

[StephenR]

What happens? Does it error? If you only have one row of data there is nothing to do.

[Mayweed]

Two files attached... might help?

[greekboyuk]

Hi mayweed,

Thank you for your help,
I tested your attachments and they dont function the way that I wanted them. I would like the first row of the test data not to be copied.
Also, when test workbook is empty (ignore row1) then the msg is displayed which is great however the test workbook stays open and it doesn't close.

Any ideas please?

Many Thanks

greekboyuk

[Mayweed]

Try the attached files.