Select non-blank rows in spreadsheet

Question

I am going to try and keep this as short as I can and still explain adequately, here goes :)

I have searched forums, my VBA literature, and cannot find a way to do what I'm trying.

I have a spreadsheet with rowTotal >= 60 rows. The rows either have text data in cells of each column, or the rows are blank with a pattern and colorindex set.

I need a macro to select all non-blank rows.

I first tried looping through the cells of column A (if a cell in column A has text data, then its row should be selected), checking if activecell.value <> empty.

Here's the jist (mix of pseudocode & code):

Range("A1").Select
loop to end
  if activeCell.value <> empty then 
    stringVar = stringVar + cstr(activeCell.row) + ":" + cstr(activeCell.row) + ","
  end if 
end loop

stringVar = Left(stringVar, (Len(stringVar) - 1))
Range(stringVar).Select

1. If I have total 10 rows with rows 2 and 8 having data, stringVar would resolve to this: "2:2, 8:8".
   Range(stringVar).Select would have same result as writing Range("2:2, 8:8").Select.

2. If the number of rows to be in the range is <= 45, this works no problem. However, as soon as the number of rows with data in them exceeds 45, the code fails on Range(stringVar).Select.

I tried the macro recorder and it gets around this by using the Union method. And so I thought, "self, you can get this done with Union(). hooray MacroRecorder." But alas, my joy was remiss.
I was thinking I could split the one large string into 1 or more strings; each of these smaller strings would be under the 45 limit mentioned above. Then I can use Union() to group all the ranges (these smaller strings) together into the one desired range.

However, I would have to "build" my Union() code in real time during code execution, after I knew how many of these 45> strings I had.

Anyone know how to take a worksheet and select just rows that contain data; which amounts to having a range of non-contiguous rows where more than a count of 45 rows are selected.

Answer 1

No need for loops - use SpecialCells

For column A only use:

Set rng1 = Columns("A").SpecialCells(xlCellTypeConstants).EntireRow

instead.

Sub QuickSet()

  Dim rng1 As Range

  On Error Resume Next
  Set rng1 = Cells.SpecialCells(xlCellTypeConstants).EntireRow
  On Error GoTo 0

  If Not rng1 Is Nothing Then
    MsgBox "Your working range is " & rng1.Address(0, 0)
  Else
    MsgBox "No constants found"
  End If

End Sub

Answer 2

I first suggest you try using Autofilter. If you're using Excel 2010 (and prob 2007, but I can't check) this is as simple as selecting your data, choosing the "Data" tab, then clicking Filter. Using the drop-down box in your first column, deselect "blanks".

The exact same functionality exists in Excel 2003, under the Data/Filter menu option. I can't really remember it all that well, though; you'll have to experiment, or Google it.

If that doesn't work:

Sub it()

    Dim cell As Range
    Dim selectRange As Range

    For Each cell In ActiveSheet.Range("A:A")
        If (cell.Value <> "") Then
            If selectRange Is Nothing Then
                Set selectRange = cell
            Else
                Set selectRange = Union(cell, selectRange)
            End If
        End If
    Next cell

    selectRange.Select
    ' selectRange.EntireRow.Select 'If you want to select entire rows

End Sub