Excel VBA code to select non empty cells

Question

In Excel, let's I have data in B2 to B7 and C2 to C7 . In VBA I can write a macro to select it:

Sub Macro1()
Range("B2:C7").Select
End Sub

How do I rewrite the code so that it chooses automatically the cells that are non-empty? If I delete the data in cell B7 and C7 then I want the macro to select only Range(B2:C6) And if I add data to Cell B8 and C8 then I want the macro to choose Range(B2:C8).

My data will always start a B2,C2 and I will not have any free space between data.

Possible duplicate of http://stackoverflow.com/questions/821364/selecting-non-blank-cells-in-excel-with-vba — Marek Sagan, Oct 10 '16 at 22:02
@k.dkhk what if from the `Range(B2:C7)` the cells `B4` and `C4` will be cleared, so you will need to select the `Range(B2:C3)` or you still need to select `Range(B2:C7)`? — Vasily, Oct 10 '16 at 22:12

L.Dutch · Accepted Answer · 2016-10-11T12:45:20.263

11

your data always start at B2,C2 and has no empty cell inbetween? If so you can set a variable to be the "last filled in row"

lastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
Range("B2:C" & lastRow).Select

and define the range from B2 to the C"last row"

edited Oct 11 '16 at 12:45

answered Oct 10 '16 at 22:56

L.Dutch

926
3
17
38

"...define the range from B2 to the C"last row"". How exactly,? I am not sure what you mean exactly – k.dkhk Oct 11 '16 at 06:16
What if there are some empty cells in between? – Nenne Jan 25 '21 at 09:36

score 3 · Answer 2 · answered Sep 27 '19 at 14:27

3

Use the 'SpecialCells' function of Selection object

Sub Macro1()
    Range("B2:C7").Select
    For Each self in Selection.SpecialCells(xlCellTypeConstants)
        Debug.Print(self)
    Next
End Sub

answered Sep 27 '19 at 14:27

Maksim Sych

31
2

1

Good tip re: SpecialCells Selection. Exactly what I needed. Note: `xlCellTypeConstants` will only select cells with constant values (obviously), not ones whose value is formula-driven. The OP's question is a little hard to understand but does state "cells that are non-empty", which I read as constants _and_ formulas with non-empty results. This can still be done compactly with your method by taking the Union of `xlCellTypeConstants` and `xlCellTypeFormulas` results. I.e. `Union(Selection.SpecialCells(xlCellTypeConstants), Selection.SpecialCells(xlCellTypeFormulas))` – SSilk Jan 17 '22 at 21:33
Follow up to my previous comment: the union method fails if either of the two ranges being unioned is empty. I.e. if the range does not contain any formula driven values. So you would need to add a check on the length of each range. – SSilk Jan 17 '22 at 21:37

score 3 · Answer 3 · answered Feb 17 '21 at 12:27

In order to get all the nonblank cells you have to collect cells containing formulas too:

Function getNonBlankCells(myRange As Range) As Range
    Dim tmpRange As Range, resultRange As Range
    
    Set resultRange = Nothing
    Set tmpRange = myRange.Cells.SpceialCells(xlCellTypeConstants)
    If Not tmpRange Is Nothing Then Set resultRange = tmpRange
    
    Set tmpRange = myRange.Cells.SpceialCells(xlCellTypeFormulas)
    If Not tmpRange Is Nothing Then
        If resultRange Is Nothing Then
            Set resultRange = tmpRange
        Else
            Set resultRange = Union(resultRange, tmpRange)
        End If
    End If

    Set getNonBlankCells = resultRange
End Function

score 2 · Answer 4 · answered Oct 10 '16 at 22:31

Use a loop:

Sub qwerty()
    Dim rng As Range, r As Range, rSel As Range

    Set rng = Range("B2:C7")
    Set rSel = Nothing

    For Each r In rng
        If r.Value <> "" Then
            If rSel Is Nothing Then
                Set rSel = r
            Else
                Set rSel = Union(rSel, r)
            End If
        End If
    Next r
    If Not rSel Is Nothing Then rSel.Select
End Sub

If you want to expand the area being tested, use:

Range("B2:C7").CurrentRegion

Excel VBA code to select non empty cells

4 Answers4

Linked