Find word (first word of sentence) and Delete entire column

  • I need to find the first word of a sentence and then delete the whole column.


    At the moment my code finds the word anywhere in the cell.


  • Maybe

    Rephrase your request. It does not make sense, to me anyway

    Remember, you know your workbook/spreadsheet. We're just guessing by what you tell us.

    The best thing is to explain in detail what you want to achieve or attach a workbook with a before and after with an explanation on how you got the "after".

    Put your code between tags. Highlight your code and click on the < / > (without spaces) at the top of your post.

  • This is how I understand your request.

    BTW, deleting Rows and/or Columns should be done by startind at the end and work your way back to the beginning (Row 1 or Column 1)

  • So there are a number of columns within my spreadsheet that contain pointless data so they need to be deleted.

    To find the column to be deleted I need to search for a word (input by the user), and then delete that entire column. The code I have uploaded successfully achieves that but now I need to adjust it to only delete columns with the FIRST WORD within the cell matching the users input.


    *Your code does not work


    Thank you so much for your help

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post


    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.


    How to use code tags

    Note: no apostrophe in the tags, just used for demonstration here.

    ['code]


    your code goes between these tags


    ['/code]


    Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.


    Thanks.

  • You say "the first word of a sentence".

    I understand that to mean the first word of a string in a cell. Maybe not so.

    As I previously asked, explain in detail or the best thing to do is attach a realistic workbook. Change privacy values if they exist in the workbook (names, addresses, email addresses and what have you)

  • Attached is the excel file I am working with. If you run my marco you may understand what I am trying to achieve.

    I need to delete the columns with the words "line", "group", "Heading" etc... in the header.


    The embedded marco will search the row input by the user (Should be the header row) for a string also input by the user (eg "Heading").

    The issue I'm having is that the macro will search the whole cell for the word which may cause columns to be deleted that shouldn't be.

    I need the marco to only search the first word of the cell to reduce errors.


    Thank you again

  • I assumed you mean Row 8 to be the Row in question.

    If it is Row #7, change all the referenced cell numbers from 8 to 7

    Right now it colors the cells red so you can check if the code does what you want it to do.

    Code
    Sub Maybe()
    Dim i As Long, strWord As String
    strWord = Application.InputBox("Enter the word to search for.", "Delete the columns with this word", Type:=2)
    For i = Cells(8, Columns.Count).End(xlToLeft).Column To 1 Step -1    '<---- Change the 8 to a 7 for Row #7
    If Left(Cells(8, i), Len(strWord)) = strWord Then Cells(8, i).Interior.Color = vbRed    '<---- Change both 8's to a 7 for Row #7
    Next i
    End Sub



    Code
    Sub Un_Color()
    Worksheets("Worksheet").Rows("8:8").Interior.Pattern = xlNone    '<---- Change Row number and/or Sheet Name if required
    End Sub



    If you're happy, change this line

    Code
    If Left(Cells(8, i), Len(strWord)) = strWord Then Cells(8, i).Interior.Color = vbRed

    to

    Code
    If Left(Cells(8, i), Len(strWord)) = strWord Then Cells(8, i).    '<---- Change both 8's to a 7 for Row #7




    For future considerations.

    Header rows are normally the Top Row unless specified to be different.

  • Multiple values in one go.

    Code
    Sub Maybe_Multiple_Values()
    Dim i As Long, delArr, j As Long
    delArr = Array("line", "group", "Heading")    '<---- Put all the values to be deleted between double quotation marks in the array
    For i = Cells(8, Columns.Count).End(xlToLeft).Column To 1 Step -1    '<---- Change the 8 to a 7 for Row #7
    For j = LBound(delArr) To UBound(delArr)
    If Left(Cells(8, i), Len(delArr(j))) = delArr(j) Then Cells(8, i).Interior.Color = vbRed: Exit For   '<---- Change both 8's to a 7 for Row #7
    Next j
    Next i
    End Sub
  • Which code are you referring to? The code from Post #9 or Post #10?

    If it is from Post #10, change this line

    Code
    If Left(Cells(8, i), Len(delArr(j))) = delArr(j) Then Cells(8, i).Interior.Color = vbRed: Exit For

    to this

    Code
    If Left(Cells(8, i), Len(delArr(j))) = delArr(j) Then Cells(8, i).EntireColumn.Delete: Exit For

    If it is for the code from Post #9, it is mentioned on how to proceed at the end of that Post.


    If you want to keep the cells red and have a separate macro to delete the Columns later, after checking maybe, let us know.

  • If you do want to do it after, this will do that.

    Code
    Sub Delete_Red_Cell_Columns()
    Dim i As Long
    Application.ScreenUpdating = False
    For i = Cells(8, Columns.Count).End(xlToLeft).Column To 1 Step -1    '<---- Change the 8 to a 7 for Row #7
    If Cells(8, i).Interior.Color = vbRed Then Cells(8, i).EntireColumn.Delete    '<---- Change the 8 to a 7 for Row #7
    Next i
    Application.ScreenUpdating = True
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!