Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows for given conditions.
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows for given conditions.
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" wrote: 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows for given conditions.
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" wrote: 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows for given conditions.
Thanks Mike! This is a beautiful thing!!!
"Mike H" wrote: 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" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows for given conditions.
Thanks JLGWhiz! This is a beautiful thing!!!
"JLGWhiz" wrote: 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" wrote: 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Rows for given conditions.
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - 2003 - Delete data if conditions met | Excel Discussion (Misc queries) | |||
Delete variable # of rows depending on conditions | Excel Programming | |||
how to write to macro to only delete rows under certain conditions | Excel Programming | |||
Delete the contents of a cell under multiple conditions | Excel Programming | |||
Delete every 3rd row, then delete rows 2-7, move info f/every 2nd row up one to the end and delete the row below | Excel Programming |