Deleting Rows with Blank Cells - Big Excel File

[Johnny Meredith]

I know this is a common question, and I have looked at past posts, but
I can't seem to make it work.

I have an Excel file developed from a text import. It has approx.
50,000 rows that represent 25,000 records (records are on two rows).
I'm trying to eventually get to Access, but using Excel to clean up
data. The final piece of code to write simply deletes every row where
cell A is empty.

I've tried two solutions. This is the first one:

For i = rng1.Rows(rng1.Rows.Count).Row To 1 Step -1
If IsEmpty(rng(i)) = True Then
rng(i).EntireRow.Delete
End If
Next

For some reason, the code is "interrupted" at the End If line, but it
does delete about 5,000 rows before it farts.

The second solution is:

Set rng = Range(Cells(1, "A"), Cells(Rows.Count, "A").End(xlUp))
Set rng = rng.SpecialCells(xlBlanks)
rng.EntireRow.Delete

I get the same error, but on line 3 of above (the cause is the special
cells selection is too large). Also, this piece of code does not
delete a single row before it quits. So I think this is not a
solution at all.

I've read some stuff that says to cycle through ranges backwards when
deleting rows to avoid these errors (which I've done in #1 above).
Also, that Excel gets "ahead of itself" and I need to tell the code to
"wait." (I don't have a clue what that means!) Your help is greatly
appreciated, either with improving the above code, or doing something
completely different. Also, I must do it programmtically.

Aside - anyone have any ideas on reading a fixed width text file line
by line and avoiding the whole manual import mess in the first place.
I'll warn you: these text files are terrible - no consistency.

 

[Ron de Bruin]

Try this one for column A on the Activesheet
If the Inputbox show up press OK

Sub Delete_Rows_Active_Sheet()
Dim FindString As String
Dim iRow As Long
Dim LastRow As Long
Dim FirstRow As Long
Dim CalcMode As Long

FindString = Application.InputBox("Enter a search word")
'If Trim(FindString) = "" Then Exit Sub

With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

FirstRow = 1

With ActiveSheet
.DisplayPageBreaks = False
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = LastRow To FirstRow Step -1

If IsError(.Cells(iRow, "A").Value) Then
'Do nothing
'This avoid a error if there is a error in the cell
ElseIf .Cells(iRow, "A").Value = FindString Then
.Rows(iRow).Delete
End If
Next iRow
End With

With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub

 

[Johnny Meredith]

Ron,

Thanks for the help. It works. However, it is extremely slow. Is
there another approach that will work faster?

 

[David McRitchie]

See delempty.htm page on my site for more details.
You asked for those that are blank (empty) in column A to
have their rows deleted therefore no need for the inputbox.
This has no loops should run very fast, a cell with a space
or a formula is not empty.

sub delrowswithblankincolumna()
On Error Resume Next ' In case there are no blanks
Columns("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
ActiveSheet.UsedRange 'Resets UsedRange for Excel 97
end sub

See how many times you can say Donald Duck before it completes.

 

[Ron de Bruin]

Hi John

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

You can have a problem with this one??
http://support.microsoft.com/default.aspx?scid=kb;en-us;832293

--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

 

[David McRitchie]

Hi John and Ron,
That MS KB article was a shock, but try this:

Sub del_COLA_empty()
'D McRitchie http://www.mvps.org/dmcritchie/excel/delempty.htm 2004-01-10
'based on Matt Neuburg, PhD http://www.tidbits.com/matt Aug 3, 1998
'Loop required due to MS KB http://support.microsoft.com/?kbid=832293
Dim i As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'pre XL97 xlManual
i = Cells.SpecialCells(xlCellTypeLastCell).Row
For i = i To 1 Step -8000
On Error Resume Next 'in case there are no blanks
Range(Cells(Application.WorksheetFunction.Max(1, i - 7999), 1), _
Cells(Application.WorksheetFunction.Max(i, 1), 1)). _
SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
Next i
Application.Calculation = xlCalculationAutomatic 'pre XL97 xlManual
Application.ScreenUpdating = True
ActiveSheet.UsedRange 'Resets UsedRange for Excel 97
End Sub

generated data for testing, about the worst case scenario,
every other row makes for most discontiguous cells possible.
A1: A1
B1: 1
A2: (empty)
A3: A3
A4: (empty)

Select B1:B20000, Edit, Fill, Series
Select A1:A4, double click on fill handle

 

[Ron de Bruin]

Hi

That MS KB article was a shock, but try this

For me to David when I notice it

Ther limit is for all Specialcells options
The KB will be changed with this soon



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

 

[David McRitchie]

Excel VBA should at least provide a fix identifying a return failure code.
Deleting the entire content of a sheet with a macro with loads of
data is not something one should be expecting, when the macro
works for normal test data and almost all worksheets.

 

[Ron de Bruin]

Hi David

Excel VBA should at least provide a fix identifying a return failure code.
Deleting the entire content of a sheet with a macro with loads of
data is not something one should be expecting, when the macro
works for normal test data and almost all worksheets.

You are right about that David
The developers are aware of it now so I hope they fix it.

I have said some things that I wil not repeat when
I notice it the first time and lost all my data<g>

 

[Johnny Meredith]

Check this out: This is the fastest solution of them all so far (it
runs in seconds):

Public Sub FasterStepFive()

Dim lngRow As Long
Dim lngLastRow As Long
Dim lngFirstRow As Long
Dim lngCalcMode As Long
Dim rng As Range
Dim intPutIt As Integer

With Application
lngCalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

lngFirstRow = 1
intPutIt = 14

lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For lngRow = lngLastRow To lngFirstRow Step -1
If lngRow Mod 2 <> 0 Then
Cells(lngRow, intPutIt) = lngRow
End If
Next lngRow

Set rng = Range(Cells(lngFirstRow, 1), Cells(lngLastRow, intPutIt))

With rng
.Sort Range("N1"), xlAscending
.Range("N1").EntireColumn.Delete
End With

With Application
.ScreenUpdating = True
.Calculation = lngCalcMode
End With
End Sub

As you can see, it places a value in cells in column N (where there is
no data), then sorts on that range. The blanks fall to the bottom, so
I need some code in there to eliminate what falls out. Any
suggestions? I'm going to loosly couple this to get rid of the hard
coding for the column, and this little piece of code might come in
handy later!

 

[Dave Peterson]

If the cells are really blank, not formulas that evaluate to "" (or even
formulas that evaluated to "" that were later converted to values!), then you
can add:

lngLastRow = cells(rows.count,"N").end(xlup).row
rows(lnglastrow + 1 & ":" & rows.count).delete

I had a routine the did similar stuff as yours. Instead of using column N, I
inserted new columns A and B (after checking to see if less than 255 columns
were used).

In one of the columns I put the row number:

with activesheet
with .range("a1:a" & lngLastrow)
.formula = "=row()"
.value = .value
end with
.range("b1").value = "x"
.range("b1:b2").autofill _
destination:=.range("b1:b" & lngLastRow), type:=xlfilldefault
end with

Then sorted by column B, deleted the blanks, and resorted by column A (may not
have been necessary) and deleted columns A&B (and tried to reset the last used
cell.)

But this can have its own trouble, too. If you have objects on your worksheet,
they may not have been set to move and size with cells. So you can't use this
kind of thing all the time.