Error in a Macro

[Guest]

Hi:

The below macro is supposed to delete the complete rows when the cells in
columm E are not equal to "CNR001". I am getting an error in rows.Delete

Could anyone help me checking out this macro for me.

Thanks.

Sub NoProReport()

Range("E1").Select

For Carrier = 1 To 1000

If Carrier = "CNR001" Then
ActiveCell(1, 0).Select

Else
rows.Delete

End If
Next Carrier

End Sub

 

[Guest]

Change your "rows.delete" line and replace with the following:

Rows(Application.ActiveCell.Row).EntireRow.Delete

 

[Guest]

Thanks Kevin for your answer. I run the macro but it deleted all the rows.
I must have made a mistake in other part of the macro. If it is not too
much to ask could you check it out?

Thanks,

 

[Dave Peterson]

Option Explicit
Sub NoProReport2()

dim iRow as long
with worksheets("Somesheetnamehere")
for irow = 1000 to 1 step -1
if lcase(.cells(irow,"E").value) = lcase("CNR001") the
.rows(irow).delete
end if
next irow
end with

End Sub

You could be overestimating where to start. If you wanted to start in the last
used row in column E, you could use this line instead:

for irow = .cells(.rows.count,"E").end(xlup).row to 1 step -1

And by starting at the bottom and working your way to the top, you'll find that
it's much easier. You don't have to keep track of what row you're on and if it
was deleted or if it was kept.

 

[Guest]

Hi Dave

Thanks for your answer. I tried your version but it is deleting the rows
with CNR001 and I want it to delete the rows that don't read CNR001 on columm
E.

 

[Dave Peterson]

Change this portion:

Hi Dave

Thanks for your answer. I tried your version but it is deleting the rows
with CNR001 and I want it to delete the rows that don't read CNR001 on columm
E.

 

[Dave Peterson]

Oops.

Change this portion:
if lcase(.cells(irow,"E").value) = lcase("CNR001") then
.rows(irow).delete
end if

to

if lcase(.cells(irow,"E").value) = lcase("CNR001") then
'do nothing
else
.rows(irow).delete
end if

You may want to use 1000 to 1 now. Or use something else to determine what the
last row is.

 

[Guest]

Thanks Dave It worked.

Just my last question, How can I program it so instead of going through 1 -
1000 it goes to the last row with data and stops there. I ususally have
less data than 1000 but the number is irregular.

Thanks again

 

[Dave Peterson]

If you can pick out a column that you can use to determine the last row (I used
column E), you could use this line:

for irow = .cells(.rows.count,"E").end(xlup).row to 1 step -1

instead.

Change "E" to whatever column letter can be used.

Thanks Dave It worked.

Just my last question, How can I program it so instead of going through 1 -
1000 it goes to the last row with data and stops there. I ususally have
less data than 1000 but the number is irregular.

Thanks again

 

[Guest]

Thanks a lot Dave. It worked.

If you can pick out a column that you can use to determine the last row (I used
column E), you could use this line:

for irow = .cells(.rows.count,"E").end(xlup).row to 1 step -1

instead.

Change "E" to whatever column letter can be used.