We help IT Professionals succeed at work.

Delete row if issue

Wilder1626
Wilder1626 asked
on
Medium Priority
263 Views
Last Modified: 2012-08-14
Hello all

I have a issue with this code.

When i run that macro on the sheet "Output" it work but if i run it from the sheet ("Import"), it does not delete the rows.

Do you know why?

Thanks for your help


'Delete scrap
    Dim z, LastRow1
    With Sheets("Output")
LastRow1 = Sheets("Output").Range("A" & Rows.Count).End(xlUp).Row
For z = LastRow1 To 11 Step -1
If Cells(z, "B").Value = "Total:" Or Cells(z, "E").Value = "From Da" Or Cells(z, "F").Value = "Direct" Or Cells(z, "G").Value = "All Shift" Or Cells(z, "A").Value = "OperID" Then
Cells(z, "A").EntireRow.Delete
End If
Next
End With

Open in new window

Comment
Watch Question

Engineer
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
You haven't qualified any of your Cells calls:
'Delete scrap
    Dim z, LastRow1
    With Sheets("Output")
LastRow1 = .Range("A" & .Rows.Count).End(xlUp).Row
For z = LastRow1 To 11 Step -1
If .Cells(z, "B").Value = "Total:" Or .Cells(z, "E").Value = "From Da" Or .Cells(z, "F").Value = "Direct" Or .Cells(z, "G").Value = "All Shift" Or .Cells(z, "A").Value = "OperID" Then
.Cells(z, "A").EntireRow.Delete
End If
Next
End With

Open in new window


Note the periods before the .Cells calls
CERTIFIED EXPERT

Author

Commented:
Oh thanks,

That easy!!!!!!!

loll
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
You realise that your code is deleting from one sheet but may be checking cells on another if you do that?
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Rory is right. You need to define where each of those cell calls are referring to. It may work well in your present scenario but you may run into trouble in some other scenario.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
It will only work properly if the Output sheet is active, which was the original problem. ;)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.