efficient code to remove unwanted rows

[Godders199]

Hello,, i am currently using the folliwng vba to remove unwanted rows, however as the data grows the refresh time is not very fast.

iLastRow = Cells(Rows.Count, "g").End(xlUp).Row
For i = iLastRow To 1 Step -1
If Cells(i, "j").Value = "xxx" Then
Rows(i).delete

End If
Next i

I am also having to remove rows with values "zzz" now. so have just duplicated this code.

Is there a more efficent way to remove these rows in a timely manner?

 

[njimack]

It's quicker if you delete the content of the cells and then delete the rows in en masse...

Sub x()

Dim iLastRow As Long
Dim c As Range


Application.ScreenUpdating = False


iLastRow = Cells(Rows.Count, "g").End(xlUp).Row


For Each c In Range(Cells(1, 10), Cells(iLastRow, 10))
    If c = "xxx" Or c = "zzz" Then c.ClearContents
Next c


Range(Cells(1, 10), Cells(iLastRow, 10)).SpecialCells(xlCellTypeBlanks).EntireRow.Delete


Application.ScreenUpdating = True


End Sub

 

[vcoolio]

Hello Godders,

It's quicker if you delete the content of the cells and then delete the rows in en masse...

Ditto what Neil says, but here's another method using autofilter which could work for you:-

Sub DeleteIt()

Dim ar As Variant
ar = Array("xxx", "zzz")

For i = 0 To UBound(ar)
    With Sheet1.[A1].CurrentRegion
        .AutoFilter 7, ar(i)
        .Offset(1).EntireRow.Delete
        .AutoFilter
    End With
Next i

End Sub

Should you expect that the array values could increase in number then you could create a list of all values in another sheet and reference them from there. The list can grow/shrink as you please.

I hope that this helps.

Cheerio,
vcoolio.

 

[Fazza]

It's quicker if you delete the content of the cells and then delete the rows in en masse...

there is still, Neil, a loop 'for each c in Range'. consider instead using Replace
RangeReference.replace what:="xxx", replacement:=vbNullString
RangeReference.replace what:="zzz", replacement:=vbNullString

Godders, if there is a lot of data and it suits what you're doing. it is faster to sort the data on the field of xxx, zzz, or blanks before doing the deletion. Cause when the rows being deleted are not contiguous it is very slow on large datasets

 

[Godders199]

thanks all, will give it a go..

appreciate sort and delete might be quicker, but the vba pulls the data from set reports , maybe i need to go back to the source reports and see if these can be changed,

 

[Peter_SSs]

I disagree with replacing the values and then deleting rows with blanks as that would also delete rows that were originally blank in column G, if any, not just the ones that had "xxx" or "zzz".

Here is a way that does involve sorting but also does much of the work in memory (faster) and results in just a single block of rows being deleted. You should find it very fast but how noticeable that will be depends somewhat on how many rows of data you have to start with, what proportion of those rows would need to be removed and how many disjoint sets of rows that deletion involves.

Don't be put off by the sorting that this code does as the rows that do not get deleted will be in the same relative order that they were before this code is run.

Sub Del_xxx_zzz()
  Dim a, b
  Dim nc As Long, i As Long, k As Long
 
  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  a = Range("G1", Range("G" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    If a(i, 1) = "xxx" Or a(i, 1) = "zzz" Then
      b(i, 1) = 1
      k = k + 1
    End If
  Next i
  If k > 0 Then
    Application.ScreenUpdating = False
    With Range("A1").Resize(UBound(a), nc)
      .Columns(nc).Value = b
      .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
      .Resize(k).EntireRow.Delete
    End With
    Application.ScreenUpdating = True
  End If
End Sub

 

[Godders199]

Thanks Peter, just trying this .
I changed the following to "J" as thats the column with the XXX ZZZ
a = Range("j1", Range("j" & Rows.Count).End(xlUp)).Value

but i get a runtime error against

.Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Do I need to change
With Range("A1").Resize(UBound(a), nc)

to J1 as well. column A is blank

thanks

 

[ZVI]

Sub Del_xxx_zzz()
  Dim a, b
  Dim nc As Long, i As Long, k As Long
 
  nc = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, SearchFormat:=False).Column + 1
  ' ...
End Sub

Hi Peter,

In case last column in the used range is hidden it's data will be destroyed by temporary filter condition values.
Find of "*" with LookIn:=xlFormulas is more safety because it counts hidden columns/rows too.

Regards

 

[Peter_SSs]

but i get a runtime error against

.Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

What is the full error message?

Do you have any merged cells?

Hi Peter,

In case last column in the used range is hidden it's data will be destroyed by temporary filter condition values.
Find of "*" with LookIn:=xlFormulas is more safety because it counts hidden columns/rows too.

Regards

Hi Vlad
Good suggestion - I will try to remember to use that in the future.