Delete Rows for given conditions.

[fpd833]

I'm looking for some coding help. I have a large worksheet of data that
varies in length (well over 4k rows) and I'm looking to delete rows based on
a "code" entered in col E. The "code" in col E is text based and varies in
length.

I have a list of 85 or so codes that I want to delete from the data sample
without having to perform this manually. I've found code to delete rows based
on a cell value, but cannot figure out how to make it loop through all of the
codes I have to remove.

Is this even possible? Any help would be greatly appreciated. Thanks!

 

[Mike H]

Hi,

This assumes your codes that you want to delete are in column A of sheet 2.

Right click the sheet tab with your data in, View code and paste this in and
run it. N

Sub delete_Me()
Dim DelFalg As Boolean
Dim copyrange As Range, CheckRange As Range
LastrowA = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set CheckRange = Sheets("Sheet2").Range("A1:A" & LastrowA)
lastrow = Cells(Cells.Rows.Count, "E").End(xlUp).Row
Set MyRange = Range("E1:E" & lastrow)
For Each c In MyRange
delflag = False
For Each r In CheckRange
If c = r Then
delflag = True
Exit For
End If
Next
If delflag Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Delete
End If
End Sub


Mike

 

[JLGWhiz]

You can substitute your sheet names and column designation in the code below
to do the job:

Sub deleRows()
Dim lstRow1 as Long, lstRow2 As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
lstRow1 = sh1.Cells(Rows.Count, "E").Enc(xlUp).Rpw
lstRow2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row
For Each c In sh2.Range("A2:A" & lstRow2) '<<range w/list of codes
For i = lstRow1 To 2 Step - 1
If sh2.c.Value = sh1.Cells(i, 5).Value Then
sh1.Cells(i, 5).EntireRow.Delete
End If
Next
Next
End Sub

To use this code, put your list of codes to be searched in a column on a
separate worksheet. I used column A of sheet 2. Put the code into the
standard code module1 by opening the VBE Alt + F11. The code searches one
code at a time on sheet 1, starting at the bottom and working up. It will
repeat the search until each code in column A of sheet 2 has been checked.
That way, as the rows are deleted, it will not inadvertantly skip a row. I
did not test the code, so if there are any problems, post back.

 

[fpd833]

Thanks Mike! This is a beautiful thing!!!

Hi,

This assumes your codes that you want to delete are in column A of sheet 2.

Right click the sheet tab with your data in, View code and paste this in and
run it. N

Sub delete_Me()
Dim DelFalg As Boolean
Dim copyrange As Range, CheckRange As Range
LastrowA = Sheets("Sheet2").Cells(Cells.Rows.Count, "A").End(xlUp).Row
Set CheckRange = Sheets("Sheet2").Range("A1:A" & LastrowA)
lastrow = Cells(Cells.Rows.Count, "E").End(xlUp).Row
Set MyRange = Range("E1:E" & lastrow)
For Each c In MyRange
delflag = False
For Each r In CheckRange
If c = r Then
delflag = True
Exit For
End If
Next
If delflag Then
If copyrange Is Nothing Then
Set copyrange = c.EntireRow
Else
Set copyrange = Union(copyrange, c.EntireRow)
End If
End If
Next
If Not copyrange Is Nothing Then
copyrange.Delete
End If
End Sub


Mike

 

[fpd833]

Thanks JLGWhiz! This is a beautiful thing!!!

You can substitute your sheet names and column designation in the code below
to do the job:

Sub deleRows()
Dim lstRow1 as Long, lstRow2 As Long
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
lstRow1 = sh1.Cells(Rows.Count, "E").Enc(xlUp).Rpw
lstRow2 = sh2.Cells(Rows.Count, "A").End(xlUp).Row
For Each c In sh2.Range("A2:A" & lstRow2) '<<range w/list of codes
For i = lstRow1 To 2 Step - 1
If sh2.c.Value = sh1.Cells(i, 5).Value Then
sh1.Cells(i, 5).EntireRow.Delete
End If
Next
Next
End Sub

To use this code, put your list of codes to be searched in a column on a
separate worksheet. I used column A of sheet 2. Put the code into the
standard code module1 by opening the VBE Alt + F11. The code searches one
code at a time on sheet 1, starting at the bottom and working up. It will
repeat the search until each code in column A of sheet 2 has been checked.
That way, as the rows are deleted, it will not inadvertantly skip a row. I
did not test the code, so if there are any problems, post back.

 

[gimme_this_gimme_that]

If your data has headers along row 1 - a DAO solution deleting rows
with a SQL like command would be way faster than a row-at-a-time VBA
macro.