kelly mort
Well-known Member
- Joined
- Apr 10, 2017
- Messages
- 2,169
- Office Version
- 2016
- Platform
- Windows
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.
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?
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?
Code:
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