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

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.

 

[kelly mort]

You're welcome, glad to help & thanks for the feedback.

the code has started doing what it was doing before. It is deleting other row instead of the specified row

 

[Akuini]

the code has started doing what it was doing before. It is deleting other row instead of the specified row

Can you explain in more detail?

 

[kelly mort]

Can you explain in more deta

What I have observed is that the code deletes the row that is active.
I have tried to vary the active rows manually and it seemed to work.

So with the After argument, how do I use it in my case to specify the active cell?

Maybe that could save me.

Thanks again.

 

[Michael M]

@Akuini
Oh Groan !!!....didn't see the forest !!

 

[kelly mort]

          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 =  cel.Offset(, -25)
                        
                        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

I finally fixed it

 

[Akuini]

Glad you figured it out.