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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
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,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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