Excel VBA code to select non empty cells

6
votes

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.

4
vbaexcelspreadsheet
Possible duplicate of stackoverflow.com/questions/821364/… – Marek Sagan
@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 Ivoyzha

4 Answers

8
votes

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"

2
votes

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
1
votes

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
1
votes

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