Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row If Q
What code should I use if I want to delete entire Rows (and then move all
other rows below up) if the value in say Sheet1 A1:A.... is not within a named range, say 'Product Numbers'. I may have to set-up a Dynamic Range for the values in Sheet1 A1:A.... as they will change in Row numbers daily - so I would want the code to search until the last value in Column A Sheet1. I'm looking to place this code in a macro. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row If Q
Hi John
This code will scan your entire list in column A on sheet1 and check if the value found is in the list in column A on sheet2 If the value on sheet1 is not found on sheet2 then the entire row on sheet1 is deleted and all rows shifted up. It adjusts for varying lengths of data in both sheet1 and sheet2. Public Sub SelectiveDelete() Dim LRowData As Long, LRowTable As Long LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate Dim ir As Long, it As Long, NotFound As Boolean For ir = LRowData To 1 Step -1 NotFound = True For it = 1 To LRowTable If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False Next it If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp Next ir End Sub Cheers Nigel "John" wrote in message ... What code should I use if I want to delete entire Rows (and then move all other rows below up) if the value in say Sheet1 A1:A.... is not within a named range, say 'Product Numbers'. I may have to set-up a Dynamic Range for the values in Sheet1 A1:A.... as they will change in Row numbers daily - so I would want the code to search until the last value in Column A Sheet1. I'm looking to place this code in a macro. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row If Q
Thanks Nigel, I have several 'lists' in Sheet1 Column A that do not relate
to the search I am doing. In saying that I cannot specify a set range as my 'source' range can change. Would I be better specifying a Range name for my 'source' data "Nigel" wrote in message ... Hi John This code will scan your entire list in column A on sheet1 and check if the value found is in the list in column A on sheet2 If the value on sheet1 is not found on sheet2 then the entire row on sheet1 is deleted and all rows shifted up. It adjusts for varying lengths of data in both sheet1 and sheet2. Public Sub SelectiveDelete() Dim LRowData As Long, LRowTable As Long LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate Dim ir As Long, it As Long, NotFound As Boolean For ir = LRowData To 1 Step -1 NotFound = True For it = 1 To LRowTable If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False Next it If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp Next ir End Sub Cheers Nigel "John" wrote in message ... What code should I use if I want to delete entire Rows (and then move all other rows below up) if the value in say Sheet1 A1:A.... is not within a named range, say 'Product Numbers'. I may have to set-up a Dynamic Range for the values in Sheet1 A1:A.... as they will change in Row numbers daily - so I would want the code to search until the last value in Column A Sheet1. I'm looking to place this code in a macro. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row If Q
Hi John
It depends on how you are loading this list. Range names are a good way of limiting the range extent and simplify coding, but if your range extent is changing you will need to use VBA to control the addition or subtraction to/from the range, but that depends on how you load the data. The code I provided looks for the last used row in column A, it might be better to simplify your data model and limit column A as your search column or use an end of data marker to signify the last row, the code could be modifed to look for this marker rather than the used range. If as you say the length of the search list can change how do you control that other data in column A is not overwritten - or do you load this at the same time? As I say the whole approach does very much depend on your load routine. Cheers Nigel "John" wrote in message ... Thanks Nigel, I have several 'lists' in Sheet1 Column A that do not relate to the search I am doing. In saying that I cannot specify a set range as my 'source' range can change. Would I be better specifying a Range name for my 'source' data "Nigel" wrote in message ... Hi John This code will scan your entire list in column A on sheet1 and check if the value found is in the list in column A on sheet2 If the value on sheet1 is not found on sheet2 then the entire row on sheet1 is deleted and all rows shifted up. It adjusts for varying lengths of data in both sheet1 and sheet2. Public Sub SelectiveDelete() Dim LRowData As Long, LRowTable As Long LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate Dim ir As Long, it As Long, NotFound As Boolean For ir = LRowData To 1 Step -1 NotFound = True For it = 1 To LRowTable If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False Next it If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp Next ir End Sub Cheers Nigel "John" wrote in message ... What code should I use if I want to delete entire Rows (and then move all other rows below up) if the value in say Sheet1 A1:A.... is not within a named range, say 'Product Numbers'. I may have to set-up a Dynamic Range for the values in Sheet1 A1:A.... as they will change in Row numbers daily - so I would want the code to search until the last value in Column A Sheet1. I'm looking to place this code in a macro. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row If Q
Nigel, can't get the code to work, it hits compile error at the line "Next
it" and states "Next without for" - any ideas? "Nigel" wrote in message ... Hi John This code will scan your entire list in column A on sheet1 and check if the value found is in the list in column A on sheet2 If the value on sheet1 is not found on sheet2 then the entire row on sheet1 is deleted and all rows shifted up. It adjusts for varying lengths of data in both sheet1 and sheet2. Public Sub SelectiveDelete() Dim LRowData As Long, LRowTable As Long LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate Dim ir As Long, it As Long, NotFound As Boolean For ir = LRowData To 1 Step -1 NotFound = True For it = 1 To LRowTable If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False Next it If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp Next ir End Sub Cheers Nigel "John" wrote in message ... What code should I use if I want to delete entire Rows (and then move all other rows below up) if the value in say Sheet1 A1:A.... is not within a named range, say 'Product Numbers'. I may have to set-up a Dynamic Range for the values in Sheet1 A1:A.... as they will change in Row numbers daily - so I would want the code to search until the last value in Column A Sheet1. I'm looking to place this code in a macro. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row If Q
John,
The statement: If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False needs to be all on one line, or else the compiler sees an If without a Then, which causes the 'For without Next' message. hth, Doug Glancy "John" wrote in message ... Nigel, can't get the code to work, it hits compile error at the line "Next it" and states "Next without for" - any ideas? "Nigel" wrote in message ... Hi John This code will scan your entire list in column A on sheet1 and check if the value found is in the list in column A on sheet2 If the value on sheet1 is not found on sheet2 then the entire row on sheet1 is deleted and all rows shifted up. It adjusts for varying lengths of data in both sheet1 and sheet2. Public Sub SelectiveDelete() Dim LRowData As Long, LRowTable As Long LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate Dim ir As Long, it As Long, NotFound As Boolean For ir = LRowData To 1 Step -1 NotFound = True For it = 1 To LRowTable If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False Next it If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp Next ir End Sub Cheers Nigel "John" wrote in message ... What code should I use if I want to delete entire Rows (and then move all other rows below up) if the value in say Sheet1 A1:A.... is not within a named range, say 'Product Numbers'. I may have to set-up a Dynamic Range for the values in Sheet1 A1:A.... as they will change in Row numbers daily - so I would want the code to search until the last value in Column A Sheet1. I'm looking to place this code in a macro. Thanks |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row If Q
Thanks Doug, prob solved
"Doug Glancy" wrote in message ... John, The statement: If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False needs to be all on one line, or else the compiler sees an If without a Then, which causes the 'For without Next' message. hth, Doug Glancy "John" wrote in message ... Nigel, can't get the code to work, it hits compile error at the line "Next it" and states "Next without for" - any ideas? "Nigel" wrote in message ... Hi John This code will scan your entire list in column A on sheet1 and check if the value found is in the list in column A on sheet2 If the value on sheet1 is not found on sheet2 then the entire row on sheet1 is deleted and all rows shifted up. It adjusts for varying lengths of data in both sheet1 and sheet2. Public Sub SelectiveDelete() Dim LRowData As Long, LRowTable As Long LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate Dim ir As Long, it As Long, NotFound As Boolean For ir = LRowData To 1 Step -1 NotFound = True For it = 1 To LRowTable If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False Next it If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp Next ir End Sub Cheers Nigel "John" wrote in message ... What code should I use if I want to delete entire Rows (and then move all other rows below up) if the value in say Sheet1 A1:A.... is not within a named range, say 'Product Numbers'. I may have to set-up a Dynamic Range for the values in Sheet1 A1:A.... as they will change in Row numbers daily - so I would want the code to search until the last value in Column A Sheet1. I'm looking to place this code in a macro. Thanks |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row If Q
Guys, still have a problem, I tailored the code to what I thought would work
(see below). Simply I'm looking at all Rows in Sheet Sales Mix Column B for those values in Sheet Master A451 that match Sheet Sales Mix "B...". If they do match, then delete those rows in Sheet Sales Mix and move all other rows up one etc. I thought by modifying Nigels code it would work, it works great (and correct) if my Sales Mix values were in Column A, I thought it was a simple process of substituting B for A, but I stuck Thanks Public Sub SelectiveDelete() Application.ScreenUpdating = False With Application .Calculation = xlManual .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False Dim LRowData As Long, LRowTable As Long LRowData = Sheets("Sales Mix").Cells(Rows.Count, "B").End(xlUp).Row LRowTable = Sheets("Master").Range("A451") Sheets("Sales Mix").Activate Dim ir As Long, it As Long, Found As Boolean For ir = LRowTable To 1 Step -1 Found = False For it = 1 To LRowData If Cells(ir, 1).Value = Sheets("Master").Cells(it, 1).Value Then Found = True Next it If Found Then Rows(ir).EntireRow.Delete Shift:=xlUp Next ir With Application .Calculation = xlAutomatic .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False Application.ScreenUpdating = True End Sub "Doug Glancy" wrote in message ... John, The statement: If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False needs to be all on one line, or else the compiler sees an If without a Then, which causes the 'For without Next' message. hth, Doug Glancy "John" wrote in message ... Nigel, can't get the code to work, it hits compile error at the line "Next it" and states "Next without for" - any ideas? "Nigel" wrote in message ... Hi John This code will scan your entire list in column A on sheet1 and check if the value found is in the list in column A on sheet2 If the value on sheet1 is not found on sheet2 then the entire row on sheet1 is deleted and all rows shifted up. It adjusts for varying lengths of data in both sheet1 and sheet2. Public Sub SelectiveDelete() Dim LRowData As Long, LRowTable As Long LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate Dim ir As Long, it As Long, NotFound As Boolean For ir = LRowData To 1 Step -1 NotFound = True For it = 1 To LRowTable If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False Next it If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp Next ir End Sub Cheers Nigel "John" wrote in message ... What code should I use if I want to delete entire Rows (and then move all other rows below up) if the value in say Sheet1 A1:A.... is not within a named range, say 'Product Numbers'. I may have to set-up a Dynamic Range for the values in Sheet1 A1:A.... as they will change in Row numbers daily - so I would want the code to search until the last value in Column A Sheet1. I'm looking to place this code in a macro. Thanks |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row If Q
John,
Don't know if you've solved this yet, but it looks like the line: If Cells(ir, 1).Value = Sheets("Master").Cells(it, 1).Value Then Found should be: If Cells(ir, 2).Value = Sheets("Master").Cells(it, 2).Value Then Found The Cells property takes the form Cells(row, column) so from what you said, I think you want column 2. hth, Doug Glancy "John" wrote in message ... Guys, still have a problem, I tailored the code to what I thought would work (see below). Simply I'm looking at all Rows in Sheet Sales Mix Column B for those values in Sheet Master A451 that match Sheet Sales Mix "B...". If they do match, then delete those rows in Sheet Sales Mix and move all other rows up one etc. I thought by modifying Nigels code it would work, it works great (and correct) if my Sales Mix values were in Column A, I thought it was a simple process of substituting B for A, but I stuck Thanks Public Sub SelectiveDelete() Application.ScreenUpdating = False With Application .Calculation = xlManual .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False Dim LRowData As Long, LRowTable As Long LRowData = Sheets("Sales Mix").Cells(Rows.Count, "B").End(xlUp).Row LRowTable = Sheets("Master").Range("A451") Sheets("Sales Mix").Activate Dim ir As Long, it As Long, Found As Boolean For ir = LRowTable To 1 Step -1 Found = False For it = 1 To LRowData If Cells(ir, 1).Value = Sheets("Master").Cells(it, 1).Value Then Found = True Next it If Found Then Rows(ir).EntireRow.Delete Shift:=xlUp Next ir With Application .Calculation = xlAutomatic .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False Application.ScreenUpdating = True End Sub "Doug Glancy" wrote in message ... John, The statement: If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False needs to be all on one line, or else the compiler sees an If without a Then, which causes the 'For without Next' message. hth, Doug Glancy "John" wrote in message ... Nigel, can't get the code to work, it hits compile error at the line "Next it" and states "Next without for" - any ideas? "Nigel" wrote in message ... Hi John This code will scan your entire list in column A on sheet1 and check if the value found is in the list in column A on sheet2 If the value on sheet1 is not found on sheet2 then the entire row on sheet1 is deleted and all rows shifted up. It adjusts for varying lengths of data in both sheet1 and sheet2. Public Sub SelectiveDelete() Dim LRowData As Long, LRowTable As Long LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate Dim ir As Long, it As Long, NotFound As Boolean For ir = LRowData To 1 Step -1 NotFound = True For it = 1 To LRowTable If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False Next it If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp Next ir End Sub Cheers Nigel "John" wrote in message ... What code should I use if I want to delete entire Rows (and then move all other rows below up) if the value in say Sheet1 A1:A.... is not within a named range, say 'Product Numbers'. I may have to set-up a Dynamic Range for the values in Sheet1 A1:A.... as they will change in Row numbers daily - so I would want the code to search until the last value in Column A Sheet1. I'm looking to place this code in a macro. Thanks |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row If Q
Thanks again Doug, that worked. I've now my final bit to do. Instead of
looking at just one cell i.e. A451 on want to look at more, ideally a Range name but don't know how you would incorporate an named range as the lookup, i.e. everything in the named range that is in Column B in sheet Sales Mix delete. I've tried the following but it hits debug on this line LRowTable = Sheets("Master").Range("A451:A452") "Doug Glancy" wrote in message ... John, Don't know if you've solved this yet, but it looks like the line: If Cells(ir, 1).Value = Sheets("Master").Cells(it, 1).Value Then Found should be: If Cells(ir, 2).Value = Sheets("Master").Cells(it, 2).Value Then Found The Cells property takes the form Cells(row, column) so from what you said, I think you want column 2. hth, Doug Glancy "John" wrote in message ... Guys, still have a problem, I tailored the code to what I thought would work (see below). Simply I'm looking at all Rows in Sheet Sales Mix Column B for those values in Sheet Master A451 that match Sheet Sales Mix "B...". If they do match, then delete those rows in Sheet Sales Mix and move all other rows up one etc. I thought by modifying Nigels code it would work, it works great (and correct) if my Sales Mix values were in Column A, I thought it was a simple process of substituting B for A, but I stuck Thanks Public Sub SelectiveDelete() Application.ScreenUpdating = False With Application .Calculation = xlManual .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False Dim LRowData As Long, LRowTable As Long LRowData = Sheets("Sales Mix").Cells(Rows.Count, "B").End(xlUp).Row LRowTable = Sheets("Master").Range("A451") Sheets("Sales Mix").Activate Dim ir As Long, it As Long, Found As Boolean For ir = LRowTable To 1 Step -1 Found = False For it = 1 To LRowData If Cells(ir, 1).Value = Sheets("Master").Cells(it, 1).Value Then Found = True Next it If Found Then Rows(ir).EntireRow.Delete Shift:=xlUp Next ir With Application .Calculation = xlAutomatic .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False Application.ScreenUpdating = True End Sub "Doug Glancy" wrote in message ... John, The statement: If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False needs to be all on one line, or else the compiler sees an If without a Then, which causes the 'For without Next' message. hth, Doug Glancy "John" wrote in message ... Nigel, can't get the code to work, it hits compile error at the line "Next it" and states "Next without for" - any ideas? "Nigel" wrote in message ... Hi John This code will scan your entire list in column A on sheet1 and check if the value found is in the list in column A on sheet2 If the value on sheet1 is not found on sheet2 then the entire row on sheet1 is deleted and all rows shifted up. It adjusts for varying lengths of data in both sheet1 and sheet2. Public Sub SelectiveDelete() Dim LRowData As Long, LRowTable As Long LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate Dim ir As Long, it As Long, NotFound As Boolean For ir = LRowData To 1 Step -1 NotFound = True For it = 1 To LRowTable If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False Next it If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp Next ir End Sub Cheers Nigel "John" wrote in message ... What code should I use if I want to delete entire Rows (and then move all other rows below up) if the value in say Sheet1 A1:A.... is not within a named range, say 'Product Numbers'. I may have to set-up a Dynamic Range for the values in Sheet1 A1:A.... as they will change in Row numbers daily - so I would want the code to search until the last value in Column A Sheet1. I'm looking to place this code in a macro. Thanks |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delete Entire Row If Q
John,
In the original code from Nigel, the line you referred to, with the changes we've made, would look like: LRowTable = Sheets("Master").Cells(Rows.Count, "B").End(xlUp).Row so I'd try changing it to that. This is finds the last row with data in sheet Master, column B. Your delete function can then search backwards from that spot. LRowTable can only be one cell, not a range, because it's a starting point for the whole find and delete loop. If that change doesn't work, I'd start a new thread, describing exactly where you're at. hth, Doug Glancy "John" wrote in message ... Thanks again Doug, that worked. I've now my final bit to do. Instead of looking at just one cell i.e. A451 on want to look at more, ideally a Range name but don't know how you would incorporate an named range as the lookup, i.e. everything in the named range that is in Column B in sheet Sales Mix delete. I've tried the following but it hits debug on this line LRowTable = Sheets("Master").Range("A451:A452") "Doug Glancy" wrote in message ... John, Don't know if you've solved this yet, but it looks like the line: If Cells(ir, 1).Value = Sheets("Master").Cells(it, 1).Value Then Found should be: If Cells(ir, 2).Value = Sheets("Master").Cells(it, 2).Value Then Found The Cells property takes the form Cells(row, column) so from what you said, I think you want column 2. hth, Doug Glancy "John" wrote in message ... Guys, still have a problem, I tailored the code to what I thought would work (see below). Simply I'm looking at all Rows in Sheet Sales Mix Column B for those values in Sheet Master A451 that match Sheet Sales Mix "B...". If they do match, then delete those rows in Sheet Sales Mix and move all other rows up one etc. I thought by modifying Nigels code it would work, it works great (and correct) if my Sales Mix values were in Column A, I thought it was a simple process of substituting B for A, but I stuck Thanks Public Sub SelectiveDelete() Application.ScreenUpdating = False With Application .Calculation = xlManual .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False Dim LRowData As Long, LRowTable As Long LRowData = Sheets("Sales Mix").Cells(Rows.Count, "B").End(xlUp).Row LRowTable = Sheets("Master").Range("A451") Sheets("Sales Mix").Activate Dim ir As Long, it As Long, Found As Boolean For ir = LRowTable To 1 Step -1 Found = False For it = 1 To LRowData If Cells(ir, 1).Value = Sheets("Master").Cells(it, 1).Value Then Found = True Next it If Found Then Rows(ir).EntireRow.Delete Shift:=xlUp Next ir With Application .Calculation = xlAutomatic .MaxChange = 0.001 End With ActiveWorkbook.PrecisionAsDisplayed = False Application.ScreenUpdating = True End Sub "Doug Glancy" wrote in message ... John, The statement: If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False needs to be all on one line, or else the compiler sees an If without a Then, which causes the 'For without Next' message. hth, Doug Glancy "John" wrote in message ... Nigel, can't get the code to work, it hits compile error at the line "Next it" and states "Next without for" - any ideas? "Nigel" wrote in message ... Hi John This code will scan your entire list in column A on sheet1 and check if the value found is in the list in column A on sheet2 If the value on sheet1 is not found on sheet2 then the entire row on sheet1 is deleted and all rows shifted up. It adjusts for varying lengths of data in both sheet1 and sheet2. Public Sub SelectiveDelete() Dim LRowData As Long, LRowTable As Long LRowData = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row LRowTable = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Sheet1").Activate Dim ir As Long, it As Long, NotFound As Boolean For ir = LRowData To 1 Step -1 NotFound = True For it = 1 To LRowTable If Cells(ir, 1).Value = Sheets("Sheet2").Cells(it, 1).Value Then NotFound = False Next it If NotFound Then Rows(ir).EntireRow.Delete Shift:=xlUp Next ir End Sub Cheers Nigel "John" wrote in message ... What code should I use if I want to delete entire Rows (and then move all other rows below up) if the value in say Sheet1 A1:A.... is not within a named range, say 'Product Numbers'. I may have to set-up a Dynamic Range for the values in Sheet1 A1:A.... as they will change in Row numbers daily - so I would want the code to search until the last value in Column A Sheet1. I'm looking to place this code in a macro. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete an entire row | Excel Discussion (Misc queries) | |||
Delete entire row if | Excel Discussion (Misc queries) | |||
Another delete entire row | Excel Programming | |||
Delete entire row if A1,2,3... is empty | Excel Programming | |||
Delete Entire Row. | Excel Programming |