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
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
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
 

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
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.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Perhaps it is a captialization issue

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

John Davis

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

ADVERTISEMENT

Perhaps it is a captialization issue

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

Jonmo1: Thanks, but that didn't do the trick either.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Maybe it's BOTH leading/trailing spaces AND Capitalization...

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

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650

ADVERTISEMENT

Perhaps

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

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
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
 

John Davis

Well-known Member
Joined
Sep 11, 2007
Messages
3,457
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??
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,329
Members
414,055
Latest member
mcarduner

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
Top