Remove Cell Contents Macro
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? |
Remove Cell Contents Macro
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? |
Remove Cell Contents Macro
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? |
Remove Cell Contents Macro
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? |
Remove Cell Contents Macro
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 |
Remove Cell Contents Macro
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 |
All times are GMT +1. The time now is 11:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com