I am confused by a problem I am having. I have a module that is just deleting rows based on criteria and one block seems to only work when the module is ran by itself but when it is ran during the whole process, it doesnt do anything.
Here is the full module:
The section that seems to be selective on when it runs is:
In the above block, num is a string whereas in the For Each above it, it is a number, but I dont think that should matter since it is a variant. Any idea why this only wants to work some of the time?
Here is the full module:
Code:
Public Sub Criteria()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Sheets("Config")
Dim OrderNum As Long: OrderNum = ws.Range("B9")
Dim ws2 As Worksheet: Set ws2 = wb.Sheets(OrderNum & " Report")
Dim ws3 As Worksheet: Set ws3 = wb.Sheets("Job Info")
Dim Customer As String
Dim Job As String
Dim PO As String
Dim c As Range
Dim arry As Variant
Dim arry2 As Variant
Dim lastrow As Long
Dim i As Long
Dim RowsToDrop As Variant
Dim num As Variant
With ws3.Range("A2:Z1000")
Set c = .Find(OrderNum, LookIn:=xlValues)
If c Is Nothing Then
MsgBox ("No job information entered in Job Info. No filters or header applied to order " & ws.Range("B9").Value)
Exit Sub
End If
Customer = c.Offset(1, 0)
Job = c.Offset(2, 0)
PO = c.Offset(3, 0)
arry = c.Offset(4, 0)
arry2 = c.Offset(5, 0)
End With
ws2.Range("C8").Value = Customer
ws2.Range("C9").Value = Job
ws2.Range("C10").Value = PO
RowsToDrop = Split(arry, ",")
lastrow = ws2.Cells(Rows.Count, 4).End(xlUp).Row
For Each num In RowsToDrop
For i = lastrow To 12 Step -1
If Cells(i, 1).Value Like num & "*" Then
Rows(i).Delete
End If
Next i
Next num
RowsToDrop = Split(arry2, ",")
lastrow = ws2.Cells(Rows.Count, 4).End(xlUp).Row
For Each num In RowsToDrop
For i = lastrow To 12 Step -1
If Cells(i, 5).Value = num Then
Rows(i).Delete
End If
Next i
Next num
End Sub
The section that seems to be selective on when it runs is:
Code:
RowsToDrop = Split(arry2, ",")
lastrow = ws2.Cells(Rows.Count, 4).End(xlUp).Row
For Each num In RowsToDrop
For i = lastrow To 12 Step -1
If Cells(i, 5).Value = num Then
Rows(i).Delete
End If
Next i
Next num
In the above block, num is a string whereas in the For Each above it, it is a number, but I dont think that should matter since it is a variant. Any idea why this only wants to work some of the time?