Not sure what to call this Delete column if statment is true

counterscc

Board Regular
Joined
Mar 6, 2006
Messages
119
Hello, Here is what I am looking at and what I would like to do is, if Column B does not have ETA in it Delete that row. Any help with this would be greatly apprecaited. Thanks again!

Cloumn A
Row 1 = 12345
Row 2 = 98765
Row 3 = 54637

Column B
Row 1 = ETA
Row 2 = (blank)
Row 3 = ETA
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

jss1234

Active Member
Joined
May 28, 2004
Messages
434
Code:
Sub deleterows()
Dim a As Long
For a = Range("A65536").End(xlUp).Row To 2 Step -1
    If Range("B" & a).Value <> "ETA" Then
        Rows(a).EntireRow.Delete
    End If
Next a
End Sub
 

counterscc

Board Regular
Joined
Mar 6, 2006
Messages
119
I must be doing something wrong. The column that would have the ETA in it is actually Column AC so I switched the "B" to "AC" I would like this to be built into a macro. Is there anything I need to do to do that? Thanks for your help!!
 

jss1234

Active Member
Joined
May 28, 2004
Messages
434
Do you know how to create a macro? Just press Alt & F11. Then insert a module, and copy and paste the code into the module. This will create a macro for you. Then switch the "B" with "AC" and it should work as long as row 1 has headers, and column A always has data in it.
 

counterscc

Board Regular
Joined
Mar 6, 2006
Messages
119

ADVERTISEMENT

It deletes the entire page
 

jss1234

Active Member
Joined
May 28, 2004
Messages
434
Make sure that column AC contains "ETA" without spaces before, in the middle of,m or after the "ETA".
 

jss1234

Active Member
Joined
May 28, 2004
Messages
434
Try This - it will get rid of extra spaces in the "ETA" cells:

Code:
Sub deleterows()
Dim a As Long
For a = Range("A65536").End(xlUp).Row To 2 Step -1
    If Trim(Range("AC" & a).Value) <> "ETA" Then
        Rows(a).EntireRow.Delete
    End If
Next a
End Sub
 

counterscc

Board Regular
Joined
Mar 6, 2006
Messages
119
Excellent thank you very much!!!!!!!!!!!!!!!!! You people here are the best!
 

Forum statistics

Threads
1,144,389
Messages
5,724,073
Members
422,534
Latest member
ThiccNugg

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