VBA - delete rows when two criterias match, otherwise do nothing

[VBwArrior]

Hello,

I am struggling with a VBA that should delete rows when two criterias match and if not, then keep everything as it was before.

The thing is, that I have two worksheets: "Sheet1" and "Ladu"

If value (product number) in "Sheet1" N16:N match with value in "Ladu" B4:B then values in the same row but in different column Sheet1(I:I) and Ladu(I:I) should be compared.

If value in Ladu(I:I) is bigger than value in Sheet1(I:I) then delete entire row from Sheet1 (row where the matching value was found).

If value in Ladu(I:I) is smaller than value in Sheet1(I:I) then do nothing.

This is the code I've written, I hope you guys can give me some suggestions or tips.

Please Login or Register  to view this content.

Thank you in advance!

Sample file:

[Keebellah]

Very sloppy code and incorrect, below the corrected for next loops

code]
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False

Dim Rng1 As Range
Dim CompareRange As Range
Dim Rng2 As Range
Dim CompareRange2 As Range
Dim x As Variant
Dim y As Variant

Set CompareRange = Worksheets("Ladu").Range("B4:B")
Set Rng1 = Worksheets("Sheet1").Range("N16:N")

Rng1.SpecialCells(xlCellTypeConstants).Select
CompareRange.SpecialCells(xlCellTypeConstants).Select

For Each x In Rng1
For Each y In CompareRange
If x = y And Worksheets("Sheet1").cell(x, 9).Value < Worksheets("Ladu").cell(y, 9).Value Then
Worksheets("Sheet1").cell(x, 9).Value.EntireRow.Delete
Else
End If
Next y
Next x
Application.ScreenUpdating = True
End Sub
[/code]

But then ...

Please Login or Register  to view this content.

apart from that the two red marked lines, the first, that sheet is not present (as far as I can see)
What is the lat row in N?

The RefreshallDataConn in Laoseis is wrong, I'm sure you didn't even test it.

I didn't even take the time to look further

[VBwArrior]

Hi Keebellaah,

I just made the sheet name easier to work with, so renamed it to "Ladu" in the code I have posted here.

Did you mean the last row? If yes, then I would like to mark the range as full column N and B respectively.

Thanks for your time and response!

[Keebellah]

You could have least renamed the sheet yourself. Which sheet is / was it?

[VBwArrior]

Sheet named Laoseis was renamed to Ladu

[Keebellah]

The code you used is incorrect but I don't know if the result is goor,

here's the correct working code, it works because it contains no errors, it's your test with your data to verify.

Please Login or Register  to view this content.