How to copy column until blank VBA?

[clprdctn]

Hello,

I'm trying to write a vba code to say:


In sheet "ADI MMS" copy all the data from F2 until "Blank" as value only.


Your help is greatly appreciated.
Thank you.

[GC Excel]

Hello,

Like this?

Please Login or Register  to view this content.

[Mumps1]

I assume that when you say

copy all the data from F2 until "Blank"

you mean all the data in column F starting at row 2. Is that correct? Once you copy the range value, what do you want to do with it?

[clprdctn]

Sorry GC, It's my mistake I should've explained better.

Hi Mumps,

From Cell "F2" all the way down (Always on Column F) until Blank. And all I need to copy the data to the clipboard.

[JOHN H. DAVIS]

If refering to across Columns then maybe:

Please Login or Register  to view this content.

Otherwise, ignore this post.

[Mumps1]

I don't know if there is a way that you can copy only the values. Normally what is done is that you copy the range in question and then you can paste the values only using 'xlPasteValues'.

[clprdctn]

Okay, I can work around that later but is there a code to copy the data to clipboard for this?

[Mumps1]

Try:

Please Login or Register  to view this content.

[clprdctn]

I got:

Run-Time Error '91'

Object variable or with block variable not set.

[Mumps1]

Which line was highlighted?

[clprdctn]

LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

[Mumps1]

That is strange since it is simply looking for the last used cell in your sheet. As long your sheet is not blank, it should work.

[clprdctn]

What do I add to this to say select sheet ADI MMS?

[Mumps1]

This will copy the range from the sheet "ADI MMS".

Please Login or Register  to view this content.

[clprdctn]

Still getting the same error with same highlighted area.

[clprdctn]

I got this to work:

Please Login or Register  to view this content.

[Mumps1]

Glad it worked out.