Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to delete lines based on a value
Greetings all:
I have a spreadsheet that varies in length. It is sorted by account name. There are three different account names (Sales, Sales Tax & Net Amount). Each sale that our sales-reps create during the month generates three lines in this spreadsheet when I export it from our accounting package (peachtree) into excel (I am using Excel 2003). This is my question: Can I create a macro that will delete all lines that contain the account name of Sales Tax or Sales just leaving the lines that has the account name of Net Amount? I would then also like to sort the results by sales rep name. Is this possible? Any help on this would be greatly appreciated. I run this program a number of times during the week, and the sort routine is getting quite old - there has to be a better way to perfom this routine. I would create a macro to record my keystrokes, but again, the number of lines of each account varies during the month. Thanks again! Mark |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to delete lines based on a value
Hi Mark,
This is quite easy todo in code, but deleting data does present a risk so I, personally, would create a pivot table as this will safely filter (rather than delete) the rows you do not want to include. You could also sort by sales rep and they auto filter only on Net Amount HTH Simon MarkT wrote: Greetings all: I have a spreadsheet that varies in length. It is sorted by account name. There are three different account names (Sales, Sales Tax & Net Amount). Each sale that our sales-reps create during the month generates three lines in this spreadsheet when I export it from our accounting package (peachtree) into excel (I am using Excel 2003). This is my question: Can I create a macro that will delete all lines that contain the account name of Sales Tax or Sales just leaving the lines that has the account name of Net Amount? I would then also like to sort the results by sales rep name. Is this possible? Any help on this would be greatly appreciated. I run this program a number of times during the week, and the sort routine is getting quite old - there has to be a better way to perfom this routine. I would create a macro to record my keystrokes, but again, the number of lines of each account varies during the month. Thanks again! Mark -- -------------------- Simon - UK Email at simon22mports [ a t ] hot mail [ d ot ]com Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200610/1 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to delete lines based on a value
Try this to get you started it assumes that column "A" is where your
account name is...you'll need to tell us what the sales rep column is to code the sort. Sub MyDelete() Dim EndCell, MyRange As Range Set MyRange = Range("A1", Cells(Rows.Count, "A").End(xlUp).Address) Set EndCell = Range(Cells(1, 1), Cells(Rows.Count, "A").End(xlUp)) For i = EndCell.Count To 1 Step -1 If Cells(i, 1).Value = "Sales" Or Cells(i, 1).Value = "Sales Tax" Then Cells(i, 1).EntireRow.Delete End If Next i End Sub Sandy Gary L Brown wrote: Mark, This was very well written BUT You knew there was a but coming, right? :O In a new posting, copy this well written request but add an example of what your data looks like coming out of Peachtree and what you want it to look like. Sincerely, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "MarkT" wrote: Greetings all: I have a spreadsheet that varies in length. It is sorted by account name. There are three different account names (Sales, Sales Tax & Net Amount). Each sale that our sales-reps create during the month generates three lines in this spreadsheet when I export it from our accounting package (peachtree) into excel (I am using Excel 2003). This is my question: Can I create a macro that will delete all lines that contain the account name of Sales Tax or Sales just leaving the lines that has the account name of Net Amount? I would then also like to sort the results by sales rep name. Is this possible? Any help on this would be greatly appreciated. I run this program a number of times during the week, and the sort routine is getting quite old - there has to be a better way to perfom this routine. I would create a macro to record my keystrokes, but again, the number of lines of each account varies during the month. Thanks again! Mark |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
macro to delete lines based on a value
Thanks Gary, I re-wrote the posting as you have requested and have included
additional information; I do try to provide all relevant info, but now I see that this certainly would have been helpfull to you. Thank you very much for your time and in helping me. "Gary L Brown" wrote: Mark, This was very well written BUT You knew there was a but coming, right? :O In a new posting, copy this well written request but add an example of what your data looks like coming out of Peachtree and what you want it to look like. Sincerely, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "MarkT" wrote: Greetings all: I have a spreadsheet that varies in length. It is sorted by account name. There are three different account names (Sales, Sales Tax & Net Amount). Each sale that our sales-reps create during the month generates three lines in this spreadsheet when I export it from our accounting package (peachtree) into excel (I am using Excel 2003). This is my question: Can I create a macro that will delete all lines that contain the account name of Sales Tax or Sales just leaving the lines that has the account name of Net Amount? I would then also like to sort the results by sales rep name. Is this possible? Any help on this would be greatly appreciated. I run this program a number of times during the week, and the sort routine is getting quite old - there has to be a better way to perfom this routine. I would create a macro to record my keystrokes, but again, the number of lines of each account varies during the month. Thanks again! Mark |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete workbook after use | Excel Discussion (Misc queries) | |||
Delete Duplicate Rows Macro Question | Excel Discussion (Misc queries) | |||
Macro to delete a group of CELLS | Excel Discussion (Misc queries) | |||
delete pictures with a macro | Excel Discussion (Misc queries) | |||
Help: I need a macro to add words every 3 lines | Excel Discussion (Misc queries) |