Loop Each Tab and Delete Rows

[temerson]

I have the following code:

Sub delete_dc_row()


Dim wbCurrent As Workbook
Dim wsCHINO As Worksheet
Dim wsSTOCKTON As Worksheet
Dim wsFLM As Worksheet
Dim wsDGV As Worksheet
Dim wsAURORA As Worksheet


Dim nLastRow, i As Integer


Set wbCurrent = ActiveWorkbook
Set wsCHINO = wbCurrent.Worksheets("CHINO")


nLastRow = wsCHINO.Cells.Find("*", LookIn:=xlValues, searchorder:=xlByRows, searchdirection:=xlPrevious).Row


For i = nLastRow To 12 Step -1
If InStr(1, wsCHINO.Cells(i, 6).Value, "C ", vbTextCompare) > 0 Then
Else
wsCHINO.Columns(i).Delete Shift:=xlShiftUp
End If
Next i




End Sub

The goal is to look in column F and search for and delete rows that do not contain "C ".

Thank you in advance!

 

[DanteAmor]

Change

wsCHINO.Rows(i).Delete Shift:=xlShiftUp

 

[Peter_SSs]

The goal is to look in column F and search for and delete rows that do not contain "C ".

Instead of looping through each individual row, you could delete them all at once by making this substitution in your code.

<del>For i = nLastRow To 12 Step -1
If InStr(1, wsCHINO.Cells(i, 6).Value, "C ", vbTextCompare) > 0 Then
Else
wsCHINO.Rows(i).Delete Shift:=xlShiftUp
End If
Next i</del>

With wsCHINO.Range("F11:F" & nLastRow)
  .AutoFilter Field:=1, Criteria1:="<>*C *"
  .Offset(1).EntireRow.Delete
  .AutoFilter Field:=1
End With

Also, if you have a very large set of data (tens of thousands of rows) a longer but faster code could be provided.

 

[temerson]

Thank you guys!! This really help me out.

 

[DanteAmor]

You're welcome, thanks for commenting.

 

[Peter_SSs]

Thank you guys!! This really help me out.

You're welcome. Glad you got something useful.