You are here:

Excel/automatic name deletion

Advertisement


Question
QUESTION: Hi Tom

Is such a thing possible?  I have two worksheets, “Sheet1” and “Sheet2”.  On “Sheet1”  there is a list of names in “column A”.  “Sheet 1” is veryhidden.  I wish to occasionally delete names in “Sheet1”

If, say, the name “Williams, Charlie”  were written somewhere in “column A” of “Sheet1”,  could a macro be written, in “Sheet2” that would allow me to delete it?

Thanks in advance

Chris Mitchell

ANSWER: Chris Mitchell,

I wouldn't necessarily write the macro in the code module of Sheet2, but you could.  

In any event, a macro can work on an xlVeryHidden sheet for most things.


Dim sh as worksheet, r as Range, cell as Range
set sh = worksheets("Sheet1")
set r = sh.Range("A1",sh.Cells(sh.rows.count,1).End(xlup))
for each cell in r
 if instr(1, cell.value, "Williams, Charlie", vbTextcompare) > 0 then
      cell.clearcontents
 end if
Next

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Hi Tom

Your macro worked well.  There was one limitation caused by my imprecise explanation.

Is it possible to write a name in cell A1 ( of any sheet other than sheet 1) and have the macro test if it appears in column A Sheet1 and remove it, should it occur?



Public Sub removenames()

Dim sh As Worksheet, r As Range, cell As Range
Set sh = Worksheets("Sheet1")
Set r = sh.Range("A1", sh.Cells(sh.Rows.Count, 1).End(xlUp))
For Each cell In r
If InStr(1, cell.Value, "Chung, Kristina", vbTextCompare) > 0 Then
     cell.ClearContents
End If
Next

End Sub

ANSWER: Chris,

Public Sub removenames()

Dim sh As Worksheet, r As Range, cell As Range
Dim sh1 as Worksheet, s as String
set sh1 = Activesheet
s = sh1.Range("A1")
Set sh = Worksheets("Sheet1")
Set r = sh.Range("A1", sh.Cells(sh.Rows.Count, 1).End(xlUp))
if application.Countif(r, s) then
For Each cell In r
  If InStr(1, cell.Value, s, vbTextCompare) > 0 Then
    cell.ClearContents
  End If
Next
End if

End Sub

--
Regards,
Tom Ogilvy



---------- FOLLOW-UP ----------

QUESTION: Hi Tom

One last question.  I have adjusted the macro in the hope that I could delete a batch of names.  I changed the range to "A1:A100"  Unfortunately this did not work.  Is this not possible or have I just got the syntax wrong?

Thanks again

Chris Mitchell

Public Sub removenames()

Dim sh As Worksheet, r As Range, cell As Range
Dim sh1 as Worksheet, s as String
set sh1 = Activesheet
s = sh1.Range("A1:A100")
Set sh = Worksheets("Sheet1")
Set r = sh.Range("A1:A100", sh.Cells(sh.Rows.Count, 1).End(xlUp))
if application.Countif(r, s) then
For Each cell In r
 If InStr(1, cell.Value, s, vbTextCompare) > 0 Then
   cell.ClearContents
 End If
Next
End if

End Sub

Answer
Chris,

You have to loop through the list of candidate names to delete.  Then for each one, search column A.  So yes, it is possible.

Public Sub removenames()

Dim sh As Worksheet, r As Range, cell As Range
Dim sh1 as Worksheet, s as String, cell1 as Range
set sh1 = Activesheet
for each cell1 in Sh1.Range("A1:A1000")
 s = ""
 s = sh1.Range("A1").Value
 if len(trim(s)) > 0 then
   Set sh = Worksheets("Sheet1")
   Set r = sh.Range("A1", sh.Cells(sh.Rows.Count, 1).End(xlUp))
   if application.Countif(r, s) then
     For Each cell In r
      If InStr(1, cell.Value, s, vbTextCompare) > 0 Then
         cell.ClearContents
      End If
     Next
   End if
 End if
Next
End Sub

--
Regards,
Tom Ogilvy  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.