CODEWITHSUNDEEP
vbaexcelspreadsheet
k.dkhk
k.dkhk

Reputation: 530

Excel VBA code to select non empty cells

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.

Upvotes: 10

Views: 85828

Answers (5)

Salvatore Caltabiano
Salvatore Caltabiano

Reputation: 1

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

Upvotes: 0

eFi
eFi

Reputation: 51

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

Upvotes: 4

Maksim Sych
Maksim Sych

Reputation: 41

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

Upvotes: 4

L.Dutch
L.Dutch

Reputation: 966

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"

Upvotes: 12

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

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

Upvotes: 2

Related Questions