Simple question

[Ismael]

Hi to all,

just a questoin it's possible to delete one or more rows automatically, not just delete the content of the row, but delete the entire row it self.

Because I have a file that base in 3 condition I delete rows manually, and I would like to now if exist a way to this automatically.

So if any of you guys now a way please tell me OK?

Best regards,

Ismael

[Jacob Hilderbrand]

You can do it like this.


Range("A1").EntireRow.Delete

[Ismael]

Hi, DRJ

I'm glad that exist a way to this automatically, but now I will ask you if it's possible to delete the rows in sheet 1 if the values in the column B and C are equals to the values in sheet 2 column B and C, but the values in column H sheet 1 are < that the value in D4 sheet 2.

In conclusion what I want in this case is delete the range from row 4 until row 14.

Then I would try myself to expand this to more values.

If you can help me I really appreciate


Thanks in advance


best regards,

Ismael

[mvidas]

Hi Ismael,

Your sheet2.. will it always have one value in B to compare to, one value in C to compare to, and one value in D to compare to? Or could it have
AZ 21 11:00:00
AZ 22 10:00:00

Would sheet2 ever have more than one instance of "AZ" and "21" in the same row?

OK,
Assuming that there will never be more than one instance of "AZ" and "21" in the same row (meaning that each set of 2 cells is unique in those columns), you could use:


Sub IsmaelDeleteRows()
Dim WS1 As Worksheet, WS2 As Worksheet, CLL As Range, CLL2 As Range
Dim RG1 As Range, RG2 As Range, DelRG As Range
Set WS1 = Sheets("Sheet1")
Set WS2 = Sheets("Sheet2")
For Each CLL In WS2.Range("B4", WS2.Range("B65536").End(xlUp)).Cells
Set RG1 = FoundRange(WS1.Columns("B"), CLL)
If Not RG1 Is Nothing Then
Set RG2 = FoundRange(Intersect(RG1.EntireRow, WS1.Columns("C")), CLL.Offset(0, 1))
If Not RG2 Is Nothing Then
Set DelRG = Nothing
For Each CLL2 In RG2.Offset(0, 5).Cells
If CLL2 < CLL.Offset(0, 2) Then
If DelRG Is Nothing Then Set DelRG = CLL2 Else Set DelRG = Union(DelRG, CLL2)
End If
Next CLL2
If Not DelRG Is Nothing Then DelRG.EntireRow.Delete
End If
End If
Next CLL
End Sub
Function FoundRange(ByVal vRG As Range, ByVal vVal) As Range
Dim FND As Range, FND1 As Range
Set FND = vRG.Find(vVal, LookIn:=xlValues, LookAt:=xlWhole)
If Not FND Is Nothing Then
Set FoundRange = FND: Set FND1 = FND: Set FND = vRG.FindNext(FND)
Do Until FND.Address = FND1.Address
Set FoundRange = Union(FoundRange, FND): Set FND = vRG.FindNext(FND)
Loop
End If
End Function

Matt

[Ismael]

Hi Matt,

In first place thanks for the precious help.

Yes in sheet 2 will never exist more then one "AZ" "21", what could exist is "AZ" "23" or "TP" "21" but isn't the same.

I will always have one value in B to compare to, one value in C to compare to, and one value in D to compare to. So your code works perfectly.

Once a again thank you very much :thumb

best regards,

Ismael

:beerchug:

[mvidas]

Glad to help!