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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Not quite sure where your range is to start or end but you could test something like this.

VBA Code:
Sub DelSame()
  With Range("C7", Range("C" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#=" & .Offset(, 1).Address & ",True,#)", "#", .Address))
    On Error Resume Next
    .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub
 
Upvote 0
Solution
Not quite sure where your range is to start or end but you could test something like this.

VBA Code:
Sub DelSame()
  With Range("C7", Range("C" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#=" & .Offset(, 1).Address & ",True,#)", "#", .Address))
    On Error Resume Next
    .SpecialCells(xlConstants, xlLogical).EntireRow.Delete
    On Error GoTo 0
  End With
End Sub
Hi Peter,
Thank you for replying. I indeed didn't say on which row I wanted to start or end. Its actually a table and it starts on row 7 until, well the end of it which is diferent every time.

What I get with your code is the value TRUE and the rows with the same values Re not deleted. Like this:


Onderhoud prijzen 9.4.16Beta.xlsm
ABCDE
6ArtikelcodeOmschrijvingVerkoopprijsVerkoopprijs nieuwVkp % opslag
7100000Vito Glaserfix 111 6x2 mm wit - 10x25 mTRUETRUE
8100001Vito Glaserfix 111 6x2 mm zwart - 10x25 m19,1320,276,00%
9100002Vito Glaserfix 111 6x3 mm wit - 10x25 mTRUETRUE
10100003Vito Glaserfix 111 6x3 mm zwart - 10x25 mTRUETRUE
11100004Vito Glaserfix 111 6x4 mm wit - 10x25 mTRUETRUE
12100005Vito Glaserfix 111 6x4 mm zwart - 10x25 mTRUETRUE
13100006Vito Glaserfix 111 9x2 mm wit - 10x25 m25,8727,426,00%
OPXML
Cell Formulas
RangeFormula
D7:D13D7=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:C9797Cell Value<>$AL7textNO
 
Upvote 0
Try this then, checking the table name.

VBA Code:
Sub DelSame_v2()
  With Range("Table1[Verkoopprijs]")
    .Value = Evaluate(Replace("if(#=" & .Offset(, 1).Address & ",True,#)", "#", .Address))
  End With
  With Range("Table1[#All]")
    .AutoFilter Field:=3, Criteria1:="TRUE"
    If .Columns(1).SpecialCells(xlVisible).Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=3
  End With
End Sub
 
Upvote 0
Try this then, checking the table name.

VBA Code:
Sub DelSame_v2()
  With Range("Table1[Verkoopprijs]")
    .Value = Evaluate(Replace("if(#=" & .Offset(, 1).Address & ",True,#)", "#", .Address))
  End With
  With Range("Table1[#All]")
    .AutoFilter Field:=3, Criteria1:="TRUE"
    If .Columns(1).SpecialCells(xlVisible).Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=3
  End With
End Sub
Now it does not work entirely an that is probably to the cause that there are more column in this sheet and a lot of them turn into "TRUE" and thats a problem, but the lines that need to be deleted are so that works. I also get an error ' Can't move cells in a filtered range or table"



Onderhoud prijzen 9.4.16Beta.xlsm
ABCDEFGHIJKLMNOPQR
6ArtikelcodeOmschrijvingVerkoopprijsVerkoopprijs nieuwVkp % opslagNieuwe Vkp € Opslag% [Kp]Marge% [Kp]Kp~Vkp% opslagKp~Vkp% margeKostprijsKostprijs nieuwKp % opslagVerschil Kp~Ikp*Kp_fKp opslag-Kp_fKp_factorOpslag% Kp~IkpInkoopprijs
7100000Item 1TRUE19,133,00%TRUETRUE0,0%217,8%68,5%TRUE6,020,00%-0,18Kp_factor?0,00%3,08%TRUE
9100002Item 2TRUE21,26TRUETRUETRUE0,0%223,6%69,1%TRUE6,570,00%-0,19Kp_factor?0,00%2,98%TRUE
10100003Item 3TRUE21,26TRUETRUETRUE0,0%223,6%69,1%TRUE6,570,00%-0,19Kp_factor?0,00%2,98%TRUE
11100004Item 4TRUE28,696,00%TRUETRUE0,0%218,1%68,6%TRUE9,020,00%-0,26Kp_factor?0,00%2,97%TRUE
12100005Item 5TRUE28,69TRUETRUETRUE0,0%218,1%68,6%TRUE9,020,00%-0,26Kp_factor?0,00%2,97%TRUE
OPXML
Cells with Conditional Formatting
CellConditionCell FormatStop If True
O7:O9797Cell Value="Inkoopprijs?"textNO
O7:O9797Cell Value="Kp_factor?"textNO
O7:O9797Cell Value<-0,1%textNO
O7:O9797Cell Value>0,1%textNO
R7:R9797Cell Value<>$AN7textNO
C7:C9797Cell Value<>$AL7textNO
K7:K9797Cell Value<>$AM7textNO






VBA Code:
Private Sub CommandButton1_Click()


With Range("Table_Query_from_A100")
    .Value = Evaluate(Replace("if(#=" & .Offset(, 1).Address & ",True,#)", "#", .Address))
  End With
  With Range("Table_Query_from_A100[#All]")
    .AutoFilter Field:=3, Criteria1:="TRUE"
    If .Columns(1).SpecialCells(xlVisible).Count > 1 Then .Offset(1).Resize(.Rows.Count - 1).EntireRow.Delete
    .AutoFilter Field:=3
  End With
End Sub
 
Upvote 0
a lot of them turn into "TRUE"
That is because you didn't adapt my code correctly. It should be
Rich (BB code):
With Range("Table_Query_from_A100[Verkoopprijs]")

I also get an error ' Can't move cells in a filtered range or table"
I have not ben able to reproduce that particular error. There must be something else going on in your sheet that I do not have. Are you able to upload a sample file (any sensitive data removed or disguised) that has that error to DropBox/OneDrive/Google Drive etc and provide a shared link here so that it can be investigated further?
 
Upvote 0
That is because you didn't adapt my code correctly. It should be
Rich (BB code):
With Range("Table_Query_from_A100[Verkoopprijs]")


I have not ben able to reproduce that particular error. There must be something else going on in your sheet that I do not have. Are you able to upload a sample file (any sensitive data removed or disguised) that has that error to DropBox/OneDrive/Google Drive etc and provide a shared link here so that it can be investigated further?
Hi Peter,
I changed the values according to your suggestion, but I still get an error. A different one, but still an error.

Below here is the file. I stripped it here and there. In column E-H are values that will change the values in column D. So when that happens I need the ones that are not changed to be deleted. In this case row 9 should be gone. I hope you can find the problem as this is the last piece of this project I have been working on for the last few months.

Thank you for your time and have a great day.

Romano


 
Upvote 0
The link provided is asking me for a sign in. Are you able to provide a link that does not require a sign in?
 
Upvote 0

Hope this works better.
Nope

1648733517629.png
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,751
Members
448,295
Latest member
Uzair Tahir Khan

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