CODEWITHSUNDEEP
excelrangevba
user1959930
user1959930

Reputation: 41

Select non-blank rows in spreadsheet

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.

Upvotes: 4

Views: 18798

Answers (3)

Julieblue
Julieblue

Reputation: 11

Just used this code and it worked a treat - been tracking all other excel forums but couldn't find anything that was as simplified.

I also added that the selected rows were copied and pasted to the next blank row in another sheet, if anyone finds this useful.

Sub copypaste1()

    'Find rows that contain any value in column A and copy them
    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.EntireRow.Select
    selectRange.EntireRow.Copy

    'Paste copied selection to the worksheet 'mega' on the next blank row
    Sheets("mega").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial _
         Paste:=xlPasteValues

End Sub

Upvotes: 1

brettdj
brettdj

Reputation: 55702

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

Upvotes: 7

mkingston
mkingston

Reputation: 2718

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

Upvotes: 2

Related Questions