You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- automatic name deletion

Advertisement

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

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

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

Answers by Expert:

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.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.