Deleting rows which contain blank cells

[Colin Hayes]

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.

[GS]

Colin Hayes formulated on Friday :

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

[Colin Hayes]

In article <jp6ntu$nva$1...@dont-email.me>, GS <g...@somewhere.net> 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

[GS]

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!

[GS]

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!

[Colin Hayes]

In article <jp7fue$3q2$1...@dont-email.me>, GS <g...@somewhere.net> 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

[GS]

Glad you got it working! I'm happy to be of help...

[Colin Hayes]

In article <jp8i15$s7f$1...@dont-email.me>, GS <g...@somewhere.net> writes

>Glad you got it working! I'm happy to be of help...
>

OK thanks Garry.

BTW Curiously when I effect the same change manually (F5 - Special -
Blanks - Edit - Delete - Entire Row) it leaves 4359 rows from a
worksheet with 8546 rows. Using the macro it leaves 4244 lines from the
same worksheet. This is a little mysterious , I find. The worksheet has
no formulas or merged cells or false spaces in cells.

[GS]

Are there any empty cells in columns of the 4359 rows left using F5...?

[GS]

Colin Hayes wrote on 5/20/2012 :

Another thought...

The code acts on the UsedRange. Not sure what F5 acts on and so the
code might remove more if UsedRange extends beyond the actual data. You
can find the last row/col limits via 'Ctrl+End'!

[Colin Hayes]

>> BTW Curiously when I effect the same change manually (F5 - Special - Blanks -
>> Edit - Delete - Entire Row) it leaves 4359 rows from a worksheet with 8546
>> rows. Using the macro it leaves 4244 lines from the same worksheet. This is a
>> little mysterious , I find. The worksheet has no formulas or merged cells or
>> false spaces in cells.
>
>Another thought...
>
>The code acts on the UsedRange. Not sure what F5 acts on and so the
>code might remove more if UsedRange extends beyond the actual data. You
>can find the last row/col limits via 'Ctrl+End'!
>

Hi Garry

Some very interesting results. I used the manual F5 method and it
selected all the blank cells in the worksheet. I then deleted the whole
row for each and it left 4539 rows as before.

I then used the F5 method again on the amended worksheet and it found
more blank cells! This is very curious , as I thought all the blanks
would have been selected the first time around. On deleting the whole
row for each , it left 4244 rows. This is the same as the macro leaves.

I'm sure there must be a logic for this phenomenon. Clearly the macro
achieves the same result as the manual method , but does it in one pass
rather than two.

Ctrl-End does find the bottom right extremity of used range , as it
should.

[GS]

Colin Hayes brought next idea :

That's interesting to know! Thanks for reporting back...