Really Silly Problem

Hi all,
In my program generated report, I have

Agent: XX
Total: YY
Monday ---
Tuedday ---
....... Sunday

Now, we have about 157 agents. My boss is only interested in the Agent name and the total. How do I delete the days and the rows using vba?? tried using the Find & Replace, but cannot get it to delete the rows as well. Thanks

Please give us more info

OK..
We use a software by bluepumpkin, to control call centre agent adherence. At the end of the week, I will export a report generated by the software to Excel format. The report looks something like:

Agent 1
Total : xxx
-- Breakdown --
Monday
Tuesday
... all the way till Sunday

Agent 2
Total : yyy hrs
-- Breakdown --
Monday - a hrs
Tuesday - b hrs
... all the way till Sunday

My boss is only interested in the Total. How do I Delete all the rows that contain (Monday, Tuesday, Wednesday..... Sunday)?? I tried using the Replace command, but it won't delete entire rows. Help, guys!! Thanks

[Tom Urtis]

Well, here's one way to attack it. Seeing as every day of the week has a name that ends in the same 3 letters "d-a-y", and none of your wanted text ends in "d-a-y", how about hunting down those cells in column A (assuming that's where the days are listed from your import) that end in "d-a-y", and deleting them. Try this macro to see if we are on the right track. First though, copy your data onto a test worksheet, to be sure you don't lose data if this is not what you want.

''''''''''''''''''''''''''''''''''''''''

Sub DeleteDays()
Application.ScreenUpdating = False
Dim theCol As Range, cell As Range, RtoSel As Range
Dim LtoSel As String
Set theCol = Range(Range("A1"), Range("A65536").End(xlUp))
LtoSel = "day"
For Each cell In theCol
If Right(cell, 3) = LtoSel Then
If RtoSel Is Nothing Then
Set RtoSel = cell
Else
Set RtoSel = Application.Union(RtoSel, cell)
End If
End If
Next
On Error GoTo e
RtoSel.EntireRow.Delete
[A1].Select
Application.ScreenUpdating = True
Exit Sub
e:
MsgBox "There are no days to delete.", 64, "Time for a beer !"
[A1].Select
End Sub

'''''''''''''''''''''''''''''''''''''''

HTH

[Mark W.]

This can be easily accomplished with a Custom AutoFilter (see the Data | Filter | AutoFilter... menu command) using the following criteria...

Show rows where:
does not begin with | Agent
And
does not begin with | Total

After applying the filter delete all visible rows.

[ This Message was edited by: Mark W. on 2002-03-05 07:18 ]