I have a spreadsheet that will have information pasted in daily from another
source. How would I set code to automatically remove every occurance of
"Overall Result" from Column A. I know this is a worksheet change Event
Macro but I don't know the code to search in column A and remove these
entries.

Can anyone help?

I've just quickly recorded this one,
You can assign it to a button then just click on it. It will remove the cell
content if it says "Overall Results"

Sub RmoveOverallResult()

Columns("A:A").Select
Selection.Find(What:="OVERALL RESULT", After:=ActiveCell, LookIn:= _
xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext _
, MatchCase:=False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
Cells.Replace What:="OVERALL RESULT", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub


"Karen McKenzie" wrote:

I have a spreadsheet that will have information pasted in daily from another
source. How would I set code to automatically remove every occurance of
"Overall Result" from Column A. I know this is a worksheet change Event
Macro but I don't know the code to search in column A and remove these
entries.

Can anyone help?

Try this:

Sub Testing()

Dim myRange As Range, r As Range
Set myRange = Worksheets("Sheet1").Range("A1")
lrow = Cells(Rows.Count, 1).End(xlUp).Row
Debug.Print lrow
Set myRange = myRange.Resize(lrow, 1)
For Each r In myRange
Debug.Print r.Row, r.Value, r.Address

If LCase(r.Value) = "overall result" Then
Debug.Print r.Row, r.Column
r.ClearContents
End If
Next r


End Sub

"Karen McKenzie" wrote:

I have a spreadsheet that will have information pasted in daily from another
source. How would I set code to automatically remove every occurance of
"Overall Result" from Column A. I know this is a worksheet change Event
Macro but I don't know the code to search in column A and remove these
entries.

Can anyone help?

Sortdeleteresort
or a macro to filter and delete the bunch or a loop such as

for each c in range("a2:a22")
if ucase(c)="OVERALL RESULT") then c.entirerow.delete
next c

--
Don Guillett
SalesAid Software

"Karen McKenzie" wrote in message
...
I have a spreadsheet that will have information pasted in daily from
another
source. How would I set code to automatically remove every occurance of
"Overall Result" from Column A. I know this is a worksheet change Event
Macro but I don't know the code to search in column A and remove these
entries.

Can anyone help?

One mo

Option Explicit
Sub testme01()
Dim WhatToFind As String
Dim FoundCell As Range

WhatToFind = "Overall Result"

With ActiveSheet.Range("a:A")
Do
Set FoundCell = .Cells.Find(What:=WhatToFind, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'it's done
Exit Do
Else
'delete the entire row??
FoundCell.EntireRow.Delete
'or to just empty that cell???
'Foundcell.clearcontents
End If
Loop
End With

End Sub

I used xlpart when searching. You may want xlwhole if you wanted to keep cells
with text like:

This is the overall result section...

Karen McKenzie wrote:

I have a spreadsheet that will have information pasted in daily from another
source. How would I set code to automatically remove every occurance of
"Overall Result" from Column A. I know this is a worksheet change Event
Macro but I don't know the code to search in column A and remove these
entries.

Can anyone help?

--

Dave Peterson

Thanks for all the help from you all, it's really appreciated.


"Dave Peterson" wrote:

One mo

Option Explicit
Sub testme01()
Dim WhatToFind As String
Dim FoundCell As Range

WhatToFind = "Overall Result"

With ActiveSheet.Range("a:A")
Do
Set FoundCell = .Cells.Find(What:=WhatToFind, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'it's done
Exit Do
Else
'delete the entire row??
FoundCell.EntireRow.Delete
'or to just empty that cell???
'Foundcell.clearcontents
End If
Loop
End With

End Sub

I used xlpart when searching. You may want xlwhole if you wanted to keep cells
with text like:

This is the overall result section...

Karen McKenzie wrote:

I have a spreadsheet that will have information pasted in daily from another
source. How would I set code to automatically remove every occurance of
"Overall Result" from Column A. I know this is a worksheet change Event
Macro but I don't know the code to search in column A and remove these
entries.

Can anyone help?

--

Dave Peterson