Delete Rows for given conditions.

  • Thread starter Thread starter fpd833
  • Start date Start date
F

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!
 
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
 
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.
 
Thanks Mike! This is a beautiful thing!!!

Mike H said:
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
Click to expand...
 
Thanks JLGWhiz! This is a beautiful thing!!!

JLGWhiz said:
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.
Click to expand...
 
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.
 
Back
Top