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.

Decima

Possible duplicate of #821864 – Tysontyumen

@Decima 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)? – Unquestioned

Answer 1

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"

Uhf

"...define the range from B2 to the C"last row"". How exactly,? I am not sure what you mean exactly – Decima

What if there are some empty cells in between? – Groveman

Answer 2

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

Seadon

Answer 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

Frequentative

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)) – Bushhammer

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. – Bushhammer

Answer 4

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

Marriageable

Answer 5

Create this module, select a range in a sheet, launch the Macro by Alt+F8 and you will select only the cells with data, example, to create the menu for convalidation. Important, the empty cell must not have any style, no borders, just normal empty cells. This algorithm select the cells in order of the columns, se the creation of the menu is possibile, the creation of the list.

Sub SelectCellsWithData()
Dim bCell As Range
Dim bCellC As Range
Dim bCellR As Range
Set myRange = Application.Selection

Set bCellC = Nothing
For Each myCellC In myRange.Columns
    Set bCellR = Nothing
    For Each myCellR In myCellC.Rows
    
        If myCellR.Value <> "" Then
            If bCellR Is Nothing Then
                Set bCellR = myCellR
            Else
                Set bCellR = Union(bCellR, myCellR)
            End If
        End If
    Next
    If bCellC Is Nothing Then
        Set bCellC = bCellR
    Else
        Set bCellC = Union(bCellC, bCellR)
    End If
Next
If Not bCellC Is Nothing Then
    bCellC.Select
End If
End Sub

Natishanative