VBA code failing to delete the right item

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. 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.

1KELLY MORTGROUP 1MYEAR 12020/2021
1KELLY MORTGROUP 1MYEAR 22020/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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Why are you checking basically the entire row when you only want to clear freg25 ??
Why not simply
VBA Code:
If cel.Offset(, -1) = freg25 Then

Instead of
VBA Code:
If cel = freg26 And cel.Offset(, -1) = freg25 And cel.Offset(, -23) = freg3 And cel.Offset(, -24) = freg2 Then
 
Upvote 0
Why are you checking basically the entire row when you only want to clear freg25 ??
Why not simply
VBA Code:
If cel.Offset(, -1) = freg25 Then

Instead of
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
OK. I am not with my pc now. I will upload shortly.
 
Upvote 0
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:
VBA Code:
Set findvalue = sh.Range("B3:B" & lr).Find(What:="KELLY MORT", LookIn:=xlValues, lookAt:=xlWhole)

Check this article:
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."
 
Upvote 0
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:
VBA Code:
Set findvalue = sh.Range("B3:B" & lr).Find(What:="KELLY MORT", LookIn:=xlValues, lookAt:=xlWhole)

Check this article:
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
 
Upvote 0

Forum statistics

Threads
1,214,967
Messages
6,122,503
Members
449,090
Latest member
RandomExceller01

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top