Deleting rows which contain blank cells

  • Thread starter Thread starter Colin Hayes
  • Start date Start date
C

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.
 
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.
Click to expand...

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 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.
Click to expand...

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
 
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!
Click to expand...

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
 
Glad you got it working! I'm happy to be of help...
Click to expand...


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.
 
Colin Hayes wrote on 5/20/2012 :
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.
Click to expand...

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

--
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 wrote on 5/20/2012 :
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.
Click to expand...

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'!

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
BTW Curiously when I effect the same change manually (F5 - Special - Blanks -
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'!
Click to expand...

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.
 
Colin Hayes brought next idea :
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.
Click to expand...

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

--
Garry

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