Hi All,
I am having some trouble with the following code , there run time error 1004: is right where it says WSZ.Select
can anyone tell me what is wrong?
Sub DeleteNoVendors()
Dim WSZ As Worksheet
Dim EndRow4 As Long
Dim LastRow As Long
EndRow4 = Worksheets("CRM Data").Range("B" & Rows.Count).End(xlUp).Row
LastRow = Worksheets("CRM Data").Cells(Rows.Count, 1).End(xlUp).Row
Set WSZ = Worksheets("CRM Data")
WSZ.Select
WSZ.Range("A1") = "Property-HOA ID"
WSZ.Range("B1") = "Property ID"
WSZ.Range("E1") = "Vendor ID"
WSZ.Range("G1") = "Payment Amount"
WSZ.Range("H1") = "Payment Terms"
WSZ.Range("J1") = "Invoice Edit"
WSZ.Range("K1") = "Inv#"
WSZ.Range("J2") = "=IF(LEFT(E2,1)="" "",RIGHT(E2,LEN(E2)-1),E2)"
WSZ.Range("J2").Copy
WSZ.Range("J3:J" & EndRow4).Select
WSZ.Paste
WSZ.Range("K2").Value = "=IF(LEFT(E2,2)=""V0"",E2,""Delete"")"
WSZ.Range("K2").Copy
WSZ.Range("K3:K" & EndRow4).Select
WSZ.Paste
WSZ.UsedRange.Value = WSZ.UsedRange.Value
WSZ.Range("K2:K" & EndRow4).Copy
WSZ.Range("E2:E" & EndRow4).Select
WSZ.Paste
With WSZ
.AutoFilterMode = False
With Range("A1:K" & LastRow) '--Assuming your header is in Row 2 and records start at Row 3.
.AutoFilter Field:=5, Criteria1:="Delete" '--Field:=3 is Column C if data starts at A
.Cells.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete '--Delete the visible ones.
End With
End With
WSZ.AutoFilterMode = False
Application.CutCopyMode = False
End Sub
Bookmarks