Hi all
I have a large worksheet (~60,000 rows) that includes mostly numeric
data and some text rows. I need to delete entire rows that have text
in the cell at column A.
I wrote the following code that selects column A (MyRange) and deletes
the entire row if a text value if found. it works fine, but extremely
slow (~10 min):

Sheets(My_Sheet).Select
MyRange.Select
MyRange.SpecialCells(xlCellTypeConstants,
xlTextValues).EntireRow.Delete

I suspected there is a memory issue working with such a large range at
once, so I tried looping every 100 rows. At first it worked realy
fast, but started to slow again until it finaly took the same amount
of time as before.
If this is a memory issue, is there a way to free the memory inside
the loop? If not, is the a more efficient way to do this?

Thanks,
Ilan

Try the following:

Sub RemRows()
Dim cl as Range
Application.ScreenUpdating=False
Worksheets("My_Sheet").Activate
For Each cl in Range("MyRange")
cl.SpecialCells
(xlCellTypeConstants,xlTextValues).EntireRow.Delet e
Next
Application.ScreenUpdating=True
End Sub

ojv
-----Original Message-----
Hi all
I have a large worksheet (~60,000 rows) that includes
mostly numeric
data and some text rows. I need to delete entire rows
that have text
in the cell at column A.
I wrote the following code that selects column A
(MyRange) and deletes
the entire row if a text value if found. it works fine,
but extremely
slow (~10 min):

Sheets(My_Sheet).Select
MyRange.Select
MyRange.SpecialCells(xlCellTypeConstants,
xlTextValues).EntireRow.Delete

I suspected there is a memory issue working with such a
large range at
once, so I tried looping every 100 rows. At first it
worked realy
fast, but started to slow again until it finaly took the
same amount
of time as before.
If this is a memory issue, is there a way to free the
memory inside
the loop? If not, is the a more efficient way to do
this?

Thanks,
Ilan
.

I believe the method you are using is the fastest. I doubt you would gain
anything by breaking it into pieces. Certainly the suggestion of doing it
one row at a time I wouldn't think would be an improvement. Plus, as
written it would error out on the first row that doesn't get deleted.

You should be aware that specialcells can not select more than 8192 areas.

If your rows contain formulas or you have other sheets that reference data
in this sheet, then you might make sure you have set calculation to manual.

If you want to pursue your 100 at a time, you might want to work from the
bottom up, but I think it is just going to take some time to delete a lot of
rows on a sheet that big although 10 minutes doesn't sound right.

--
Regards,
Tom Ogilvy


"Ilan" wrote in message
m...
Hi all
I have a large worksheet (~60,000 rows) that includes mostly numeric
data and some text rows. I need to delete entire rows that have text
in the cell at column A.
I wrote the following code that selects column A (MyRange) and deletes
the entire row if a text value if found. it works fine, but extremely
slow (~10 min):

Sheets(My_Sheet).Select
MyRange.Select
MyRange.SpecialCells(xlCellTypeConstants,
xlTextValues).EntireRow.Delete

I suspected there is a memory issue working with such a large range at
once, so I tried looping every 100 rows. At first it worked realy
fast, but started to slow again until it finaly took the same amount
of time as before.
If this is a memory issue, is there a way to free the memory inside
the loop? If not, is the a more efficient way to do this?

Thanks,
Ilan

And one more thing to do--if you've done a print/printpreview, you'll notice
those dotted lines that excel uses to show you where the pages are.

Maybe by hiding those lines, it'll speed excel up--something like:

sheets(My_sheet).DisplayPageBreaks = False

near the top of your code.

Ilan wrote:

Hi all
I have a large worksheet (~60,000 rows) that includes mostly numeric
data and some text rows. I need to delete entire rows that have text
in the cell at column A.
I wrote the following code that selects column A (MyRange) and deletes
the entire row if a text value if found. it works fine, but extremely
slow (~10 min):

Sheets(My_Sheet).Select
MyRange.Select
MyRange.SpecialCells(xlCellTypeConstants,
xlTextValues).EntireRow.Delete

I suspected there is a memory issue working with such a large range at
once, so I tried looping every 100 rows. At first it worked realy
fast, but started to slow again until it finaly took the same amount
of time as before.
If this is a memory issue, is there a way to free the memory inside
the loop? If not, is the a more efficient way to do this?

Thanks,
Ilan

--

Dave Peterson

Ilan,

In addition to the points mentioned by Tom and Dave, yet a
bit mo

