Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
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: 1989) May 24, '17 at 1:35 am