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?
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
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.
'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.
'"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
.AutoFilterMode = False
'Apply the filter
.Range("A1:A" & .Rows.Count).AutoFilter Field:=1, Criteria1:=DeleteValue
.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
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
.AutoFilterMode = False
End With
With Application
.ScreenUpdating = True
.Calculation = calcmode
End With
.ScreenUpdating = True
.Calculation = calcmode
End With
End Sub
No comments:
Post a Comment