Macro to delete rows based on criteria in the last column

[melvinjoseph]

Hi,

I'm a novice with macros, mostly just recording and executing.
Currently I'm trying to delete rows with the words "ABC" from the last column. This is a dynamic column and tends to change column.

<gs id="c1f2c56c-ad04-4b13-8633-0134f9b5ebd3" ginger_software_uiphraseguid="7b12def1-569c-4f3f-af18-ee04d1ea18b1" class="GINGER_SOFTWARE_mark">i.e</gs> sometimes it column AI or AJ or AK, etc.

I have made use of a similar macro for a static column which works flawlessly.
http://www.mrexcel.com/forum/excel-questions/489927-macro-delete-rows-based-criteria.html

Thanks for all your help and suggestions.

Thanks,
Melvin

 

[Fishboy]

Hi,

I'm a novice with macros, mostly just recording and executing.
Currently I'm trying to delete rows with the words "ABC" from the last column. This is a dynamic column and tends to change column.

<gs id="c1f2c56c-ad04-4b13-8633-0134f9b5ebd3" ginger_software_uiphraseguid="7b12def1-569c-4f3f-af18-ee04d1ea18b1" class="GINGER_SOFTWARE_mark">i.e</gs> sometimes it column AI or AJ or AK, etc.

I have made use of a similar macro for a static column which works flawlessly.
http://www.mrexcel.com/forum/excel-questions/489927-macro-delete-rows-based-criteria.html

Thanks for all your help and suggestions.

Thanks,
Melvin

Hi Melvin, welcome to the boards.

Try out the following in a COPY of your workbook:

Sub DeleteFromDynamicLastColumn()
' Defines variables
Dim cRange As Range, x As Long
' Defines LastCol as the last column with a header in row 1
LastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
' Defines LastRow as the last row of data based on column A
LastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
' Sets the check range the top to the bottom of whatever is the last column
Set cRange = Range(Cells(1, LastCol), Cells(LastRow, LastCol))
' Step backwards through each row of the last column from the bottom upwards
For x = cRange.Cells.Count To 1 Step -1
    With cRange.Cells(x)
' If the cell value of the last column is "ABC" then delete the entire row
        If .Value = "ABC" Then .EntireRow.Delete
    End With
' Check next row in range (from bottom to top)
Next x
End Sub

 

[My Aswer Is This]

Is the word in the last column like this:
ABC
Or may it be like this:
Take me to the ABC store

 

[melvinjoseph]

</gs>Hi Melvin, welcome to the boards.

Try out the following in a COPY of your workbook:

