Macro to select and copy non blank cells in a column range
0
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?
Answers
0
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)
Else
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.
Discussion
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!
Thanks,
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!
Thanks,
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.
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: 1745) May 24, '17 at 1:35 am