Any delete rows macro faster than this one???

[nancy]

I am using this macro below to delete blank rows. It
works well, but it takes about 3-5 min to clean up blank
cells of about 21777 cells. This includes cells with
data. I have tested all other macro on the net and so far
this is the fastest. I also, think that all of the macro
is going beyond the 21777 cells. And this should be the
last cell with data. There is a second part of this macro
that was given to me earlier, but this is not the
problem. When i ran them independently, the second macro
only cleans within the range. I will appreciate and
suggestion or code that is faster than this one. Thanks
in advance.

Sub delete()
Dim Lrow As Long
Dim CalcMode As Long
Dim rng As Range, i As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

With ActiveSheet
.DisplayPageBreaks = False
For Lrow = .UsedRange.Rows.Count To 1 Step -1
If Application.CountA(.Rows(Lrow)) = 0
Then .Rows(Lrow).delete
'This will delete the row if the whole row is
empty (all columns)
Next
End With
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
On Error Resume Next
Set rng = Range("A1:g21770").Rows
For i = 1 To rng.Count
rng(i).Find("John", , , xlWhole).Cut rng(i).Cells(1)
Next

End Sub

 

[David McRitchie]

There is no need to check every cell to see
if all cell in the range A:G of a row are empty.
Pick the column most likely to have data and
check that it is blank and if it is then
check the other columns with COUNTA per row.
---
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

"nancy" <[email protected]> wrote

 

[Jim Cone]

Nancy,

I assume you want to remove all blank rows above the last row with data.
If that is the case then there is a flaw in the code.
The count of rows in the used range does not always give you the last row
with data. For instance the used range could be A11100 and the used
range rows count would be 90. Starting at Rows(90) would skip the last
ten rows. On the other hand, the last row of the used range could be
thousands of rows below the actual last row with data.
The following function will determine the last row with data, if there are no
hidden rows...

'======================================
' Returns the number of the last worksheet row with data.
' Returns 0 if the sheet is blank.
'======================================
Function GetBottomRow(ByRef objSheet As Worksheet) As Long
On Error GoTo NoRow
GetBottomRow = objSheet.Cells.Find(What:="*", _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
Exit Function
NoRow:
GetBottomRow = 0
End Function
'======================================

'You can call the function in your code like this...

Lrow = GetBottomRow(ActiveSheet)

'The heart of your code then would look something like this...
Dim Rw as Long
ActiveSheet.DisplayPageBreaks = False
For Rw = Lrow to 1 Step -1
If Application.CountA(Rows(Rw)) = 0 Then .Rows(Rw).Delete
Next

Regards,
Jim Cone
San Francisco, CA

 

[Cecilkumara Fernando]

Try this,
Sub Macro2()
Lrow = Range("A1").SpecialCells(xlLastCell).Row
With Range("H1")
..Select
..Formula = "=Counta(A1:G1)"
..AutoFill Destination:=Range("H1:H" & Lrow)
End With
Range("H1:H" & Lrow).Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Sort Key1:=Range("H1"), Order1:=xlDescending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("H1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Find(What:="0", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
End Sub

HTH
Cecil

 

[Ron de Bruin]

Hi Jim

For instance the used range could be A11100 and the used
range rows count would be 90.

I never notice that the usedrange.rows.count is smaller then the last row in the worksheet.

 

[Ron de Bruin]

I have some examples to check out
http://www.rondebruin.nl/delete.htm

The macro you use is not fast but always working.

 

[Ron de Bruin]

I mean this Jim

For Lrow = .UsedRange.Rows.Count To 1 Step -1

This will always loop through all rows on the worksheet
Maybe to many rows?
http://www.contextures.com/xlfaqApp.html#Unused

 

[Ron de Bruin]

My eyes are closed this morning Jim.

I see what you mean
I have always a header row at the top of the worksheet so you never notice this.

I use this two functions to check out the last row/column with data

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function

 

[Jim Cone]

Ron,
I just got up - it is 5:37 am here - and I don't think anything
is working for me yet.
One additional comment - to be absolutely positive (maybe) -
one should check for hidden rows before determining the last row.
Using the Auto-Filter can hide rows that will not be found by the
"Find" method, if for instance the filter hides the actual last row(s).

Regards,
Jim Cone
San Francisco, CA

My eyes are closed this morning Jim.
I see what you mean
I have always a header row at the top of the worksheet so you never notice this.
I use this two functions to check out the last row/column with data
Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function
Regards Ron de Bruin
(Win XP Pro SP-1 XL2000-2003)

- snip -

 

[Ron de Bruin]

One additional comment - to be absolutely positive (maybe) -

one should check for hidden rows before determining the last row.
Using the Auto-Filter can hide rows that will not be found by the
"Find" method, if for instance the filter hides the actual last row(s).

Good point Jim