Hi there,
I do not know I might come late with this. Anyway, here is what I quickly di=
d with a vba script not knowing if the data blue can be deleted as well. I =
guess not because it would then change your match function result... but yo=
u say in your query to delete the entire row for the data value and data se=
t you want to delete.
The code is below. In brief, I think it can be done without vba, however it=
might not be easy since there are merged cells and filtering does not do we=
ll with merge cells. The thing would have be to insert a column with ascend=
ing rows numbers, most likely before column A then insert another temporary=
column between your match formula and the client data, have a formula base=
d on the first letter of the text in the right column and the result of you=
r match function in the left column return a number that you will also incr=
ement using a counter somewhere on the sheet.
Then you sort based on this column counter and it also on row numbers to ma=
ke sure you delete everything related to the value not found. And here in c=
onsecutive order, you should have your range that you can manually delete.
Last, you sort everything in column A (the earlier increment value)in ascen=
ding order and things should be as you want. It takes a bit of additional f=
unctions, maybe split them into smaller functions and you can have somethin=
g easier to go with.
Here is a code below, you can uncomment the line with 'A mark and comment o=
ut the line below if you can to delete the data in you blue range.
Option Explicit
Sub DataInGreen()
Dim ws As Worksheet
Set ws =3D ThisWorkbook.Worksheets("Sheet1")
With Application
.Calculation =3D xlCalculationManual
.DisplayAlerts =3D False
.ScreenUpdating =3D False
End With
Dim rC As Range, rcF As Range
Dim rTot As Range
Dim RangeF As Range
Dim i As Long, j As Long
Dim k As Long
Dim lastr As Long
Set RangeF =3D ws.Range("B:B")
lastr =3D ws.Cells(Rows.Count, 5).End(xlUp).Row
For i =3D lastr To 2 Step -1
If Left(CStr(UCase(ws.Cells(i, 5))), 1) =3D "C" Then
=20
Set rC =3D ws.Cells(i, 5)
=20
Set rcF =3D RangeF.Find(rC, LookIn:=3DxlValues, lookat:=3DxlPart, _
searchorder:=3DxlByRows, searchdirection:=3DxlNext, _
MatchCase:=3DFalse, searchformat:=3DFalse)
=20
If rcF Is Nothing Then
=20
j =3D rC.Row
k =3D j
=20
Do
k =3D k + 1
=20
Loop Until InStr(1, CStr(UCase(ws.Cells(k, 5))), "TOTAL") <> 0
'ws.Range(ws.Rows(j), ws.Rows(k)).Delete 'A uncomment if data b=
lue should be deleted as well
=20
ws.Range(ws.Cells(i, 5), ws.Cells(k, Columns.Count)).Delete xlU=
p
=20
End If
=20
End If
Next i
With Application
.Calculation =3D xlCalculationAutomatic
.DisplayAlerts =3D True
.ScreenUpdating =3D True
End With
Set ws =3D Nothing
End Sub
This might take time to run, I do not know. Further optimization can be made=
to match the Total and delete rows between the value and Total.=20
Hope it helps and I do not come to late.
Pascal Baro