Loop Each Tab and Delete Rows

temerson

New Member
Joined
Apr 22, 2019
Messages
17
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

Well-known Member
Joined
Dec 3, 2018
Messages
7,891
Office Version
2007
Platform
Windows
Change

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
41,792
Office Version
365
Platform
Windows
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.

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.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,891
Office Version
2007
Platform
Windows
You're welcome, thanks for commenting.
 

Forum statistics

Threads
1,078,367
Messages
5,339,783
Members
399,323
Latest member
letitiaysk

Some videos you may like

This Week's Hot Topics

Top