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?
 
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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.