Consulting

Results 1 to 2 of 2

Thread: Search in Excel specific word, delete all rows who does not have that word

  1. #1
    VBAX Newbie
    Joined
    Jun 2020
    Posts
    1
    Location

    Search in Excel specific word, delete all rows who does not have that word

    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):
    1. An inputbox who is asking in which column has to be searched. (already written)
    2. 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!)
    3. The first row of the Excel must been frozen (= not deleted)
    4. 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.

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,203
    Location
    Rough algorithm:
    arrCol = ColumnRange.Value
    for j = Ubound(arrCol) to LBound(arrCol)
       arrCel = Split(arrCol(j, 1), ";")
       Bool = False
       for i = Lbound(arrCel) to Ubound(arrCel)
          If arrCel(i) = Response Then 
             Bool = True
             Exit For
          End If
       Next i
    'For Testing:
       If Not Bool Then Rows(j + StartRowNum - 1).Interior.ColorIndex = 3
    'For Production
    '   If Not Bool Then Rows(j + StartRowNum - 1).Delete
    Next j
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •