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

Romano_odK

Active Member
Joined
Jun 4, 2020
Messages
379
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
7100000Item number 119,1320,286,00%
8100001Item number 219,1319,13
9100002Item number 321,2623,179,00%
10100003Item number 421,2621,903,00%
11100004Item number 528,6930,416,00%
12100005Item number 628,6928,69
13100006Item number 725,8725,87
14100007Item number 825,8725,87
OPXML
Cell Formulas
RangeFormula
D7:D14D7=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
CellConditionCell FormatStop If True
C7:C14Cell Value<>$AL7textNO




VBA Code:
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
 
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. :unsure:

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

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
The file you uploaded does not have any hidden columns. :unsure:

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
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,733
Members
448,294
Latest member
jmjmjmjmjmjm

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