selecting only non empty cells

[jchisholm]

I have a spread sheet where I want to select from the column only the cells that contain data. The blank cells contain formulas that reference an other worksheet and return a blank when certain conditions are not met. The data returned is the name of an employee and their shift start time. Any suggestions.

 

[Smitty]

Welcome to the Board!

Have you tried AutoFilter? In the Filter criteria just uncheck blanks.

HTH,

 

[jchisholm]

Thanks Smitty. The only issue with auto filter is that it affects all of the columns. I have 28 columns, one for each work day in a four week skd. I want to display the whole skd on single sheet with only those employees skd to work that day showing in the column.

 

[JoeMo]

Here's a macro you can try (untested) that should select all cells with constants or formulas that don't return a blank ("") value. This is setup for Column A, but is easily adapted to another column:

Sub SelectNonEmptyCells()
Dim r1 As Range, r2 As Range, r3 As Range
With Range("A:A") 'Change column to suit
    On Error Resume Next
    Set r1 = .SpecialCells(xlCellTypeConstants)
    Set r2 = .SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If Not r2 Is Nothing Then
        For Each c In r2
            If c.Value <> "" Then
                If r3 Is Nothing Then
                    Set r3 = c
                Else
                    Set r3 = Union(c, r3)
                End If
            End If
        Next c
        If Not r1 Is Nothing Then
            If Not r3 Is Nothing Then
                Application.Union(r1, r3).Select
            Else
                r1.Select
            End If
        ElseIf Not r3 Is Nothing Then
            r3.Select
            Exit Sub
        Else
            MsgBox "All cells in the range are empty"
        End If
    Else
        MsgBox "All cells in the range are empty"
    End If
End With
End Sub

 

[jchisholm]

JoeMo, thanks for the macro, I ended up recording one to do what I wanted it to do. Without the nudge toward a macro I wouldn't have thought about it.

Thanks again to all poster for the support