Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows if they contain a value held in a separate list
I have a list of codes in column A
I want to delete any rows from column A if they contain any codes that appear in a list held on a separate tab. Any help appreciated! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows if they contain a value held in a separate list
On Apr 5, 1:21*am, wrote:
I have a list of codes in column A I want to delete any rows from column A if they contain any codes that appear in a list held on a separate tab. Any help appreciated! Hi, You could do this with a macro or formulas and a set of steps. Which is best probably depends on how often you need to do it. To do it manually, use the following process (assuming your List on sheet 1 spans Cols A:G and your list on sheet 2 is in the range A1:A50 - just for example). 1. In cell H2 of sheet 1 (next to your list) put the formula: "=ISERROR(MATCH(A2,Sheet2!$A$1:$A$50,0))" 2. Fill down to the end of your list 3. Turn on Autofilter 4. In col H filter for 'False' 5. Select all the rows and delete them 6 Turn off Autofilter. If this isn't appropriate, a macro can easily be concieved. Cheers, Ivan. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows if they contain a value held in a separate list
Hi Wreth
One way to do it in code would be Option Explicit Dim MyCell, MyRng As Range Dim FoundCell As Range Dim LastRow As Integer Private Sub CommandButton1_Click() Worksheets("Sheet3").Activate LastRow = [A65535].End(xlUp).Row Set MyRng = Range("A1:A" & LastRow) For Each MyCell In MyRng Set FoundCell = Worksheets("Sheet1").Cells _ ..Find(What:=MyCell, LookAt:=xlWhole) If Not FoundCell Is Nothing Then FoundCell.EntireRow.Delete End If Next MyCell End Sub I hope this helps you out. Steve |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows if they contain a value held in a separate list
Thanks Ivan - it successfully identifies the records just how I want.
However I get a problem when I try to delete - Excel just hangs, probably due to the # of entries (10,000 or so) Can I macro get round this? On Apr 4, 3:40 pm, Ivyleaf wrote: On Apr 5, 1:21 am, wrote: I have a list of codes in column A I want to delete any rows from column A if they contain any codes that appear in a list held on a separate tab. Any help appreciated! Hi, You could do this with a macro or formulas and a set of steps. Which is best probably depends on how often you need to do it. To do it manually, use the following process (assuming your List on sheet 1 spans Cols A:G and your list on sheet 2 is in the range A1:A50 - just for example). 1. In cell H2 of sheet 1 (next to your list) put the formula: "=ISERROR(MATCH(A2,Sheet2!$A$1:$A$50,0))" 2. Fill down to the end of your list 3. Turn on Autofilter 4. In col H filter for 'False' 5. Select all the rows and delete them 6 Turn off Autofilter. If this isn't appropriate, a macro can easily be concieved. Cheers, Ivan. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows if they contain a value held in a separate list
Steve - sorry for being thick here
My column A that I want to delete the rows from is on sheet2. The list of codes I'm cross checking against is on sheet3. How do I tweak the code to reflect this? On Apr 4, 3:53 pm, Incidental wrote: Hi Wreth One way to do it in code would be Option Explicit Dim MyCell, MyRng As Range Dim FoundCell As Range Dim LastRow As Integer Private Sub CommandButton1_Click() Worksheets("Sheet3").Activate LastRow = [A65535].End(xlUp).Row Set MyRng = Range("A1:A" & LastRow) For Each MyCell In MyRng Set FoundCell = Worksheets("Sheet1").Cells _ .Find(What:=MyCell, LookAt:=xlWhole) If Not FoundCell Is Nothing Then FoundCell.EntireRow.Delete End If Next MyCell End Sub I hope this helps you out. Steve |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows if they contain a value held in a separate list
Hi
All you need to do is change the following line to reflect which sheet you are running the "FIND" on like so Set FoundCell = Worksheets("Sheet2").Cells _ ..Find(What:=MyCell, LookAt:=xlWhole) The code already takes the list to search for from sheet3. i hope this makes it a little clearer for you but if you have any more problems with the code let me know and i will comment it for you. Steve |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows if they contain a value held in a separate list
On Apr 5, 2:37*am, Incidental wrote:
Hi All you need to do is change the following line to reflect which sheet you are running the "FIND" on like so Set FoundCell = Worksheets("Sheet2").Cells _ .Find(What:=MyCell, LookAt:=xlWhole) The code already takes the list to search for from sheet3. *i hope this makes it a little clearer for you but if you have any more problems with the code let me know and i will comment it for you. Steve Hi, Just a word of caution, If you are looping through the cells to find the matches and delete, that's fine but you won't be able to use a For Each... Next loop since this will start from the top. If you are going to be deleting rows, you need to start from the bottom and work up. Reason for this is that say if your macro identifies row 10 for deletion, it will delete it and then look at row 11 next. However, because you deleted row 10, row 11 is now row 12 if that makes sense. Cheers, Ivan. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows if they contain a value held in a separate list
Hi Steve
No idea what I'm screwing up here! Just to be clear, the values I want to delete are on sheet2 column J the script looks like: Option Explicit Dim MyCell, MyRng As Range Dim FoundCell As Range Dim LastRow As Integer Private Sub CommandButton1_Click() Worksheets("Sheet3").Activate LastRow = [A65535].End(xlUp).Row Set MyRng = Range("J1:J" & LastRow) For Each MyCell In MyRng Set FoundCell = Worksheets("Sheet2").Cells _ ..Find(What:=MyCell, LookAt:=xlWhole) If Not FoundCell Is Nothing Then FoundCell.EntireRow.Delete End If Next MyCell End Sub when I run it I get a subscript out of range error. On Apr 4, 4:37 pm, Incidental wrote: Hi All you need to do is change the following line to reflect which sheet you are running the "FIND" on like so Set FoundCell = Worksheets("Sheet2").Cells _ .Find(What:=MyCell, LookAt:=xlWhole) The code already takes the list to search for from sheet3. i hope this makes it a little clearer for you but if you have any more problems with the code let me know and i will comment it for you. Steve |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows if they contain a value held in a separate list
On Apr 5, 3:11*am, wrote:
Hi Steve No idea what I'm screwing up here! Just to be clear, the values I want to delete are on sheet2 column J the script looks like: Option Explicit Dim MyCell, MyRng As Range Dim FoundCell As Range Dim LastRow As Integer Private Sub CommandButton1_Click() Worksheets("Sheet3").Activate LastRow = [A65535].End(xlUp).Row Set MyRng = Range("J1:J" & LastRow) For Each MyCell In MyRng Set FoundCell = Worksheets("Sheet2").Cells _ .Find(What:=MyCell, LookAt:=xlWhole) If Not FoundCell Is Nothing Then * * FoundCell.EntireRow.Delete End If Next MyCell End Sub when I run it I get a subscript out of range error. On Apr 4, 4:37 pm, Incidental wrote: Hi All you need to do is change the following line to reflect which sheet you are running the "FIND" on like so Set FoundCell = Worksheets("Sheet2").Cells _ .Find(What:=MyCell, LookAt:=xlWhole) The code already takes the list to search for from sheet3. *i hope this makes it a little clearer for you but if you have any more problems with the code let me know and i will comment it for you. Steve- Hide quoted text - - Show quoted text - Hi, Give this a try: Option Explicit Private Sub CommandButton1_Click() Dim ChkList As Range, DelRange As Range Dim LastRw As Long, i As Long LastRw = Sheets("Sheet3").Range("J65535") _ .End(xlUp).Row Set ChkList = Sheets("Sheet3").Range("J1") _ .Resize(LastRw, 1) LastRw = Sheets("Sheet2").Range("A65535") _ .End(xlUp).Row Set DelRange = Sheets("Sheet2").Range("A1") _ .Resize(LastRw, 1) For i = LastRw To 1 Step -1 If Not IsError(Application.Match _ (DelRange.Cells(i), ChkList, 0)) Then DelRange.Cells(i).EntireRow.Delete End If Next End Sub This will work from the bottom up like I suggested and should do what you need. This assumes that your data which you wish to delete your rows from is on "Sheet2" in Column "A", and that your list of values that you are checking for is on "Sheet3" in Column "J". If I got that wring, just modify the top couple of lines. Cheers, Ivan. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
deleting rows if they contain a value held in a separate list
Hi
The problem you are having with my code is that you need to change LastRow = [A65535].End(xlUp).Row to find the last value in your choosen column which is J which would be LastRow = [J65535].End(xlUp).Row. That said Ivan is right working your way down and deleting rows can cause problems so i would suggest going with his loop for the removal of the rows. I should have thought of that one D'oh!!!! Thanks for the heads up and i hope you got sorted out. Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing rows in which 1 cell's value appears on a separate list | Excel Discussion (Misc queries) | |||
deleting various rows of cell data throughout the master list tha. | Excel Discussion (Misc queries) | |||
Problem Deleting all rows from List Object | Excel Programming | |||
Deleting Rows Automatically using a Text File List | Excel Discussion (Misc queries) | |||
Deleting rows from list of files | Excel Programming |