Collect numbers from a column containing empty cells using Excel VBA

Question

I have a little problem, I occasionally bump into this kind of problem, but I haven’t found a fast solution so far.

So, imagine we have an Excel worksheet and let's suppose that we have a couple of numbers in column ’A’ with some empty cells in it. Altogether (just to make it simple) we have the first 10 cells in column 'A' to observe. For example:

3
(empty cell)
(empty cell)
6
(empty cell)
4
(empty cell)
23
(empty cell)
2

Now in the next step I would like to collect these numbers into another column (for example, column ’B’) using VBA. Obviously I just want to collect those cells which contain a number and I want to ignore the empty cells. So I would like to get a column something like this:

I have already written the following code, but I’m stuck at this point.

Sub collect()
For i = 1 To 10
    if cells(i,1)<>"" then...
Next i
End Sub

Is there an easy way to solve this problem?

See if http://stackoverflow.com/questions/5338725/excel-macro-copy-a-range-of-cells-and-only-select-cells-with-data is helpful to you — jonsca, May 13 '11 at 09:42

score 3 · Answer 1 · edited Jan 01 '20 at 21:05

3

Probably the quickest and easiest way is to use Excel's Advanced Filter - the only amendment you'll need to make is it add a field name and criteria. You can even list unique items only:

Enter image description here

The VBA equivalent is

Sub test()

    With Sheet1
        .Range("B1:B8").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=.Range( _
            "D1:D2"), CopyToRange:=.Range("F1"), Unique:=False
    End With

End Sub

edited Jan 01 '20 at 21:05

Peter Mortensen

30,738
21
105
131

answered May 13 '11 at 10:49

1

I definitely favour solutions that don't iterate and instead use Excels built in functions. Compare the speed of this to any of the vba solutions below and you'll see it's materially faster. – Runonthespot May 13 '11 at 10:52

jonsca · Answer 2 · 2011-05-13T09:56:30.427

2

You should be able to use the method in the post int the comments, but you could also use SpecialCells like Range("A:A").SpecialCells(xlCellTypeConstants,xlNumbers).Copy to get all of the filled cells.

Edit: needed constants not formulas.

edited May 13 '11 at 09:56

answered May 13 '11 at 09:50

jonsca

10,218
26
54
62

What do you mean by *"in the post int the comments"*? *"in posting the comments"*? *"in the post and the comments"*? *"in the post in the comments"*? Something else? (Respond by [editing your answer](https://stackoverflow.com/posts/5990124/edit), not here in comments (as appropriate).) – Peter Mortensen Jan 01 '20 at 21:01

score 0 · Answer 3 · edited Jan 01 '20 at 21:03

0

If you wish to loop manually and don't mind specifying the maximum row limit;

Dim i As long, values As long

For i = 1 To 10
    If cells(i, 1).Value <> "" Then
        values = (values + 1)
        ' // Adjacent column target
        cells(values, 2).value = cells(i, 1).value
    End If
Next i

edited Jan 01 '20 at 21:03

Peter Mortensen

30,738
21
105
131

answered May 13 '11 at 09:56

Alex K.

171,639
30
264
288

score 0 · Answer 4 · answered May 13 '11 at 10:23

This will work for any number of rows that you select. It will always output in the next column at the start of your selection e.g. if data starts in B10 it will ooutput in C10

Sub RemoveBlanks()
    Dim cl As Range, cnt As Long
    cnt = 0

    For Each cl In Selection
        If Not cl = vbNullString Then
            Cells(Selection.Cells(1, 1).Row, Selection.Cells(1, 1).Column).Offset(cnt, 1) = cl
            cnt = cnt + 1
        End If
    Next cl
End Sub

Collect numbers from a column containing empty cells using Excel VBA

4 Answers4

Linked