Delete Rows And Column Macro

boldcode · Aug 1, 2011

Hello,

I want to create a Macro to be used on the Active Sheet that FIRST deletes all the rows that contain the following values in Column E. Here are the values contained in Column E that will result in deleting the whole row: PT, JK, BH.

Here is a data sample, the real data will have thousands of rows:

<table style="width: 487px; height: 285px;" border="0" cellpadding="0" cellspacing="0"><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4022;width:83pt" width="110"> <tbody><tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;width:48pt" height="20" width="64">ROW 1</td> <td class="xl66" style="border-left:none;width:83pt" width="110">COLUMN E</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">ROW 2</td> <td class="xl65" style="border-top:none;border-left:none">Work Code</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">ROW 3</td> <td class="xl66" style="border-top:none;border-left:none">BN</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">ROW 4</td> <td class="xl66" style="border-top:none;border-left:none">KL</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">ROW 5</td> <td class="xl67" style="border-top:none;border-left:none">PT</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">ROW 6</td> <td class="xl67" style="border-top:none;border-left:none">JK</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">ROW 7</td> <td class="xl66" style="border-top:none;border-left:none">AL</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">ROW 8</td> <td class="xl67" style="border-top:none;border-left:none">BH</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">ROW 9</td> <td class="xl67" style="border-top:none;border-left:none">PT</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">ROW 10</td> <td class="xl67" style="border-top:none;border-left:none">JK</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">ROW 11</td> <td class="xl66" style="border-top:none;border-left:none">TU</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl66" style="height:15.0pt;border-top:none" height="20">ROW 12</td> <td class="xl67" style="border-top:none;border-left:none">PT</td> </tr> </tbody></table>
 Second, I would like the Macro to DELETE the ENTIRE Column E Not just the values in Column E.

Thank you,

BC

VoG · Aug 1, 2011

Try

Code:

Sub delE()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If IsNumeric(Application.Match(Range("E" & i).Value, Array("PT", "JK", "BH"), 0)) Then Rows(i).Delete
Next i
Columns("E").Delete
End Sub

boldcode · Aug 1, 2011

VoG,

It works Awesome!! Thank you!!!!

BC

boldcode · May 19, 2012

Hi VoG,

You helped me with this post a while back (I really like how your code works). I was wondering if you could help me modify your code just a little bit without changing the entire VBA code.

I want to change the Macro to delete all the rows that contain the following values in Column E. Any values that start with "E" or "F" in Column E will result in deleting the whole row.

Your Code Below:

Code:

Sub delE()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    If IsNumeric(Application.Match(Range("E" & i).Value, [COLOR=Red]Array("PT", "JK", "BH"), 0))[/COLOR] Then Rows(i).Delete
Next i
Columns("E").Delete
End Sub

Thanks,

BC

VoG · May 19, 2012

Try

Code:

Sub delE()
Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    With Range("E" & i)
        If Left(.Value, 1) = "E" Or Left(.Value, 1) = "F" Then .EntireRow.Delete
    End With
Next i
Columns("E").Delete
End Sub

boldcode · May 19, 2012

VoG,

Thank you, it works like a champ!

ray:

ray:

BC

Delete Rows And Column Macro

boldcode

Active Member

Excel Facts

VoG

Legend

boldcode

Active Member

boldcode

Active Member

VoG

Legend

boldcode

Active Member

Similar threads

Forum statistics

Share this page

Delete Rows And Column Macro

boldcode

Active Member

Excel Facts

VoG

Legend

boldcode

Active Member

boldcode

Active Member

VoG

Legend

boldcode

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock