specialcells(xlcelltypeblanks)

[Neil]

Hi,

I have a sheet that I have imported data to, it contains approx 35000 rows
and 7 columns.
10000 of these rows either have a empty cell in column A or has text in it
and I want to delete the entire row
Is there a quicker way then checking each cell in column A and then
deleteing that row.

Sub CheckForNumber()
Dim Lastrow As Long
Dim i As Long
Lastrow = Cells(65536, 1).End(xlUp).Row + 1
For i = Lastrow To 1 Step -1
Range("a" & i).Activate
If Not IsNumeric(ActiveCell.Value) Or IsEmpty(ActiveCell) Then
ActiveCell.EntireRow.Delete
End If
Next
End Sub

I have tried using the following to delete the empty cells

'On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'On Error GoTo 0

but it deletes everything. I have used this in another workbook and it works
OK.

I have also tried filtering the data and only showing the blanks in column A
and then deleting visible rows, but I get an error message that it is too
complex to do.

Thanks in advance
Neil

 

[David McRitchie]

Hi Neil,
The reason your macro runs slow is because you did not turn
off screen updating and turn off calculation. See
Slow Response ...
http://www.mvps.org/dmcritchie/excel/slowresp.htm

But in answer to your question there is a faster way without a
loop and any need to turn off screen updating or calculation
based on the following:.

This macro will delete the empty cell cells in a selection without loops.

Sub DelEmpty()
'Matt Neuburg, PhD http://www.tidbits.com/matt Aug 3, 1998
Selection.SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)
End Sub

For your request:
You want to delete the entire row based on column A having empty cells

Sub DelRows_on_EmptyA()
'modified from Matt Neuburg, PhD http://www.tidbits.com/matt Aug 3, 1998
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete (xlShiftUp)
End Sub


Delete Cells/Rows in Range, based on empty cells, or cells with specific values
http://www.mvps.org/dmcritchie/excel/delempty.htm

 

[Neil Eves]

David,
Thanks for the help with the loop, but as for the special cells, it still
deletes all rows not just the ones with a blank cell in column A, any ideas.

Neil

Hi Neil,
The reason your macro runs slow is because you did not turn
off screen updating and turn off calculation. See
Slow Response ...
http://www.mvps.org/dmcritchie/excel/slowresp.htm

But in answer to your question there is a faster way without a
loop and any need to turn off screen updating or calculation
based on the following:.

This macro will delete the empty cell cells in a selection without loops.

Sub DelEmpty()
'Matt Neuburg, PhD http://www.tidbits.com/matt Aug 3, 1998
Selection.SpecialCells(xlCellTypeBlanks).Delete (xlShiftUp)
End Sub

For your request:
You want to delete the entire row based on column A having empty cells

Sub DelRows_on_EmptyA()
'modified from Matt Neuburg, PhD http://www.tidbits.com/matt Aug 3, 1998

Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

(xlShiftUp)
End Sub


Delete Cells/Rows in Range, based on empty cells, or cells with specific values
http://www.mvps.org/dmcritchie/excel/delempty.htm

--
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Hi,

I have a sheet that I have imported data to, it contains approx 35000 rows
and 7 columns.
10000 of these rows either have a empty cell in column A or has text in it
and I want to delete the entire row
Is there a quicker way then checking each cell in column A and then
deleteing that row.

Sub CheckForNumber()
Dim Lastrow As Long
Dim i As Long
Lastrow = Cells(65536, 1).End(xlUp).Row + 1
For i = Lastrow To 1 Step -1
Range("a" & i).Activate
If Not IsNumeric(ActiveCell.Value) Or IsEmpty(ActiveCell) Then
ActiveCell.EntireRow.Delete
End If
Next
End Sub

I have tried using the following to delete the empty cells

'On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'On Error GoTo 0

but it deletes everything. I have used this in another workbook and it works
OK.

I have also tried filtering the data and only showing the blanks in column A
and then deleting visible rows, but I get an error message that it is too
complex to do.

Thanks in advance
Neil

 

[David McRitchie]

Hi Neil,
The macro to remove all rows that are blank in Column A works fine
for me on Excel 2000. If it does not work for you, I would expect you are
running Excel 95 and it is mentioned that these macros will not work on
Excel 95. With Excel 95 you would need a macro like your original
macro with the extra coding to make run faster.
http://www.mvps.org/dmcritchie/excel/delempty.htm#emptyrows

If it is not Excel 95 or earlier that is the problem then expect you might not
be scrolled all the way to the left where Column A is visible.

Sub DelRows_on_EmptyA()
'modified from Matt Neuburg, PhD http://www.tidbits.com/matt Aug 3, 1998
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete (xlShiftUp)
End Sub

 

[Neil Eves]

David,

I am using Excel 2000 and I have two sheets that the data is imported to
from two different dat files(Mainframe generated), it works Ok on the first
sheet but not on the second.
I have another workbook importing similar data into two sheets and it works
OK there. I am at a loss as to why it doesn't work on this one sheet.

Neil

 

[John Carter]

Neil,

Try manually selecting column A then enter edit--goto--special
cells--blanks. Then scroll down and see which cells are actually selected.
Sometimes imported text files appear to have data but are actually blank.

Hope this helps
John

 

[Neil]

After a lot of trials by copying data to a new sheet and then running the
macro

Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

It appears that it will only work with a maximum of 10148 blank cells in
column A of my worksheet after that it deletes all data in the worksheet.

I ran it with 30260 rows with 10148 blank cells and it worked
I ran it with 30261 rows with 10149 blank cells and it did not work
I ran it with 30261 rows with 10148 blank cells and it worked

If I create a new sheet and go down to A10149 and enter some data in the
following cells in column a it appears to work.
If I put data in every second cell in column A down to row 25000 it deletes
everything.
Is this a limit or is it just that it is too complex to work?

Neil