Delete if value is 0

[fatalcore]

Hi,
I have a worksheet,
Now I have two sheet named Face and Metal.
What I want is a VBA code against the button "Delete" in the sheet "Metal" that, if in the sheet "Face" the value in the column AL is 0 then the entire row will be deleted in the "Metal" sheet.
The values in Column J in "Face" and Column F in the sheet "Metal" is unique.
I am uploading a dummy sheet for reference.
Thanks in advance.

[Bob Phillips]

Application.ScreenUpdating = False

With Worksheets("Face")

lastrow = .Cells(.Rows.Count, "AL").End(xlUp).Row
For i = lastrow To 2 Step -1

If .Cells(i, "J").Value <> "" And .Cells(i, "AL").Value = 0 Then

.Rows(i)>Delete
End If
Next i
End With

Application.ScreenUpdating = True

[fatalcore]

Hi,
When I ran this code it is giving me an expected function or variable error.
Please help.

[Bob Phillips]

Did you put it in a sub?

[fatalcore]

Hi xld,
Yes I have put it in a sub, The delete word is getting selected and the compile error is throwing.
Private Sub CommandButton1_Click()
Call test
End Sub
Sub test()
Application.ScreenUpdating = False

With Worksheets("Face")

lastrow = .Cells(.Rows.Count, "AL").End(xlUp).Row
For i = lastrow To 2 Step -1

If .Cells(i, "J").Value <> "" And .Cells(i, "AL").Value = 0 Then

.Rows (i) > Delete 'The delete is getting selected and a compile error is throwing.
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[Bob Phillips]

Fat fingers.

]
Private Sub CommandButton1_Click()
Call test
End Sub
Sub test()
Application.ScreenUpdating = False

With Worksheets("Face")

lastrow = .Cells(.Rows.Count, "AL").End(xlUp).Row
For i = lastrow To 2 Step -1

If .Cells(i, "J").Value <> "" And .Cells(i, "AL").Value = 0 Then

.Rows(i).Delete
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[fatalcore]

Hi xld,
Thanks for keeping so much of patience with me. But I wanted the row in Metal sheet to be deleted not on the face sheet. Can you please help me out.
Thanks in advance.

[Bob Phillips]

Try this



Dim idx As Long
Dim lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With Worksheets("Face")

lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
For i = lastrow To 2 Step -1

If .Cells(i, "J").Value <> "" And .Cells(i, "AL").Value = 0 Then

idx = 0
On Error Resume Next
idx = Application.Match(.Cells(i, "J").Value, Worksheets("Metal").Columns("F"), 0)
On Error GoTo 0
If idx > 0 Then Worksheets("Metal").Rows(idx).Delete
End If
Next i
End With

Application.ScreenUpdating = True

[fatalcore]

Hi xld,
The data is getting deleted but the rows are not getting deleted. I need the row to be completely deleted. Otherwise it's perfect !
Thanks in advance.

[Bob Phillips]

I don't understand what you mean.

[fatalcore]

I mean when I am running the code the data is getting deleted but the rows are not getting deleted.
I also want the rows to be deleted from the "Metal" sheet.

I have described everything in the file mate.
Thanks in advance mate.

[Bob Phillips]

The rows are data and my code deletes rows, so you are not making it clear to me.

[fatalcore]

Hi, If you see the file I have attached you will understand it better.

When I ran the code the data was deleted but the rows was not deleted.

In this file>Goto Metal sheet > and press Delete button.

You will understand the difference.

Thanks in advance.

[Bob Phillips]

Okay I see what you mean, but that is not what you said. What you want is when a match is found, delete that row AND ALL BLANK ROWS UNTIL THE NEXT ROW WITH A VALUE IN COLUMN F.

[fatalcore]

Hi xld,Please forgive me for my wrong explanation earlier. and I am truly sorry for the same. At the same time thanks again for bearing with me for such a long time.
Thanks and deep regards in advance.

[Bob Phillips]

Dim idx As Long
Dim lastrow As Long, lastrow2 As Long
Dim i As Long, j As Long

Application.ScreenUpdating = False

With Worksheets("Metal")

lastrow2 = .Cells(.Rows.Count, "F").End(xlUp).Row
End With

With Worksheets("Face")

lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
For i = 2 To lastrow

If .Cells(i, "J").Value <> "" And .Cells(i, "AL").Value = 0 Then

idx = 0
On Error Resume Next
idx = Application.Match(.Cells(i, "J").Value, Worksheets("Metal").Columns("F"), 0)
On Error GoTo 0
If idx > 0 Then

Worksheets("Metal").Cells(i, "F").MergeArea.EntireRow.Delete
End If
End If
Next i
End With

Application.ScreenUpdating = True

[fatalcore]

Thanks mate ! You are awesome ! :)

[fatalcore]

Hi xld,
Hope you are doing fine.
There is something wrong in the code , The code is deleting without the match.
For example if T1 is 0 and T2 is 0 and T3 is 1 , Then the T1 and T3 is getting deleted and T2 is still present in the Metal sheet.

Where it should happen like T1 and T2 be deleted and T3 still visible.

Please see the excel file for more details.

Please help, Thanks in advance.

[Bob Phillips]

Sorry mate, that was a silly error on my part



Sub test()
Dim idx As Long
Dim lastrow As Long, lastrow2 As Long
Dim i As Long, j As Long

Application.ScreenUpdating = False

With Worksheets("Metal")

lastrow2 = .Cells(.Rows.Count, "F").End(xlUp).Row
End With

With Worksheets("Face")

lastrow = .Cells(.Rows.Count, "J").End(xlUp).Row
For i = 2 To lastrow

If .Cells(i, "J").Value <> "" And .Cells(i, "AL").Value = 0 Then

idx = 0
On Error Resume Next
idx = Application.Match(.Cells(i, "J").Value, Worksheets("Metal").Columns("F"), 0)
On Error GoTo 0
If idx > 0 Then

Worksheets("Metal").Cells(idx, "F").MergeArea.EntireRow.Delete
End If
End If
Next i
End With

Application.ScreenUpdating = True
End Sub

[fatalcore]

Thanks mate, You are still awesome !!! Godspeed !!!