Hi

I'm trying to delete all the rows in my worksheet which have any blank
cells in them.

This doesn't work properly :

Columns.Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete


Can someone suggest an amendment to this? Also if a popup could be
worked in for the user to confirm delete I'd be grateful.

Thanks.

Colin Hayes formulated on Friday :

Hi

I'm trying to delete all the rows in my worksheet which have any blank cells
in them.

This doesn't work properly :

Columns.Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete


Can someone suggest an amendment to this? Also if a popup could be worked in
for the user to confirm delete I'd be grateful.

Thanks.

This is trying to delete all cells in every column that's not
containing content. Try limiting your selection to UsedRange so
columns/rows outside this are left alone. If yo want to delete extra
cols/rows outside the UsedRange it might work better to select rows (or
cols), delete, then select cols (or rows) and delete again.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

In article , GS writes
Colin Hayes formulated on Friday :

Hi

I'm trying to delete all the rows in my worksheet which have any blank cells
in them.

This doesn't work properly :

Columns.Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete


Can someone suggest an amendment to this? Also if a popup could be worked
in
for the user to confirm delete I'd be grateful.

Thanks.

This is trying to delete all cells in every column that's not
containing content. Try limiting your selection to UsedRange so
columns/rows outside this are left alone. If yo want to delete extra
cols/rows outside the UsedRange it might work better to select rows (or
cols), delete, then select cols (or rows) and delete again.


Hi Garry

OK Thanks. I see your logic.

Interestingly , I can do this manually with success , but a recorded
macro gives 'cannot use that command on overlapping selections' and
other errors. This is very curious.

I think something like

Select column A
If it has content then
Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete
Select next column
If it has content then
Selection.SpecialCells(xlCellTypeBlanks).EntireRow .Delete
do this until column has no used cells.


Can you suggest some code that would do this , please? It's the looping
element that I'm having trouble with in VBA.

Grateful for any help.



Best Wishes


Colin

I think the Q is whether you want to delete empty rows, OR any row in
which any cell of any column is blank. Since you're deleting entire
rows then maybe..

If WorksheetFunction.CountA(Rows(i)) = 0 Then Rows(i).Delete

...where i is the loop counter ranging from the 1st row to the last row
containing data.

Alternatively, if you're trying to delete records with incomplete
fields of data then...

Dim lCols As Long
lCols = ActiveSheet.UsedRange.Columns.Count

'Assume 1st row contains header
For i = 2 To ActiveSheet.UsedRange.Rows.Count
If WorksheetFunction.CountA(Rows(i)) < lCols Then Rows(i).Delete
Next 'i

...otherwise, explain exactly what it is you're trying to do!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Since we're deleting rows, we need to start at the bottom and work
up...

For i = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If WorksheetFunction.CountA(Rows(i)) < lCols Then Rows(i).Delete
Next 'i

...otherwise the shift after delete will skip a row if going downward!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

In article , GS writes
Since we're deleting rows, we need to start at the bottom and work
up...

For i = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If WorksheetFunction.CountA(Rows(i)) < lCols Then Rows(i).Delete
Next 'i

..otherwise the shift after delete will skip a row if going downward!


Hi Garry

OK many thank for that. It's working fine.

Yes , I do want to delete all rows which have any blank cells at all in
them.

I added a couple of lines to top and bottom to switch screen updating
off and on. The final macro I'm running looks like this ;


Sub A_Delete_All_Rows_Containing_Blank_Cells()

Application.ScreenUpdating = False

Dim lCols As Long
lCols = ActiveSheet.UsedRange.Columns.Count

'Assume 1st row contains header
For i = ActiveSheet.UsedRange.Rows.Count To 2 Step -1
If WorksheetFunction.CountA(Rows(i)) < lCols Then Rows(i).Delete
Next 'i

Application.ScreenUpdating = True

End Sub

I assume this is what you intended , if I've understood you right.

I'm grateful again for your time and expertise.



Best Wishes


Colin