CODEWITHSUNDEEP
vbaexcelselect
Megan
Megan

Reputation: 231

Selecting all non-blank cells in variable range in Excel VBA

I have I data set on Excel. Starting at column B, it has continuous data from B3 to a variable number that periodically get larger (today it is B114, but tomorrow the data may extend to B116, for example). The data in cell B in continuous and is never deleted. For every row of continuous data in column B, I want to select column B-AG's rows as well. However, the rows after B do NOT have continuous data.

For example: There is continuous data from B3 to B120. I want to select the range B3:AG120.

The code I have written to do this in VBA is not working. It correctly stops at B120 (in this example), however, once it reaches the non-continuous data in columns C-AG, it freaks out and selects rows past 120. I am not positive why this code is not working, any help is much appreciated!

For the record, there are formulas in nearly every cell in the sheet. Only some formula populate the cell with data, however. I want to select every cell regardless of if it is populated with data IF IT IS IN MY RANGE. Otherwise, I do not want to select it. For example, past B120 there are empty cells with formulas in them. I do not want to include those in my range. But if there is an empty cell in D40 (in between B3 and AG120) I do want to include that in the selection. 

Dim LR As Long, cell As Range, rng As Range
With Sheets("Sortable(2)")
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    For Each cell In .Range("B3:B" & LR)
        If cell.Value <> "" Then
            If rng Is Nothing Then
               Set rng = cell
            Else
               Set rng = Union(rng, cell)
            End If
         End If
    Next cell
    rng.Select
End With

Upvotes: 0

Views: 11730

Answers (1)

Tim Williams
Tim Williams

Reputation: 166860

Dim lastVal As Range, sht As Worksheet

Set sht = Sheets("Sortable(2)")

Set lastVal = sht.Columns(2).Find("*", sht.Cells(1, 2), xlValues, _
                                xlPart, xlByColumns, xlPrevious)

Debug.Print lastVal.Address

sht.Range("B2", lastVal).Resize(, 32).Select 'select B:AG

Upvotes: 4

Related Questions