Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Embedded blank rows
I have the following code that was kindly provided by Tom Hutchins, it worked
perfectly for us but now there is one change that we'd like to make but are not quite sure how to do it. (Many thanks to Tom for this great code) Here is the code: 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 Application.Dialogs(xlDialogSaveAs).Show 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 This code removes blank rows (including cells with blank drop-down boxes) and it works great as long as there are no embedded blank rows in the sheet. As soon as there is one blank row somewhere in the middle, the macro hangs. Could someone help proved the change needed to allow for blank rows in the middle of the sheet? We want all trailing blank rows removed (they may contain blank drop-downs) Thanks in advance Al |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting blank rows which contain blank drop-down list boxes | Excel Programming | |||
How do I delete blank rows (rows alternate data, blank, data, etc | Excel Discussion (Misc queries) | |||
Hiddening Rows in an embedded worksheet | Excel Programming | |||
Delete blank row only if 2 consecutive blank rows | Excel Programming | |||
Copying and pasting a worksheet to a blank and removing blank rows | Excel Programming |