Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to keep 15 specific records and delete 1000 other rows.
Hi Gang, I work inventory control for a company in Texas and have to check stock levels for around 15 parts daily. The download for these parts contain an additional 1000 other rows of part numbers I dont need. I have used Excel macros in the past that delete rows based on specific criteria but how do I do the reverse? Essentially use a macro that says "keep these 15 specific part numbers" but delete everything else that isnt them. I posted this question mistakenly in an Access forum so my aplogies if this looks like a double post. Your help is greatly appreciated. Sam |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to keep 15 specific records and delete 1000 other rows.
You don't say how many of each or if they are together or separated
1 a 1 b 2 a 4 b 8 c -- Don Guillett SalesAid Software "Sam" wrote in message ps.com... Hi Gang, I work inventory control for a company in Texas and have to check stock levels for around 15 parts daily. The download for these parts contain an additional 1000 other rows of part numbers I dont need. I have used Excel macros in the past that delete rows based on specific criteria but how do I do the reverse? Essentially use a macro that says "keep these 15 specific part numbers" but delete everything else that isnt them. I posted this question mistakenly in an Access forum so my aplogies if this looks like a double post. Your help is greatly appreciated. Sam |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to keep 15 specific records and delete 1000 other rows.
As a starter
Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False For i = iLastRow To 1 Step -1 Select Case Cells(i, "A").Value Case "value 1", "value 2", "value 3", _ "value 4", "value 5", "value 6", _ "value 7", "value 8", "value 9", _ "value 10", "value 11", "value 12", _ "value 13", "value 14", "value 15": Case Else: Rows(i).Delete End Select Next i Application.ScreenUpdating = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sam" wrote in message ps.com... Hi Gang, I work inventory control for a company in Texas and have to check stock levels for around 15 parts daily. The download for these parts contain an additional 1000 other rows of part numbers I dont need. I have used Excel macros in the past that delete rows based on specific criteria but how do I do the reverse? Essentially use a macro that says "keep these 15 specific part numbers" but delete everything else that isnt them. I posted this question mistakenly in an Access forum so my aplogies if this looks like a double post. Your help is greatly appreciated. Sam |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to keep 15 specific records and delete 1000 other rows.
Hi Don,
I just wanted to keep any of the "part" numbers that came up in "column" a. Pardon if I wasnt more specific. I guess the answer would be any in only one column (together) . Thank you so much Bob your solution was spot on. Jus to be curious I tinkered with it a bit and heres what I ended up with. Also by turing off the updating at the end caused it to run significantly faster? In any case thank you so much for your help! Sam Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False For i = iLastRow To 1 Step -1 Select Case Cells(i, "A").Value Case "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15": _ Case Else: Rows(i).Delete End Select Next i Application.ScreenUpdating = False End Sub Bob Phillips wrote: As a starter Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False For i = iLastRow To 1 Step -1 Select Case Cells(i, "A").Value Case "value 1", "value 2", "value 3", _ "value 4", "value 5", "value 6", _ "value 7", "value 8", "value 9", _ "value 10", "value 11", "value 12", _ "value 13", "value 14", "value 15": Case Else: Rows(i).Delete End Select Next i Application.ScreenUpdating = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sam" wrote in message ps.com... Hi Gang, I work inventory control for a company in Texas and have to check stock levels for around 15 parts daily. The download for these parts contain an additional 1000 other rows of part numbers I dont need. I have used Excel macros in the past that delete rows based on specific criteria but how do I do the reverse? Essentially use a macro that says "keep these 15 specific part numbers" but delete everything else that isnt them. I posted this question mistakenly in an Access forum so my aplogies if this looks like a double post. Your help is greatly appreciated. Sam |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to keep 15 specific records and delete 1000 other rows.
That doesn't make any sense Sam, all the work is done by then. Plus, you
might be messing other stuff up by not getting screen updating back. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sam" wrote in message oups.com... Hi Don, I just wanted to keep any of the "part" numbers that came up in "column" a. Pardon if I wasnt more specific. I guess the answer would be any in only one column (together) . Thank you so much Bob your solution was spot on. Jus to be curious I tinkered with it a bit and heres what I ended up with. Also by turing off the updating at the end caused it to run significantly faster? In any case thank you so much for your help! Sam Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False For i = iLastRow To 1 Step -1 Select Case Cells(i, "A").Value Case "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15": _ Case Else: Rows(i).Delete End Select Next i Application.ScreenUpdating = False End Sub Bob Phillips wrote: As a starter Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False For i = iLastRow To 1 Step -1 Select Case Cells(i, "A").Value Case "value 1", "value 2", "value 3", _ "value 4", "value 5", "value 6", _ "value 7", "value 8", "value 9", _ "value 10", "value 11", "value 12", _ "value 13", "value 14", "value 15": Case Else: Rows(i).Delete End Select Next i Application.ScreenUpdating = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sam" wrote in message ps.com... Hi Gang, I work inventory control for a company in Texas and have to check stock levels for around 15 parts daily. The download for these parts contain an additional 1000 other rows of part numbers I dont need. I have used Excel macros in the past that delete rows based on specific criteria but how do I do the reverse? Essentially use a macro that says "keep these 15 specific part numbers" but delete everything else that isnt them. I posted this question mistakenly in an Access forum so my aplogies if this looks like a double post. Your help is greatly appreciated. Sam |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to keep 15 specific records and delete 1000 other rows.
That doesn't make any sense Sam, all the work is done by then.
Application.ScreenUpdating = False Not the first time he calls it. (which is above the start of the loop) - maybe you missed it. The second time should be set to true rather than false, but is immaterial as it is turned back on by default it this is run independently which I am sure it is. -- Regards, Tom Ogilvy "Bob Phillips" wrote in message ... That doesn't make any sense Sam, all the work is done by then. Plus, you might be messing other stuff up by not getting screen updating back. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sam" wrote in message oups.com... Hi Don, I just wanted to keep any of the "part" numbers that came up in "column" a. Pardon if I wasnt more specific. I guess the answer would be any in only one column (together) . Thank you so much Bob your solution was spot on. Jus to be curious I tinkered with it a bit and heres what I ended up with. Also by turing off the updating at the end caused it to run significantly faster? In any case thank you so much for your help! Sam Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False For i = iLastRow To 1 Step -1 Select Case Cells(i, "A").Value Case "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15": _ Case Else: Rows(i).Delete End Select Next i Application.ScreenUpdating = False End Sub Bob Phillips wrote: As a starter Sub Test() Dim iLastRow As Long Dim i As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row Application.ScreenUpdating = False For i = iLastRow To 1 Step -1 Select Case Cells(i, "A").Value Case "value 1", "value 2", "value 3", _ "value 4", "value 5", "value 6", _ "value 7", "value 8", "value 9", _ "value 10", "value 11", "value 12", _ "value 13", "value 14", "value 15": Case Else: Rows(i).Delete End Select Next i Application.ScreenUpdating = True End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Sam" wrote in message ps.com... Hi Gang, I work inventory control for a company in Texas and have to check stock levels for around 15 parts daily. The download for these parts contain an additional 1000 other rows of part numbers I dont need. I have used Excel macros in the past that delete rows based on specific criteria but how do I do the reverse? Essentially use a macro that says "keep these 15 specific part numbers" but delete everything else that isnt them. I posted this question mistakenly in an Access forum so my aplogies if this looks like a double post. Your help is greatly appreciated. Sam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete rows containing specific data | New Users to Excel | |||
Macro to delete specific rows above selected cell | Excel Programming | |||
Macro to search across multiple columns and delete rows not containing specific value | Excel Programming | |||
macro to plot charts of 1000 records automatically possible? | Excel Programming | |||
Macro to delete specific rows | Excel Programming |