How to filter out the rows that are having different values in the columns

[Romano_odK]

Good morning,

In the attached sheet I would like to be able to filter out the rows that do not have a different value in both column C and D. So I like to keep the row that have a different value in C and D and delete the rest. I also have a piece of code which was genouely given to me to delete filtered rows. Maybe it would be possible to alter this. Could anyone help me with this please.

Thank you for your time.

Romano

Onderhoud prijzen 9.4.15Beta.xlsm
	A	B	C	D	E
7	100000	Item number 1	19,13	20,28	6,00%
8	100001	Item number 2	19,13	19,13
9	100002	Item number 3	21,26	23,17	9,00%
10	100003	Item number 4	21,26	21,90	3,00%
11	100004	Item number 5	28,69	30,41	6,00%
12	100005	Item number 6	28,69	28,69
13	100006	Item number 7	25,87	25,87
14	100007	Item number 8	25,87	25,87
OPXML

Cell Formulas
Range	Formula
D7:D14	D7	=IFERROR(IFS(H7>0,K7/(1-H7),G7>0,K7*G7+K7,F7>0,F7,E7>0,E7*AL7+AL7,$D$1>0,$D$1*AM7+AM7,C7<>AM7,C7,C7=AM7,AM7),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C7:C14	Cell Value	<>$AL7	text	NO

Private Sub VerwijderVerborgenRijen_Click()

Application.EnableEvents = False
  Application.ScreenUpdating = False
  With ActiveSheet.ListObjects(1)
    .ListColumns.Add Position:=2
    On Error Resume Next
    .ListColumns(2).DataBodyRange.SpecialCells(xlVisible).Value = 1
    On Error GoTo 0
    If .Parent.FilterMode Then .Parent.ShowAllData
    .Sort.SortFields.Clear
    .Sort.SortFields.Add2 Key:=.ListColumns(2).DataBodyRange, SortOn:=xlSortOnValues, Order:=xlAscending
    .Sort.Apply
    On Error Resume Next
    .ListColumns(2).DataBodyRange.SpecialCells(xlBlanks).EntireRow.Delete
    On Error GoTo 0
    .ListColumns(2).Delete
  End With
  Application.EnableEvents = False

Range("AJ7:AJ15000").ClearContents
Range("AK7:AK15000").ClearContents
Range("AL7:AL15000").ClearContents

  With Sheets("OPXML").ListObjects("Table_Query_from_A100")
      .ListColumns(36).DataBodyRange.Value = .ListColumns(3).DataBodyRange.Value
   End With
    With Sheets("OPXML").ListObjects("Table_Query_from_A100")
      .ListColumns(37).DataBodyRange.Value = .ListColumns(11).DataBodyRange.Value
   End With
    With Sheets("OPXML").ListObjects("Table_Query_from_A100")
      .ListColumns(38).DataBodyRange.Value = .ListColumns(16).DataBodyRange.Value
   End With


    Dim xAF As AutoFilter
    Dim xFs As Filters
    Dim xLos As ListObjects
    Dim xLo As ListObject
    Dim xRg As Range
    Dim xWs As Worksheet
    Dim xIntC, xF1, xF2, xCount As Integer
    Application.ScreenUpdating = False
    On Error Resume Next
    For Each xWs In Application.Worksheets
        xWs.ShowAllData
        Set xLos = xWs.ListObjects
        xCount = xLos.Count
        For xF1 = 1 To xCount
         Set xLo = xLos.Item(xF1)
         Set xRg = xLo.Range
         xIntC = xRg.Columns.Count
         For xF2 = 1 To xIntC
            xLo.Range.AutoFilter Field:=xF2
           
         Next
        Next
    Next
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    
  

End Sub

 

[Romano_odK]

Nope

View attachment 61381

I changed it. Now its ok. I am sure of it.

Onderhoud prijzen.xlsm

drive.google.com

 

[Fluff]

@Peter_SSs I can download the file from post#9 quite happily.

@Romano_odK if I click the DeleteDifferentValues button, everything seems to work, with no errors.

 

[Romano_odK]

@Peter_SSs I can download the file from post#9 quite happily.

@Romano_odK if I click the DeleteDifferentValues button, everything seems to work, with no errors.

I get this, so TRUE and thats it.

 

[Fluff]

I get that as well, but the row is then deleted.

 

[Romano_odK]

I get that as well, but the row is then deleted.

As it should, but for me it isn't for some reason. I am going to drop it, since you say it works, so it has to be something at my end. Thank you for your time and have a great day.

 

[Fluff]

Sorry I can't help any further, but it's hard to pinpoint problems when I don't get them.

 

[Peter_SSs]

@Peter_SSs I can download the file from post#9 quite happily.

Yes, I had tried before the post had been edited.

@Romano_odK
It appears that the file is no longer able to be downloaded so I am unable to take a look.

I also found it intriguing that your button's title is 'DeleteDifferentValues' but your original question was

I like to keep the row that have a different value in C and D

 

[Romano_odK]

Yes, I had tried before the post had been edited.

@Romano_odK
It appears that the file is no longer able to be downloaded so I am unable to take a look.

View attachment 61431

I also found it intriguing that your button's title is 'DeleteDifferentValues' but your original question was

Good morning,
The description of the button is indeed deceiving, should have been "DeleteValuesThatAreTheSame" because that is what I am trying to accomplish. I restored the file.

Onderhoud prijzen.xlsm

drive.google.com

 

[Peter_SSs]

I restored the file.

Thanks

Do you get an error with that actual sample file (if so what is the full error message and on what line does the error occur).

Otherwise, if the error is with your real file, do you have hidden columns in the ListObject table?

 

[Romano_odK]

Thanks

Do you get an error with that actual sample file (if so what is the full error message and on what line does the error occur).

Otherwise, if the error is with your real file, do you have hidden columns in the ListObject table?

Hi Peter,
Yes I got with the file I send you also and guess what, when unhid all the columns it worked....great! So its this is a must to let it work. Ok then I have to figure something out for those hidden columns. Thank you and have a great weekend.

Romano