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.
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"
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
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))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
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
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
Range(B2:C7)the cellsB4andC4will be cleared, so you will need to select theRange(B2:C3)or you still need to selectRange(B2:C7)?