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

 

[Peter_SSs]

Yes I got with the file I send you also and guess what, when unhid all the columns it worked

The file you uploaded does not have any hidden columns.

If your actual file does then, the macro could unhide the columns, delete the relevant rows and then hide the columns again.

 

[Romano_odK]

The file you uploaded does not have any hidden columns.

If your actual file does then, the macro could unhide the columns, delete the relevant rows and then hide the columns again.

Good afternoon,
After some testing I found out that this is not working for me. The solution, although it worked once, will not work constantly and I have no idea what the cause might be. So is it possible to approach this differently. Is it possible to delete lines when a certain fill color is in a cell. So RGB 84 85 108 should be deleted in colum C. Can this be done?

Thank you again for you time,

Romano