macro to select range of cells...

[Guest]

anyone know how to create a macro that would do the following:

within a certain range of cells (ex- D1:D5), for those cells that are empty, they and their entire rows are selected/highlighted. So, for example, if D1, D2, D3, & D4 had text and or numbers in them, and D5 was empty, the macro would select/highlight the entire row 5. Thanks....

 

[Barrie Davidson]

On 2002-03-08 12:25, Anonymous wrote:
anyone know how to create a macro that would do the following:

within a certain range of cells (ex- D1:D5), for those cells that are empty, they and their entire rows are selected/highlighted. So, for example, if D1, D2, D3, & D4 had text and or numbers in them, and D5 was empty, the macro would select/highlight the entire row 5. Thanks....

This code will select the entire row where the value in column D is blank (for the range D1:D5).

Sub ThosePeskyEmptyCells()
Dim HighlightRange As Range
On Error GoTo ErrorHandler
For Each c In Range("D1:D5")
    If c.Value = "" Then
        If HighlightRange Is Nothing Then
            Set HighlightRange = Range(c.Address)
        Else
            Set HighlightRange = Union(HighlightRange, _
                Range(c.Address))
        End If
    End If
Next c
HighlightRange.EntireRow.Select
Exit Sub
ErrorHandler:
MsgBox ("No blank cells found")
End Sub

Regards,

 

[Guest]

thanks man, I'm going to give it a try!

 

[Escalus]

Or more simply :-

Sub ThosePeskyEmptyCells()
On Error Resume Next
Range("D1:D5").SpecialCells(xlCellTypeBlanks).EntireRow.Select
On Error GoTo 0
End Sub