How to copy only cells that are not blank in a range to the clipboard via vba?

I have a dynamic list that populates in range U2:U12.  I want to copy just the cells that are not blank.  That way I don’t have a bunch of blank lines when I paste it into a word document.
kbay808Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Range("U2:U12").Select
    Range("U12").Activate
    Selection.AutoFilter
    ActiveSheet.Range("$U$2:$U$12").AutoFilter Field:=1, Criteria1:="<>"
    Selection.Copy
' Do what you want to with the non-blank cells and then display all cells
    Selection.AutoFilter
0
Saqib Husain, SyedEngineerCommented:
If they are all constants you can try simply

range("U2:U12").SpecialCells(xlCellTypeConstants).Copy

If they are all formulas then

Range("U2:U12").SpecialCells(xlCellTypeFormulas).Copy
0
EirmanChief Operations ManagerCommented:
If you want to avoid coding ......
Home > Find & Select > Go To Special > Constants
Described here (as well as several other methods)  ...
http://www.extendoffice.com/documents/excel/1578-excel-copy-and-paste-only-non-blank-cells.html

Temporarily paste the contiguous data into another region of your worksheet
and copy that into word.
0
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

kbay808Author Commented:
@All – I attached an example file so that you have a better understanding of what I’m working with.  When the user clicks on the “Copy List” button, they need to be able to paste it into a word document without all of the extra spaces.

@ Martin Liss – Your code works, but I have the cells hidden from the user and the filter changes the view in the excel form.  I need a code to copy it to the clipboard in the background so that the user’s view does not change.

@ Saqib Husain, Syed – The spaces still show up when I paste the results into a word document.

@ Eirman – Sorry, I need it to work with a button.
Copy-List-Example.xlsm
0
Martin LissOlder than dirtCommented:
At the start of my code add Application.screenupdating = False at the start and Application.screenupdating = True at the end. They won't see anything if you do that.
0
Saqib Husain, SyedEngineerCommented:
For the given file it should be

Range("a2:a12").SpecialCells(xlCellTypeConstants).Copy Range("F2")
0
kbay808Author Commented:
@ Martin Liss - Here is how I entered your code.  I see the range selected, but when I try to paste it I get nothing.

Sub Copy()
Application.ScreenUpdating = False
Range("C2:C12").Select
     Range("C12").Activate
     Selection.AutoFilter
     ActiveSheet.Range("$C$2:$C$12").AutoFilter Field:=1, Criteria1:="<>"
     Selection.Copy
 ' Do what you want to with the non-blank cells and then display all cells
     Selection.AutoFilter
Application.ScreenUpdating = True
End Sub

Open in new window

0
kbay808Author Commented:
@ Saqib Husain, Syed – It looks good, but I only want to copy it to the clipboard.
0
Martin LissOlder than dirtCommented:
That's because you haven't told it to do anything with the data. Here's an example where the data is pasted to column A

Sub Copy()
Application.ScreenUpdating = False
Range("C2:C12").Select
     Range("C12").Activate
     Selection.AutoFilter
     ActiveSheet.Range("$C$2:$C$12").AutoFilter Field:=1, Criteria1:="<>"
     Selection.Copy
 ' Do what you want to with the non-blank cells and then display all cells
 Range("A1").Select
 ActiveSheet.Paste
 
     Selection.AutoFilter
Application.ScreenUpdating = True

Open in new window

0
kbay808Author Commented:
@Martin Liss – The user needs to be able to paste it into a word document.  The goal is to have a user click on the “Copy List” button and then click into the word document and press ctr+V to paste it.
0
Martin LissOlder than dirtCommented:
Sorry but I misunderstood what you wanted, so try this. It requires that you add a Reference to the Microsoft Forms 2.0 Object library. If you don't find that reference in the References list (Tools->References...), add a userform (which you can later delete) and it will show up.

Sub Copy()

Dim obj As New DataObject
Dim strTemp As String
Dim c As Range
Dim bFirstTime As Boolean

bFirstTime = True
For Each c In Range("C2:C12")
    If c.Value <> "" Then
        If bFirstTime Then
            bFirstTime = False
            strTemp = c.Value
        Else
            strTemp = strTemp & vbCrLf & c.Value
        End If
    End If
Next
obj.SetText strTemp
obj.PutInClipboard
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kbay808Author Commented:
That worked perfect!!!  Thank you very much.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to (eventually) help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
0
Saqib Husain, SyedEngineerCommented:
For copy only you would need

Range("a2:a12").SpecialCells(xlCellTypeConstants).Copy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.