Deleting Rows

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
Hello All:

The below code is not accomplishing the task. It should delete all the rows in Column K which contain the word DUAL. Can anyone offer any help? Could this be a formating issue?

Dim lastrow As Long, myrow As Long
lastrow = Cells(Rows.Count, 11).End(xlUp).Row
For myrow = lastrow To 2 Step -1
If Cells(myrow, 11).VALUE = "DUAL" Then Rows(myrow).DELETE
Next myrow
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Perhaps there are leading/trailing spaces. Try

Code:
Sub del()
Dim lastrow As Long, myrow As Long
lastrow = Cells(Rows.Count, 11).End(xlUp).Row
For myrow = lastrow To 2 Step -1
If Trim(Cells(myrow, 11).Value) = "DUAL" Then Rows(myrow).Delete
Next myrow
End Sub
 
Upvote 0
Perhaps there are leading/trailing spaces. Try

Code:
Sub del()
Dim lastrow As Long, myrow As Long
lastrow = Cells(Rows.Count, 11).End(xlUp).Row
For myrow = lastrow To 2 Step -1
If Trim(Cells(myrow, 11).Value) = "DUAL" Then Rows(myrow).Delete
Next myrow
End Sub

Vog II: Thanks for your reply, however, it is still not working.
 
Upvote 0
Perhaps it is a captialization issue

If Ucase(Cells(myrow, 11).VALUE) = "DUAL" Then Rows(myrow).DELETE
 
Upvote 0
Maybe it's BOTH leading/trailing spaces AND Capitalization...

If Ucase(Trim(Cells(myrow, 11).VALUE)) = "DUAL" Then Rows(myrow).DELETE
 
Upvote 0
Perhaps

Code:
If WorksheetFunction.Clean(Trim(Cells(myrow, 11).Value)) = "DUAL" Then Rows(myrow).Delete
 
Upvote 0
Perhaps

Code:
If WorksheetFunction.Clean(Trim(Cells(myrow, 11).Value)) = "DUAL" Then Rows(myrow).Delete

Neither of these worked either. I'll try something else. Since the DUAL in Column K yields a 0 in Column J, I'll try this.

If WorksheetFunction.Clean(Trim(Cells(myrow, 10).Value)) = "0" Then Rows(myrow).Delete
 
Upvote 0
Neither of these worked either. I'll try something else. Since the DUAL in Column K yields a 0 in Column J, I'll try this.

If WorksheetFunction.Clean(Trim(Cells(myrow, 10).Value)) = "0" Then Rows(myrow).Delete


Vog II and Jonmo. I got it. I just moved the code too another location in the macro and now it works??
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top