Is it possible for a macro to do this?

I have seen many macros designed to remove blank rows in Excel but I have
many spreadsheets which, while the trailing rows appear to be blank, some
have list boxes which have not been used (appear blank) and I'd like to
remove those.

Thanks
Al

Hi Al,

Do you mean data validation type drop downs?

If is data validation type drop downs:
"Some have list boxes which have not been used (appear blank)". Do you mean
that no selection has been made or the underlying list is now blank?

What version of xl are you using? Identifying the type of validation was
added to xl2007 so not sure if it could be done in previous versions.

--
Regards,

OssieMac


"Al" wrote:

Is it possible for a macro to do this?

I have seen many macros designed to remove blank rows in Excel but I have
many spreadsheets which, while the trailing rows appear to be blank, some
have list boxes which have not been used (appear blank) and I'd like to
remove those.

Thanks
Al

If you want to delete empty rows which contain one or more cells with
dropdown list data validation in which no selection has been made, this macro
should do it:

Dim Rng As Range

Sub DelEmptyRowsWithDV()
Dim c As Range
Do While CountEmptyCellsWithDV 0
For Each c In Rng
If c.Validation.Type = 3 Then
With ActiveSheet
If Application.CountA(.Rows(c.Row)) = 0 Then
.Rows(c.Row).Delete
End If
End With
End If
Next c
Set Rng = Nothing
Loop
End Sub

Private Function CountEmptyCellsWithDV() As Long
Dim x As Long, m As Range
On Error GoTo CECwDVerr
Set Rng = ActiveCell.SpecialCells(xlCellTypeAllValidation)
x = 0
For Each m In Rng
If Len(m.Value) = 0 And m.Validation.Type = 3 Then
x = x + 1
End If
Next m
CountEmptyCellsWithDV = x
Exit Function
CECwDVerr:
CountEmptyCellsWithDV = -1
End Function

Hope this helps,

Hutch

"Al" wrote:

Is it possible for a macro to do this?

I have seen many macros designed to remove blank rows in Excel but I have
many spreadsheets which, while the trailing rows appear to be blank, some
have list boxes which have not been used (appear blank) and I'd like to
remove those.

Thanks
Al

Tom you are a true master of Excel VBA - thank-you very very much
This is exactly what I needed.
Al

"Tom Hutchins" wrote:

If you want to delete empty rows which contain one or more cells with
dropdown list data validation in which no selection has been made, this macro
should do it:

Dim Rng As Range

Sub DelEmptyRowsWithDV()
Dim c As Range
Do While CountEmptyCellsWithDV 0
For Each c In Rng
If c.Validation.Type = 3 Then
With ActiveSheet
If Application.CountA(.Rows(c.Row)) = 0 Then
.Rows(c.Row).Delete
End If
End With
End If
Next c
Set Rng = Nothing
Loop
End Sub

Private Function CountEmptyCellsWithDV() As Long
Dim x As Long, m As Range
On Error GoTo CECwDVerr
Set Rng = ActiveCell.SpecialCells(xlCellTypeAllValidation)
x = 0
For Each m In Rng
If Len(m.Value) = 0 And m.Validation.Type = 3 Then
x = x + 1
End If
Next m
CountEmptyCellsWithDV = x
Exit Function
CECwDVerr:
CountEmptyCellsWithDV = -1
End Function

Hope this helps,

Hutch

"Al" wrote:

Is it possible for a macro to do this?

I have seen many macros designed to remove blank rows in Excel but I have
many spreadsheets which, while the trailing rows appear to be blank, some
have list boxes which have not been used (appear blank) and I'd like to
remove those.

Thanks
Al