Fastest way to delete rows in Excel 2

[BFG2]

I have an Excel spreadsheet with over 2000 rows of data and need to delete any rows that contain a 'x' in a certain column (approximately 2 in every 7). At the moment am looping through from the end of the data but it is sooooo slooooooooooow :-( Anybody got any better methods?

I've switched off autocalc and screen refresh to try and speed it up, but this still takes an age and a half (the machine I'm using isn't particularly fast)

PLEASE if someone can help....even if it's just to stop my monitor getting thrown on the floor out of sheer desperation ;-)

This is the code that I'm using at the moment:

Sub Remove_rows_with_x()
'Switch off auto calculate in Excel to speed up process
With Application
.Calculation = xlManual
.MaxChange = 0.001
.CalculateBeforeSave = False
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Application.ScreenUpdating = False
Sheet3.Select
Dim intCounter As Integer, intLastRow As Integer
intLastRow = Cells(Rows.Count, 3).End(xlUp).Row
For intCounter = intLastRow To 1 Step -1
If Not IsEmpty(Cells(intCounter, 3)) And _
Cells(intCounter, 3).Value = "x" Then
Rows(intCounter).Delete
End If
Next intCounter
Sheet1.Select

'Switch autocalculate back on
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Application.ScreenUpdating = True

End Sub

 

[combo]

Change the range to your own one, and try this way to search and delete:

[tt]Dim xRows As Range, c As Range
With ThisWorkbook.Worksheets(1).UsedRange
Set c = .Find("x", LookIn:=xlValues)
If Not c Is Nothing Then
Set xRows = c.EntireRow
firstAddress = c.Address
Do
Set xRows = Union(xRows, c.EntireRow)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
xRows.Delete[/tt]

combo

 

[TonyJollans]

Hi BFG2,

Don't know the code off the top of my head (just record yourself doing it to find out) but if you put a filter (&quot;contains x&quot on the column and then delete all visible rows you should be done.

Enjoy,
Tony

 

[BFG2]

Combo,

Thanks for your quick reply - and looks like just what I need. Unfortunately not having much success, most probably due to my lack of understanding!!

I've put it into a sub, and when run it comes up with 'Compile error, variable not defined' and points at &quot;firstaddress =&quot; on the line &quot;firstaddress = c.Address&quot;.
I've tried 'dim'ing it as a range, but am also coming up with Run time error 91, Object variable or with block variable not set, and highlights 'xRows.Delete'

Also, have I set my range correctly and referred to it properly in the code? Here's the code as it is now:

Sub Delete_Rows()
Dim xRows As Range, c As Range, firstaddress As Range, myrange As Range
Set myrange = (&quot;D&quot
With myrange.UsedRange
Set c = .Find(&quot;x&quot;, LookIn:=xlValues)
If Not c Is Nothing Then
Set xRows = c.EntireRow
firstaddress = c.Address
Do
Set xRows = Union(xRows, c.EntireRow)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
End With

 

[BFG2]

Tony,

Nice idea, but when selecting rows that are visible, it also selects the rows that have been 'hidden' by the filter, so you would in theory delete all the data present.

Or have I got the wrong end of the stick?

Cheers.

BFG

 

[combo]

The address variable (firstaddress) is String.
BTW, to get rows, you can use c.Rows instead of c.EntireRow (whatever is faster).

combo

 

[RobBroekhuis]

If your data is sorted, or if the order of rows doesn't matter, you could sort on column C, after which all rows to be deleted will be in one block (you'd have to search for the beginning and end of the &quot;x&quot;'s). Deleting many lines at once is much faster than one by one. Then you may have to resort to get back to the original order. If there is no predefined sort criteria, then you can maintain the previous order by putting an index number (equal to the row number) in a temporary column.
I'm not sure that doing two sort operations would be faster than what you have now, but it's worth a try.


Rob

 

[combo]

I've just noticed the you try to use UsedRange - it is a worksheet property an probably will not work in this context. So rather use (for &quot;D&quot; column, intLastRow as Long):
intLastRow = Cells(65536, 4).End(xlUp).Row
set myrange=ThisWorkbook.Worksheets(1).Range(Cells(1, 4), Cells(intLastRow, 4))
With myrange
...

combo

 

[TonyJollans]

Hi BFG2,

Have you tried with a filter because it works for me; it only deletes the visible rows.

quick and dirty test ...

[A2].AutoFilter Field:=3, Criteria:="*x*"
Cells.Delete

Enjoy,
Tony

 

[xlbo]

BFG2 - tony's autofilter route is definately the way to go - I know it's a bit scary having a delete on all rows but it will only actually delete the visible ones...just in case, make a backup and try this:

sub Delx()
lRow = Cells(65536, 3).End(xlUp).Row
Range(&quot;A1:Z&quot; & lRow).AutoFilter Field:=3, Criteria1:=&quot;x&quot;
Rows(&quot;2:&quot; & lRow).Delete Shift:=xlUp
Selection.AutoFilter Field:=3
end sub

change the Z to reflect the right most column of data and you should be well away

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[BFG2]

Everyone,

Thanks for your replies. Tony apologies, I tried it again, after sending that post and it worked fine, however still quite slowly (but better than what I had first of all)

However, a star must go to Rob and Combo, as using Robs technique is mega fast and I then combined this technique with Combo's solution to get something that completes in about 15 seconds rather than 4 (yes 4) minutes.

Cheers. As always it's great to see a community that helps increase peoples knowledge like this!

BFG.