Posts by jolivanes

    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

    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.

    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

    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.

    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)

    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)

    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.

    Do you manually enter the "y" into Column K?

    Is that going to be the only value in the whole of Column K? Nothing higher up or lower down?

    I assume that the "k" will be in the same row that needs to be transferred.

    When you say "entire row", why not just the used cells in that row?

    The reason I say this is to get you used to NOT use entire rows and/or columns as this will bloat your file size when using it with formats etc.

    Don't know for sure but this might be what you're attempting.

    I assumed that "Report" is the Sheet where you want to "deposit" the data?

    Check and change references where required.

    This is not a valid Range

    Code
    For Each cl In Range("L")

    This is

    Code
    For Each cl In Range("L:L")

    But be prepaired. There are over a million cl's in Range("L:L")

    Use a Range from the beginning of your data to the last cell of your data.

    There are all kinds of ways for this. Dave in Post #2 shows one way of doing it.


    If you want to use a similar way of what you had, this works also.

    Code
    Sub addvalue()
    Dim sh1 As Worksheet
    Dim cl As Range
    Set sh1 = Worksheets("Sheet1")
    For Each cl In sh1.Range("L1:L" & Cells(Rows.Count, "L").End(xlUp).Row)
    If Not WorksheetFunction.IsText(cl) Then cl = "'000" & cl
    Next cl
    End Sub

    If you check out this article

    https://excelmacromastery.com/excel-vba-find/

    and come back to this

    Function Find(What, [After], [LookIn], [LookAt], [SearchOrder], [SearchDirection As XlSearchDirection = xlNext], [MatchCase], [MatchByte], [SearchFormat]) As Range

    you'll see that the fourth item says "LookAt" (xlWhole or xlPart)

    We want to use "Lookat:=xlWhole" which is the same as 1


    Hope that this explains it. If not, we'll hear it.

    Good Luck in the meantime.