Excel VBA Problem

Blobajob88

New Member
Joined
Mar 27, 2020
Messages
1
Office Version
365, 2019
Platform
Windows
Hi,

Can someone please help. For some reason, I cannot get the below code to work. I want it to delete the rows if there is a duplicate with the cells in column 3. It worked before but all of sudden it doesn't. When I change the number to column 4, 2, 8 or anything other than 3, it works fine. Can someone help urgently? The column titled 'description' is column 3

1585293178654.png
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
5,249
Office Version
365
Platform
Windows
It would be easier for anyone trying to help you if you posted the whole of your code rather than a picture of part of it
Click on <vba/> and paste your code between the code tags and do NOT try to format anything
 

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
2,629
Office Version
2010
Platform
Windows
I agree with Yongle about posting actual code (or a download URL that points to the Excel file or exported ".bas" file).

And while I cannot determine the intended code because some of it is commented out, things might work as intended if you add Exit For after the Delete statement. Something like:

VBA Code:
If Cells(i, 3) = Cells(i-j, 3) Then
    Rows(i).Delete shift:=xlUp
    Exit For
End If

FYI, note that you do not need to do Rows(i).Select.

Note: Exit For exits only the innermost for-loop. And that seems to be all you need.
 

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
751
Office Version
2016
Platform
Windows
alternatively you could remove the duplicates using the removeduplicates function
change the range to your dynamic range and header to yes if you have headers within the range

VBA Code:
ActiveSheet.Range("$B$2:$D$6").RemoveDuplicates Columns:=3, Header:=xlNo
 

Forum statistics

Threads
1,089,220
Messages
5,406,927
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top