Jesseke
06-29-2020, 04:28 AM
Hello,
I have an Excel file with 3000 rows. Target is that I input a column (the column where Excel has to search in) in an inputbox and a name in another input box, the vba macro is analyzing the excel file and removes all the rows who are not fulfill the inserted criteria.
Somebody created already something for me, but the function like in the search function "match entire cell contents" is unusable in that code.
Sub DelRows() Application.ScreenUpdating = False
Dim a, b, nc As Long, i As Long, Col As String, response As String
Col = InputBox("Enter the column letter:")
response = InputBox("Enter the taxonomy:")
nc = Cells(1, Columns.Count).End(xlToLeft).Column + 1
a = Range(Col & "1", Range(Col & Rows.Count).End(xlUp)).Value
ReDim b(1 To UBound(a), 1 To 1)
For i = 1 To UBound(a)
If Not a(i, 1) Like "*" & response & "*" Then b(i, 1) = 1
Next i
With Range(Col & "1").Resize(UBound(a), nc)
.Columns(nc).Value = b
' .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
.Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=True, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
On Error Resume Next
.Columns(nc).SpecialCells(xlConstants).EntireRow.Delete
On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub
Imagine you have an Excel:
1 Tree
2 Trees
3 Trees; leaf
4 Tree; leaf
I want to have (like in script above):
An inputbox who is asking in which column has to be searched. (already written)
An inputbox who is asking WHAT I want to search (already written but do not 100% like I want because Tree and Trees are shown!)
The first row of the Excel must been frozen (= not deleted)
All the rows who are not fulfilling the input criteria of item 2 => delete these rows completely
So If we should run now the script on the example I gave here above (the 4 lines) (show the exact match "Tree"), the result should be:
1 Tree
4 Tree; leaf
I have read that the "find-function" has a "Match entire cell contents" option, but I have no idea at all to transform the already written content and merge with the new coding. Hope somebody can help a dummy.
I have an Excel file with 3000 rows. Target is that I input a column (the column where Excel has to search in) in an inputbox and a name in another input box, the vba macro is analyzing the excel file and removes all the rows who are not fulfill the inserted criteria.
Somebody created already something for me, but the function like in the search function "match entire cell contents" is unusable in that code.
Sub DelRows() Application.ScreenUpdating = False
Dim a, b, nc As Long, i As Long, Col As String, response As String
Col = InputBox("Enter the column letter:")
response = InputBox("Enter the taxonomy:")
nc = Cells(1, Columns.Count).End(xlToLeft).Column + 1
a = Range(Col & "1", Range(Col & Rows.Count).End(xlUp)).Value
ReDim b(1 To UBound(a), 1 To 1)
For i = 1 To UBound(a)
If Not a(i, 1) Like "*" & response & "*" Then b(i, 1) = 1
Next i
With Range(Col & "1").Resize(UBound(a), nc)
.Columns(nc).Value = b
' .Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, _
' OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
.Sort Key1:=.Columns(nc), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=True, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
On Error Resume Next
.Columns(nc).SpecialCells(xlConstants).EntireRow.Delete
On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub
Imagine you have an Excel:
1 Tree
2 Trees
3 Trees; leaf
4 Tree; leaf
I want to have (like in script above):
An inputbox who is asking in which column has to be searched. (already written)
An inputbox who is asking WHAT I want to search (already written but do not 100% like I want because Tree and Trees are shown!)
The first row of the Excel must been frozen (= not deleted)
All the rows who are not fulfilling the input criteria of item 2 => delete these rows completely
So If we should run now the script on the example I gave here above (the 4 lines) (show the exact match "Tree"), the result should be:
1 Tree
4 Tree; leaf
I have read that the "find-function" has a "Match entire cell contents" option, but I have no idea at all to transform the already written content and merge with the new coding. Hope somebody can help a dummy.