[<gs id="02062137-cbce-4f69-8761-3e393776c0b7" ginger_software_uiphraseguid="f4fa4ec7-c9d0-4d3b-ab52-af3d8c9e19f2" class="GINGER_SOFTWARE_mark">code</gs>]
Sub DeleteFromDynamicLastColumn<gs id="0de2e5aa-12f4-4e6b-a93f-b9e402f038d6" ginger_software_uiphraseguid="105f2250-c0b0-4fba-a9d6-f7a00f43744f" class="GINGER_SOFTWARE_mark">(</gs>)
' Defines variables
Dim <gs id="bb463e79-8cd2-4134-9e46-30e1ca9e0655" ginger_software_uiphraseguid="89689e10-1248-4af9-93b1-50f139bd6f6a" class="GINGER_SOFTWARE_mark">cRange</gs> As Range, x As Long
' Defines LastCol as the last column with a header in row 1
<gs id="de6f0fc5-6833-4f42-8f12-709d768726c6" ginger_software_uiphraseguid="6f600652-07cc-4430-9180-839c3e6edf50" class="GINGER_SOFTWARE_mark">LastCol</gs> = ActiveSheet<gs id="635dcb74-1d61-4451-ba0f-83a186048667" ginger_software_uiphraseguid="6f600652-07cc-4430-9180-839c3e6edf50" class="GINGER_SOFTWARE_mark">.</gs>Cells<gs id="26f36524-8b85-45b2-a6bf-59d8898daa07" ginger_software_uiphraseguid="6f600652-07cc-4430-9180-839c3e6edf50" class="GINGER_SOFTWARE_mark">(</gs>1, Columns<gs id="ad54fac4-3990-44d8-9735-119e5f51bdd4" ginger_software_uiphraseguid="6f600652-07cc-4430-9180-839c3e6edf50" class="GINGER_SOFTWARE_mark">.</gs>Count)<gs id="ba16c15e-09e8-4e5f-bf2d-22f6d4c226de" ginger_software_uiphraseguid="6f600652-07cc-4430-9180-839c3e6edf50" class="GINGER_SOFTWARE_mark">.</gs>End<gs id="d8231ff1-a9a3-4aea-a77f-b95b33678d1c" ginger_software_uiphraseguid="6f600652-07cc-4430-9180-839c3e6edf50" class="GINGER_SOFTWARE_mark">(</gs><gs id="6feae4b9-984b-4085-8bf7-92b0eeb54c37" ginger_software_uiphraseguid="6f600652-07cc-4430-9180-839c3e6edf50" class="GINGER_SOFTWARE_mark">xlToLeft</gs>)<gs id="d6c197a5-89bc-4d6d-b9b6-b573c232172a" ginger_software_uiphraseguid="6f600652-07cc-4430-9180-839c3e6edf50" class="GINGER_SOFTWARE_mark">.</gs>Column
' Defines LastRow as the last row of data based on column A
<gs id="d1dcb2dc-2d45-48f0-a14b-ab873d479891" ginger_software_uiphraseguid="bba8182a-27f4-4082-989d-00a4a53c730d" class="GINGER_SOFTWARE_mark">LastRow</gs> = ActiveSheet<gs id="f85c5fc7-eda9-497a-9d52-37d872e186c1" ginger_software_uiphraseguid="bba8182a-27f4-4082-989d-00a4a53c730d" class="GINGER_SOFTWARE_mark">.</gs>Cells<gs id="d1b3bfeb-ec28-4a26-918e-fc19aa2f1962" ginger_software_uiphraseguid="bba8182a-27f4-4082-989d-00a4a53c730d" class="GINGER_SOFTWARE_mark">(</gs>Rows<gs id="d5ca77e0-dcf8-4044-9a93-8f6dd248762a" ginger_software_uiphraseguid="bba8182a-27f4-4082-989d-00a4a53c730d" class="GINGER_SOFTWARE_mark">.</gs>Count, "A")<gs id="20bd324d-cb1d-43e7-8e37-3b1c7b89098f" ginger_software_uiphraseguid="bba8182a-27f4-4082-989d-00a4a53c730d" class="GINGER_SOFTWARE_mark">.</gs>End<gs id="93e32425-19dc-4698-8bd9-086829524a5b" ginger_software_uiphraseguid="bba8182a-27f4-4082-989d-00a4a53c730d" class="GINGER_SOFTWARE_mark">(</gs><gs id="0e8a79f4-5044-4240-9a06-c74166aa529c" ginger_software_uiphraseguid="bba8182a-27f4-4082-989d-00a4a53c730d" class="GINGER_SOFTWARE_mark">xlUp</gs>)<gs id="e39eaad5-2968-4729-9964-7c33c8d9e294" ginger_software_uiphraseguid="bba8182a-27f4-4082-989d-00a4a53c730d" class="GINGER_SOFTWARE_mark">.</gs>Row
' Sets the check range the top to the bottom of whatever is the last column
Set <gs id="7affe31f-9cf5-4843-af98-af3aaf9eec55" ginger_software_uiphraseguid="949adeee-4320-4c73-b3f4-9aa2aa2748c4" class="GINGER_SOFTWARE_mark">cRange</gs> = Range<gs id="16d4ff6e-c4f2-4d4c-882b-2fa576530a34" ginger_software_uiphraseguid="949adeee-4320-4c73-b3f4-9aa2aa2748c4" class="GINGER_SOFTWARE_mark">(</gs>Cells<gs id="34ab820a-55c8-496e-97d2-275304388d1b" ginger_software_uiphraseguid="949adeee-4320-4c73-b3f4-9aa2aa2748c4" class="GINGER_SOFTWARE_mark">(</gs>1, LastCol), Cells<gs id="e713e145-54ca-4e3a-a005-7663e4a1690d" ginger_software_uiphraseguid="949adeee-4320-4c73-b3f4-9aa2aa2748c4" class="GINGER_SOFTWARE_mark">(</gs>LastRow, LastCol))
' Step backwards through each row of the last column from the bottom upwards
For x = <gs id="54d493e6-8460-4374-a556-c9094793cca6" ginger_software_uiphraseguid="febab5d3-2ba9-43b1-a9b9-53103c6174e6" class="GINGER_SOFTWARE_mark">cRange</gs><gs id="f80d04c8-8f02-4353-9bc8-ebf8e3997311" ginger_software_uiphraseguid="febab5d3-2ba9-43b1-a9b9-53103c6174e6" class="GINGER_SOFTWARE_mark">.</gs>Cells<gs id="16ef1d08-8227-49c2-a349-e086aa1be515" ginger_software_uiphraseguid="febab5d3-2ba9-43b1-a9b9-53103c6174e6" class="GINGER_SOFTWARE_mark">.</gs>Count To 1 Step -1
With <gs id="80ad7ecf-d9b2-464c-8347-bded3ca7e1af" ginger_software_uiphraseguid="d2fa86a7-2db0-4fd2-98e6-020b9d0f8e6b" class="GINGER_SOFTWARE_mark">cRange</gs><gs id="2b254a81-9d8f-4d4a-a550-3deb77009339" ginger_software_uiphraseguid="d2fa86a7-2db0-4fd2-98e6-020b9d0f8e6b" class="GINGER_SOFTWARE_mark">.</gs>Cells<gs id="466079d8-6c26-4f15-906d-12aed0a6ebad" ginger_software_uiphraseguid="d2fa86a7-2db0-4fd2-98e6-020b9d0f8e6b" class="GINGER_SOFTWARE_mark">(</gs>x)
' If the cell value of the last column is "ABC" then delete the entire row
If<gs id="b1ebbc86-cfb7-4727-b623-c9d851bbac55" ginger_software_uiphraseguid="da9360bd-3362-4250-a6ad-ae5ee3b47f81" class="GINGER_SOFTWARE_mark"> .</gs>Value = "ABC" Then<gs id="7794e8fb-4638-49b3-beea-919c20d433d2" ginger_software_uiphraseguid="da9360bd-3362-4250-a6ad-ae5ee3b47f81" class="GINGER_SOFTWARE_mark"> .</gs><gs id="174a0440-c0b3-4c78-9eb7-35937f2d5a89" ginger_software_uiphraseguid="da9360bd-3362-4250-a6ad-ae5ee3b47f81" class="GINGER_SOFTWARE_mark">EntireRow</gs><gs id="df2c23a1-1443-43c4-8f17-4be75ba2af7d" ginger_software_uiphraseguid="da9360bd-3362-4250-a6ad-ae5ee3b47f81" class="GINGER_SOFTWARE_mark">.</gs>Delete
End With
' Check next row in range (from bottom to top)
Next x
End Sub
[/<gs id="9f841faa-681e-4257-97e8-b329c9def3da" ginger_software_uiphraseguid="7da4e7f6-bcba-406a-91a2-bd9643486213" class="GINGER_SOFTWARE_mark">code</gs><gs id="8f1e8199-00d7-44c4-a8f8-d1b4c16a5aad" ginger_software_uiphraseguid="7da4e7f6-bcba-406a-91a2-bd9643486213" class="GINGER_SOFTWARE_mark">]</gs>

Thanks a ton, this worked like a charm

 

[Fishboy]

Thanks a ton, this worked like a charm

Happy to help mate