VBA code failing to delete the right item

[kelly mort]

freg2 contains name eg. "THE NAME"
freg3 contains a string say "GROUP 1", "GROUP 2". "GROUP 3" , ...., - it is a combobox
freg25 also contains a string just as freg3 eg "YEAR 1", "YEAR 2", "YEAR 3" - it is a textbox but will contain each of the strings "YEAR *"
freg26 will contain string like "2021/2022", etc.

The data starts from column A row 4.

1

KELLY MORT

GROUP 1

M

YEAR 1

2020/2021

1

KELLY MORT

GROUP 1

M

YEAR 2

2020/2021

So from the above sample, when I run my code, my aim was to delete the record for the YEAR 1 (The first item) from the data above.
But code code keeps deleting the second item instead - except there is only one record (year 1 only) that it deletes the right item for me.

I tried adding a third item (year 3) to check if the code will delete it (thinking the code was pointing to the last row) but it deleted the year 2 again.
can someone help me with the best way to get the job done?

          Sub DeleteIt()
                Dim cel As Range, sh As Worksheet, lr&
                Set sh = Sheets("Data")
                lr = sh.Range("A" & Rows.Count).End(xlUp).Row
                If lr < 4 Then lr = 4
                For Each cel In sh.Range("Z4:Z" & lr)
                    If cel = freg26 And cel.Offset(, -1) = freg25 And cel.Offset(, -23) = freg3 And cel.Offset(, -24) = freg2 Then
                        Set findvalue = sh.Range("A4:A" & lr).Find(what:=freg1, LookIn:=xlValues, lookat:=xlWhole)
                        
                        findvalue.Resize(, 29).ClearContents
                        sh.Range("A4:AC" & lr).Sort key1:=sh.[B4], order1:=xlAscending, Header:=xlNo
                        sh.Range("A4:AC" & lr).Sort key1:=sh.[C4], order1:=xlAscending, Header:=xlNo
                        Exit For
                    End If
                Next cel
            End Sub

 

[Michael M]

Why are you checking basically the entire row when you only want to clear freg25 ??
Why not simply

If cel.Offset(, -1) = freg25 Then

Instead of

If cel = freg26 And cel.Offset(, -1) = freg25 And cel.Offset(, -23) = freg3 And cel.Offset(, -24) = freg2 Then

 

[kelly mort]

Why are you checking basically the entire row when you only want to clear freg25 ??
Why not simply

If cel.Offset(, -1) = freg25 Then

Instead of

If cel = freg26 And cel.Offset(, -1) = freg25 And cel.Offset(, -23) = freg3 And cel.Offset(, -24) = freg2 Then

It is because, the name could appear multiple times and using only the freg25 will not help me in the future as the Year 1 could also appear multiple times.

Using the 4 criteria will resolve the future trap.

That's why I am checking the 4 columns.

 

[Michael M]

Ok, thats fine...When you step through using F8, do the correct values appear as freg2,3,25 and 26 ??
Freg3 might be your issue though....is it a Data Validation Dropdown or an actual forms combobox...OR an ActiveX combobox...
If you hover over the variables when the line is highlighted it will show their values.

 

[kelly mort]

Sure they are returning the right values.

I used a message box to track them and they are showing same values as their respective variables
The freg3 is a form combobox

 

[Michael M]

Can you upload the desensitized file to dropbox or similar, then paste the link back here....
I don't follow that if all the criteria are met in the other cells, that it can clear a different row ?

 

[kelly mort]

OK. I am not with my pc now. I will upload shortly.

 

[kelly mort]

DELETE_FAIL_DEMO_BOOK.xlsm

Shared with Dropbox

www.dropbox.com

Here is the sample workbook

 

[Akuini]

In Find method, the search doesn't start at the first cell in the defined range but AFTER the first cell (unless you specify the "After" argument). In your code it stars at B5.
So it should be B3 not B4 in this line:

Set findvalue = sh.Range("B3:B" & lr).Find(What:="KELLY MORT", LookIn:=xlValues, lookAt:=xlWhole)

Check this article:

Range.Find method (Excel)

Office VBA reference topic

docs.microsoft.com

It says:
"The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface.
Notice that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell.
If you do not specify this argument, the search starts after the cell in the upper-left corner of the range."

 

[kelly mort]

In Find method, the search doesn't start at the first cell in the defined range but AFTER the first cell (unless you specify the "After" argument). In your code it stars at B5.
So it should be B3 not B4 in this line:

Set findvalue = sh.Range("B3:B" & lr).Find(What:="KELLY MORT", LookIn:=xlValues, lookAt:=xlWhole)

Check this article:

Range.Find method (Excel)

Office VBA reference topic

docs.microsoft.com

It says:
"The cell after which you want the search to begin. This corresponds to the position of the active cell when a search is done from the user interface.
Notice that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell isn't searched until the method wraps back around to this cell.
If you do not specify this argument, the search starts after the cell in the upper-left corner of the range."

It worked!
Thanks for the quick fix

Have a wonderful time and thanks to you @Michael M