Excel VBA code for selecting nonblank cells

Question

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

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

How can I rewrite the code so that it automatically selects cells that are not empty? If I delete the data in cells B7 and C7, then I want the macro to select only Range (B2: C6) and if I add the data to cells B8 and C8, then I want the macro to select Range (B2: C8).

My data will always run B2, C2, and I will not have free space between the data.

Answer 1

B2, C2 inbetween? , " "

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

B2 C "

Answer 2

:

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

, :

Range("B2:C7").CurrentRegion

Answer 3

Use the 'SpecialCells' Function of the Selection Object

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