Macro to select and copy non blank cells in a column range

I have been looking online for the solution to my question and I can't seem to find it.  I have a spreadsheet and in column T32:T52 I have a formula that will populate some names depending on another macro.     What I want to do is select in that column only the populated names within that range.  For example if I only have the first 4 cells populated I want the macro to only select that part and copy.  The cells that aren't populated have #value in it.  I don't want those included in the copy.     If I do a macro to select and end down on the column it picks up everything.  So that won't help.  I just want the cells that have the names populated to be selected and copied.    After the macro runs I take what is copied on the clipboard and paste into another applicaction.   Can this be done?



Here is a simple macro to do this:

Sub select_non_empty()

Dim cell_range As Range, cellSelect As Range

'get the range through which we want to loop
Set cell_range = Range("T32:T52")
Set cellSelect = Nothing

'start the loop
For Each cell In cell_range

    If cell.Value <> "" Then

        If Not cellSelect Is Nothing Then

            Set cellSelect = Union(cellSelect, cell)


            Set cellSelect = cell

        End If

    End If

'continue the loop
Next cell

'select the cells
If Not cellSelect Is Nothing Then cellSelect.Select

End Sub

You still have to hit Ctrl + C after running the macro.



When I ran the macro a Microsoft Visual Basic Run-time error '13': pops up and says
Type mismatch.  So I click the debug button and there is an arrow pointing to this part of the vb code and it is shaded in yellow:

If cell.Value <> "" Then

So I am unable to figure out why this is happening.  I don't know how to fix this.  But something about that phrase the system doesn't like.  Please help!
LymaBeane (rep: 2) May 23, '17 at 11:27 am
It works in Excel 2016 for me. However, replace that line with this one and let me know if it works for you.

If Len(cell.Value) > 0 Then

I also updated the macro at the end so it won't throw an error if the range is empty.
don (rep: 1247) May 24, '17 at 1:35 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login