Vba Code to Delete Contract Rows in Spreadsheet

Question

I have a spreadsheet set to 'read only' with about 8000 rows containing details of items on various contracts (and some other random data). There are anywhere between 50 - 300 items listed against each contract and I have to regularly run reports for various contracts. Currently I open the spreadsheet and filter by contract in column 'D' and delete all the other rows except for the contract I want to keep.

I want to automate this using a macro and vba code so as when I run the macro it displays an InputBox and asks for the contract name that I want for the report. When I enter the contract name the routine will delete all the other contract rows and any other rows of data that do not contain the contract name specified in the InputBox (in column 'D').

I am very new to vba and have tried to put the below code together but I know its not correct and not working (it is actually looking for the string "strName" and not the InputBox value and I also want it to delete everything except the InputBox value).

Sub Contract_Selection()

Dim strName As String    
strName = InputBox("Enter Contract Name.", "CONTRACT SELECTION")
If strName = vbNullString Then Exit Sub

With ActiveSheet
    .AutoFilterMode = False
    With Range("d1", Range("d" & Rows.Count).End(xlUp))
        .AutoFilter 1, "strName"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With

End Sub

Can anyone help with the correct code to do the above mentioned?

Thanks Ian

Jane Jane · Accepted Answer · 2015-07-04T10:55:23

When using variables, the variable name is used without quotes ie strName rather than "strName".

The other problem here is that you are filtering to show the values matching that string then deleting those items. You need to show all the items which don't match the string.

A good tip for getting the correct syntax is to record a macro where you do the required AutoFilter, then look at the code that was recorded. This would show the filter is in the form "<>ContractId". To use this in your macro, you would need to use Concatenation to join "<>" with your variable. In VBA, concatenation uses an ampersand as follows: NewVariable = "SomeString" & OtherVariable & "SomethingElse" You can mix variables and fixed strings as needed.

This should work:

Sub Contract_Selection()
    Dim strName As String
    strName = InputBox("Enter Contract Name.", "CONTRACT SELECTION")
    If strName = vbNullString Then Exit Sub

    With ActiveSheet
        .AutoFilterMode = False
        With Range("d1", Range("d" & Rows.Count).End(xlUp))
            .AutoFilter 1, "<>" & strName
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
    End With
End Sub

Vba Code to Delete Contract Rows in Spreadsheet

1 Answers