Sunday, 15 January 2017

Delete rows based on criteria using an input box

Today I was asked the following question


I was on your course the other day, and was wondering if you could help.

say i have a table of data, and i want a button to delete certain rows if column a begins with a certain letter is that possible? 


Bannaa1
4
4
1
Bannana2
4
apple3
4
apple2124
4
4
water1
4
4
water2
3
4
biscuit
1
2
4





so say i wanted to delete every row, if column a started with the letter B is that possible?




cheers Charlie

Only to glad to help Charlie as I said on the course we offer support after the course and we mean that.
So Here is the code to do that just copy and paste to a VBA module in excel the code searches column A for any Text beginning with the letter you specify on the input Box, if you forget and click ok a message will come up and abort the process leaving your data in tact



Sub Delete_with_Autofilter()
    Dim DeleteValue As String
    Dim rng As Range
    Dim calcmode As Long
   
   'sets the varible to accept data from inputbox
    Dim remove As Variant
    With Application
        calcmode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
    'Fill in the value that you want to delete
 
   
                'Tip: with AutoFilter you can also use wildcards.
                '"*ron"  End with ron
                '"ron*"  Start with ron
                '"*ron*" ron is a part of the string
                'Tip: use DeleteValue = "<>ron" to delete rows without ron
                'You can also use the wildcard ? for a single character.
                'Note: in the examples I use the range  .Range("A1:A" & .Rows.Count)
                'Remember that A1 of this range is your header cell.
   
    'invokes inputbox adds wild card to the end
    remove = InputBox("Enter the value to start with") & "*"
   
    'the if function is added to make sure you enter a start value if only wild card all data deleted
If remove = "*" Then
    MsgBox "Warning!!! no start value selected aborting", vbCritical, "Avoid Delete of All Data"
    Else
   
    'change the delete value below in this scenario remove is a variable that accepts data from input box
    DeleteValue = remove
End If
    'Sheet with the data, you can also use Sheets("MySheet")
    With ActiveSheet
        'Firstly, remove the AutoFilter
        .AutoFilterMode = False
        'Apply the filter
        .Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue
        With .AutoFilter.Range
            On Error Resume Next
            Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
                      .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rng Is Nothing Then rng.EntireRow.Delete
        End With
        'Remove the AutoFilter
        .AutoFilterMode = False
    End With
    With Application
        .ScreenUpdating = True
        .Calculation = calcmode
    End With
End Sub

No comments:

Post a Comment