SpecialSelect gets disproportionately slower with
increased non-contiguous areas, over say 1000. If that's
likely to be a possibility many times per 100 rows then
reduce the 100.

Need error handling in case no cells are found.

Depending on how many rows you are deleting it could well
take a while. I think it's normal to expect each loop to
take increasingly longer as more "kept" rows are
being "moved" each time.

Something for you to experiment with:

Sub DelTextRows()
Dim nrow As Long, ncol As Long

'change to ref MySheet/MyRange, also qualify Ranges below

With ActiveSheet.UsedRange
nrow = .Rows(.Rows.Count).Row
ncol = .Columns(.Columns.Count).Column
End With

On Error Resume Next
'ActiveSheet.DisplayPageBreaks = False 'Dave Peterson

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual 'Tom Ogilvy

While nrow 100
'(2,2) = (xlCellTypeConstants, xlTextValues)
Range(Cells(nrow - 99, 1), Cells(nrow, ncol)) _
..SpecialCells(2, xlTextValues).EntireRow.Delete

nrow = nrow - 100
Application.StatusBar = nrow
'resetting UR appears to speed up a little
ActiveSheet.UsedRange

Wend

If nrow Then
Range(Cells(1, 1), Cells(nrow, ncol)) _
..SpecialCells(2, 2).EntireRow.Delete
End If

ActiveSheet.UsedRange
'ActiveSheet.DisplayPageBreaks = True
Application.StatusBar = False
'maybe capture/restore original calc
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False

End Sub

Sub TestSample()
For r = 1 To 50075 Step 2
Cells(r, 1) = "a"
Cells(r + 1, 1) = r
Next
End Sub


-----Original Message-----
Hi all
I have a large worksheet (~60,000 rows) that includes
mostly numeric
data and some text rows. I need to delete entire rows
that have text
in the cell at column A.
I wrote the following code that selects column A
(MyRange) and deletes
the entire row if a text value if found. it works fine,
but extremely
slow (~10 min):

Sheets(My_Sheet).Select
MyRange.Select
MyRange.SpecialCells(xlCellTypeConstants,
xlTextValues).EntireRow.Delete

I suspected there is a memory issue working with such a
large range at
once, so I tried looping every 100 rows. At first it
worked realy
fast, but started to slow again until it finaly took the
same amount
of time as before.
If this is a memory issue, is there a way to free the
memory inside
the loop? If not, is the a more efficient way to do this?

Thanks,
Ilan
.

Thank you all for your help.
I added the "Application.Calculation = xlCalculationManual " Tom
suggested and looping from the bottom-up and it did improve, though
not a lot.
However, when I run the macro a second time, it starts very slow right
from the begining. So I have to close the file and re-open it.

I'm no expert in Excel, but it looks as if the application reserves a
lot of resources for the file and frees them only after the file is
closed, something like the Clipboard or Undo. If this makes any sense,
is there a way to disable them?

Ilan

A follow on the subject:
Following CHORDially's advice, I solved the issue by filtering my data
(using IsNumber True/False) and then copying the selected range to a
new sheet. This way you avoid multiple rows being deleted and jamming
Excel for long minutes.
Here is the sub I used:
---------------------------------------------------
Sub MoveNumRows()
' Moves only rows with number in sheet3-column A to sheet4
Dim My_Range As Range
Dim Last_Row As Double
Dim Rng As String

Application.ScreenUpdating = False
Last_Row = Find_Last_Row(Sheet3, 3, 1) 'Find the last row in
sheet3
Range("aa3").Formula = "=ISNUMBER(A3)"
Rng = "aa3"
Set My_Range = Range(Rng)
My_Range.Select
Selection.AutoFill Destination:=Range("aa3:aa" & Last_Row),
Type:=xlFillDefault
Range("aa3").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="TRUE"
Range("A2:y2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sheet4").Select
Range("A1").Select
ActiveSheet.Paste
Application.ScreenUpdating = True
End Sub
---------------------------------------------------
Thanks all,
Ilan

(Ilan) wrote in message . com...
Thank you all for your help.
I added the "Application.Calculation = xlCalculationManual " Tom
suggested and looping from the bottom-up and it did improve, though
not a lot.
However, when I run the macro a second time, it starts very slow right
from the begining. So I have to close the file and re-open it.

I'm no expert in Excel, but it looks as if the application reserves a
lot of resources for the file and frees them only after the file is
closed, something like the Clipboard or Undo. If this makes any sense,
is there a way to disable them?

Ilan