VBA - find word one row above and clear all to the right

[bobaol]

Hello, I have column BA lined up with words. in the row above, it contains the same word, but in different places like BC, BG, etc. Is there VBA to quickly clear all the data in the above row to the right, when the word is found? The word will always be found. Take for example, go to BA43, find END date in BB42 to XFC42. when found, clear BC42 to BBW42. or, take BA44, go to BA44, find Business in BB43 to XFC43, when found, clear BK43 to BBW43. it does not have to end at BBW, it can end at 500 columns to the right. Any help is appreciated. thanks in advance.

 

[Cubist]

Can you share a sample dataset using XL2BB so we don't have to create a sample to test on?

 

[Cubist]

Try this on a copy. Not tested.

Sub SearchAndDelete()
    Dim a
    Dim i As Long, j As Long, k As Long
    Dim ws As Worksheet
 
    Set ws = ThisWorkbook.Worksheets("Sheet2") 'change sheet as needed
    a = ws.Range("BA42").CurrentRegion.Value 'assume cell starts in BA42
 
    For i = 1 To UBound(a, 1)
        For j = 1 To UBound(a, 1)
            For k = 2 To UBound(a, 2)
                If a(i, 1) = a(j, k) Then a(j, k) = ""
            Next k
        Next j
    Next i
 
    ws.Range("BA42").Resize(UBound(a, 1), UBound(a, 2)).Value = a
 
End Sub

 

[bobaol]

Hi Cubist, thanks for your response. the code did not work for me. here is a copy of the sample data. Thanks again in advance.

''cell BA41

'cell BR40

Row4

REG02

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

Start Date

Start Date

End Date

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

End Date

Status

Approved

45291​

45304​

Show previous row (inactive button)

1-7 of 7

Show next row (inactive button) View All

Business Unit

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

Business Unit

Row1

xxx

xxx

XXX

xxx

xxx

xxx

10​

0​

xxx

xxx

xxx

xxx

xxx

xxx

xxx

xxx

Row1

REG02

xxx

xxx

Auto Approved

47848​

xxx

10​

0​

8​

Row2

xxx

xxx

xxx

xxx

xxx

xxx

xxx

Row2

REG02

xxx

xxx

Auto Approved

47848​

xxx

5​

0​

4​

Row3

xxx

xxx

xxx

xxx

xxx

xxx

xxx

Row3

REG02

xxx

xxx

Approved

47848​

xxx

25​

0​

20​

Row4

xxx

xxx

xxx

xxx

xxx

xxx

xxx

Row4

REG02

xxx

xxx

Approved

47848​

xxx

20​

0​

16​

Row5

xxx

xxx

xxx

xxx

xxx

xxx

xxx

Row5

REG02

xxx

xxx

Approved

48944​

xxx

5​

0​

4​

Row6

xxx

xxx

xxx

xxx

xxx

xxx

xxx

Row6

REG02

xxx

xxx

Auto Approved

46446​

xxx

25​

0​

20​

Row7

xxx

xxx

xxx

xxx

xxx

xxx

xxx

Row7

REG02

xxx

xxx

Auto Approved

48760​

xxx

10​

0​

8​

Total Dist%

xxx

xxx

xxx

xxx

xxx

xxx

xxx

''cell BA52

 

[Cubist]

It works fine for me. In what way it didn't work?

Book1
	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ	BK	BL	BM	BN	BO	BP	BQ	BR
42	Row4	REG02	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
43	Start Date		xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
44	End Date	Status	Approved	45291	45304		Show previous row (inactive button)	1-7 of 7	Show next row (inactive button) View All		xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
45	Business Unit		xxx	xxx	XXX	xxx	xxx	xxx	10	0	xxx	xxx	xxx	xxx	xxx	xxx	xxx	xxx
46	Row1	REG02	xxx	xxx	Auto Approved	47848	xxx	10	0	8		xxx	xxx	xxx	xxx	xxx	xxx	xxx
47	Row2	REG02	xxx	xxx	Auto Approved	47848	xxx	5	0	4		xxx	xxx	xxx	xxx	xxx	xxx	xxx
48	Row3	REG02	xxx	xxx	Approved	47848	xxx	25	0	20		xxx	xxx	xxx	xxx	xxx	xxx	xxx
49	Row4	REG02	xxx	xxx	Approved	47848	xxx	20	0	16		xxx	xxx	xxx	xxx	xxx	xxx	xxx
50	Row5	REG02	xxx	xxx	Approved	48944	xxx	5	0	4		xxx	xxx	xxx	xxx	xxx	xxx	xxx
51	Row6	REG02	xxx	xxx	Auto Approved	46446	xxx	25	0	20		xxx	xxx	xxx	xxx	xxx	xxx	xxx
52	Row7	REG02	xxx	xxx	Auto Approved	48760	xxx	10	0	8	Total Dist%	xxx	xxx	xxx	xxx	xxx	xxx	xxx
Sheet2

 

[bobaol]

Hi Cubist, thank you so much for your code and input. I re-ran it again and it mostly worked. what i want is to delete everything to the right, except the word that is found. For example, in cell BF44, I want to keep BF44, but delete the 9999 cells to the right. thanks in advance again.

 

[Cubist]

I misunderstood what you wanted. Will there ever be duplicates? For example, Row4 appears multiple times.

 

[Alex Blakenburg]

Not as compact as Cubist but see if this works for you.
I have coded the row to start at, as being 42, so you will need to change that to the 1st row in your actual dataset.

Sub FindValueDeleteToEnd()

    Dim ws As Worksheet
    Dim rng As Range
    Dim arr As Variant
    Dim findString As String, foundYN As String
    Dim rowFirst As Long, rowLast As Long, colLast As Long
    Dim i As Long, j As Long
    
    Set ws = ActiveSheet                                        ' <--- ideally change to Worksheets("NameOfSheet")
    
    With ws
        rowFirst = 42                                           ' <--- Change to 1st row containing data to be checked
        rowLast = .Range("BA" & Rows.Count).End(xlUp).Row
        colLast = .Cells.Find(what:="*" _
            , Lookat:=xlPart _
            , LookIn:=xlFormulas _
            , searchorder:=xlByColumns _
            , searchdirection:=xlPrevious).Column
        Set rng = .Range(.Cells(rowFirst, "BA"), .Cells(rowLast, colLast))
        arr = rng.Value
    End With
    
    For i = 1 To UBound(arr) - 1
        findString = arr(i + 1, 1)                              ' Value from next row, 1st column in array
        foundYN = "N"
        For j = 2 To UBound(arr, 2)
            If arr(i, j) = findString Then
                foundYN = "Y"
            ElseIf foundYN = "Y" Then
                arr(i, j) = ""
            End If
        Next j
    Next i
    
    rng.Value = arr

End Sub

 

[bobaol]

Thank you both Alex and Cubist for your help. Alex's code worked perfectly.

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.