macro to delete lines based on a value

[MarkT]

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

 

[smw226 via OfficeKB.com]

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

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.aspx/ms-excel/200610/1

 

[Gary L Brown]

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,

 

[Sandy]

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

 

[MarkT]

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.