Delete Specific Contents
 

I would like to create a macro that will search Column A and every time it
finds a cell that says "User" or "Theodore Galin" the contents of that cell
will be deleted. Can you help me with this?

Delete Specific Contents
 

Dear Galin

Please find the below code. Incase you are not familiar with macros follow
the below instructions. Launch VBE using Alt+F11. On the left treeview, right
click 'This Workbook' -- Insert-- Module . Paste the code. Goto workbook
and Run Macro1 from Tools--Macro

If this post helps click Yes
---------------
Jacob Skaria

Sub Macro1()

intRow = ActiveSheet.Range("A65536").End(xlUp).Row
For intTemp = 1 To intRow
If Range("A" & intTemp) = "User" Or Range("A" & intTemp) = "Theodore Galin"
Then
Range("A" & intTemp) = ""
End If
Next

End Sub

Delete Specific Contents
 

hi,

Right click your sheet tab, view code and paste this in and run it. If you
mean contains those 2 string among other thext then change =1 to 0

Sub Sonic()
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
If InStr(1, c.Value, "user", vbTextCompare) = 1 Or _
InStr(1, c.Value, "Theodore Galin", vbTextCompare) = 1 Then
c.ClearContents
End If
Next
End Sub


Mike

"TGalin" wrote:

I would like to create a macro that will search Column A and every time it
finds a cell that says "User" or "Theodore Galin" the contents of that cell
will be deleted. Can you help me with this?

Delete Specific Contents
 

This will do it:
Sub step_backwards()
Dim myLastRow As Long
Dim r As Long
Dim c As Range
myLastRow = ActiveSheet.Cells(10000, 1).End(xlUp).Row
For r = myLastRow To 1 Step -1
Set c = ActiveSheet.Range("a" & r)
If c.Value = "User" Or c.Value = "Theodore Galin" Then
c.ClearContents
End If
Next r
End Sub


Use this if you want the entire row to shift up as well as delete the
contents of the cell:
Sub step_backwards()
Dim myLastRow As Long
Dim r As Long
Dim c As Range
myLastRow = ActiveSheet.Cells(10000, 1).End(xlUp).Row
For r = myLastRow To 1 Step -1
Set c = ActiveSheet.Range("a" & r)
If c.Value = "User" Or c.Value = "Theodore Galin" Then
c.EntireRow.Delete
End If
Next r
End Sub

Remember, backup your data before you start deletin' stuff!! Just in case
it does something you don't expect.

Regards,
Ryan---

--
RyGuy


"Mike H" wrote:

hi,

Right click your sheet tab, view code and paste this in and run it. If you
mean contains those 2 string among other thext then change =1 to 0

Sub Sonic()
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
If InStr(1, c.Value, "user", vbTextCompare) = 1 Or _
InStr(1, c.Value, "Theodore Galin", vbTextCompare) = 1 Then
c.ClearContents
End If
Next
End Sub


Mike

"TGalin" wrote:

I would like to create a macro that will search Column A and every time it
finds a cell that says "User" or "Theodore Galin" the contents of that cell
will be deleted. Can you help me with this?

Delete Specific Contents
 

Cool! It works great thank you! I appreciate you're help.

"Jacob Skaria" wrote:

Dear Galin

Please find the below code. Incase you are not familiar with macros follow
the below instructions. Launch VBE using Alt+F11. On the left treeview, right
click 'This Workbook' -- Insert-- Module . Paste the code. Goto workbook
and Run Macro1 from Tools--Macro

If this post helps click Yes
---------------
Jacob Skaria

Sub Macro1()

intRow = ActiveSheet.Range("A65536").End(xlUp).Row
For intTemp = 1 To intRow
If Range("A" & intTemp) = "User" Or Range("A" & intTemp) = "Theodore Galin"
Then
Range("A" & intTemp) = ""
End If
Next

End Sub

Delete Specific Contents
 

It works... Nice! I appreciate you're help; Thank you.

"Mike H" wrote:

hi,

Right click your sheet tab, view code and paste this in and run it. If you
mean contains those 2 string among other thext then change =1 to 0

Sub Sonic()
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
If InStr(1, c.Value, "user", vbTextCompare) = 1 Or _
InStr(1, c.Value, "Theodore Galin", vbTextCompare) = 1 Then
c.ClearContents
End If
Next
End Sub


Mike

"TGalin" wrote:

I would like to create a macro that will search Column A and every time it
finds a cell that says "User" or "Theodore Galin" the contents of that cell
will be deleted. Can you help me with this?

Delete Specific Contents
 

Sweet..it works! You're help is very much appreciated; Thank you!

"ryguy7272" wrote:

This will do it:
Sub step_backwards()
Dim myLastRow As Long
Dim r As Long
Dim c As Range
myLastRow = ActiveSheet.Cells(10000, 1).End(xlUp).Row
For r = myLastRow To 1 Step -1
Set c = ActiveSheet.Range("a" & r)
If c.Value = "User" Or c.Value = "Theodore Galin" Then
c.ClearContents
End If
Next r
End Sub


Use this if you want the entire row to shift up as well as delete the
contents of the cell:
Sub step_backwards()
Dim myLastRow As Long
Dim r As Long
Dim c As Range
myLastRow = ActiveSheet.Cells(10000, 1).End(xlUp).Row
For r = myLastRow To 1 Step -1
Set c = ActiveSheet.Range("a" & r)
If c.Value = "User" Or c.Value = "Theodore Galin" Then
c.EntireRow.Delete
End If
Next r
End Sub

Remember, backup your data before you start deletin' stuff!! Just in case
it does something you don't expect.

Regards,
Ryan---

--
RyGuy


"Mike H" wrote:

hi,

Right click your sheet tab, view code and paste this in and run it. If you
mean contains those 2 string among other thext then change =1 to 0

Sub Sonic()
Dim MyRange, MyRange1 As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Range("A1:A" & LastRow)
For Each c In MyRange
If InStr(1, c.Value, "user", vbTextCompare) = 1 Or _
InStr(1, c.Value, "Theodore Galin", vbTextCompare) = 1 Then
c.ClearContents
End If
Next
End Sub


Mike

"TGalin" wrote:

I would like to create a macro that will search Column A and every time it
finds a cell that says "User" or "Theodore Galin" the contents of that cell
will be deleted. Can you help me with this?

Delete Specific Contents
 

I would think this macro will work and be quite quick as well...

Sub RemoveUserAndTheodoreGalin()
Dim C As Range
On Error Resume Next
Set C = Range("A:A").Find(What:="User", LookAt:=xlWhole)
Do While Not C Is Nothing
C.Clear
Set C = Range("A:A").FindNext(C)
Loop
Set C = Range("A:A").Find(What:="Theodore Galin", LookAt:=xlWhole)
Do While Not C Is Nothing
C.Clear
Set C = Range("A:A").FindNext(C)
Loop
End Sub

--
Rick (MVP - Excel)


"TGalin" wrote in message
...
I would like to create a macro that will search Column A and every time it
finds a cell that says "User" or "Theodore Galin" the contents of that
cell
will be deleted. Can you help me with this?

Delete Specific Contents
 

This would probably be considered "cleaner" and also more "proper" as it
specifies the worksheet to apply the code to (so, change my example
worksheet name of "Sheet1" to whatever your actual worksheet name is)...

Sub RemoveUserAndTheodoreGalin()
Dim C As Range, R As Range, V As Variant
On Error Resume Next
Set R = Worksheets("Sheet1").Range("A:A")
For Each V In Array("User", "Theodore Galin")
Set C = R.Find(What:=V, LookAt:=xlWhole)
Do While Not C Is Nothing
C.Clear
Set C = R.FindNext(C)
Loop
Next
End Sub

Note that you can expand the functionality of this code by simply adding
more text strings to the Array function call if necessary.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
I would think this macro will work and be quite quick as well...

Sub RemoveUserAndTheodoreGalin()
Dim C As Range
On Error Resume Next
Set C = Range("A:A").Find(What:="User", LookAt:=xlWhole)
Do While Not C Is Nothing
C.Clear
Set C = Range("A:A").FindNext(C)
Loop
Set C = Range("A:A").Find(What:="Theodore Galin", LookAt:=xlWhole)
Do While Not C Is Nothing
C.Clear
Set C = Range("A:A").FindNext(C)
Loop
End Sub

--
Rick (MVP - Excel)


"TGalin" wrote in message
...
I would like to create a macro that will search Column A and every time it
finds a cell that says "User" or "Theodore Galin" the contents of that
cell
will be deleted. Can you help me with this?

Delete Specific Contents
 

Try this one it gives you the control of choosing which column to search and
which values:

Sub ClearSpecifiedValue()
Dim Sht As Worksheet, Rng As Range, LastRow As Long
Dim Str As String, Col As Variant
Dim i As Long

Col = InputBox("Enter the column you want to search")
If Col = "" Then Exit Sub

On Error Resume Next
Set Rng = Cells(1, Col)

Do Until Err.Number = 0
Err.Clear
Set Rng = Cells(1, Col)
If Err < 0 Then
MsgBox "Enter a valid Column in text format i.e. A, B..."
Col = InputBox("Enter the column you want to search")
Else
Exit Do
End If
Loop

'Get last row in specified column
Set Sht = ActiveSheet
With Sht
If .Cells(.Rows.Count, Col) < "" And .Cells(.Rows.Count) < 0 Then
LastRow = .Rows.Count
Else
LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row
End If

Str = InputBox("Which value would you like to to delete?")
If Str = "" Then Exit Sub

For i = 1 To LastRow
If .Cells(i, Col) = Str Then .Cells(i, Col).ClearContents
Next i
End With
End Sub

The above code is case sensitive, if you want it to be not case sensitive
change the last If statement with the following:

If UCase(.Cells(i, Col)) = UCase(Str) Then .Cells(i, Col).ClearContents

Hope this helps
--
A. Ch. Eirinberg


"TGalin" wrote:

I would like to create a macro that will search Column A and every time it
finds a cell that says "User" or "Theodore Galin" the contents of that cell
will be deleted. Can you help me with this?

Delete Specific Contents
 

This one is the same as before, only I've added a message that tells you how
many values have been deleted:


Sub ClearSpecifiedValue2()
Dim Sht As Worksheet, Rng As Range, LastRow As Long
Dim Str As String, Col As Variant
Dim i As Long, x As Long

Col = InputBox("Enter the column you want to search")
If Col = "" Then Exit Sub

On Error Resume Next
Set Rng = Cells(1, Col)

Do Until Err.Number = 0
Err.Clear
Set Rng = Cells(1, Col)
If Err < 0 Then
MsgBox "Enter a valid Column in text format i.e. A, B..."
Col = InputBox("Enter the column you want to search")
Else
Exit Do
End If
Loop

'Get last row in specified column
Set Sht = ActiveSheet
With Sht
If .Cells(.Rows.Count, Col) < "" And .Cells(.Rows.Count) < 0 Then
LastRow = .Rows.Count
Else
LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row
End If

Str = InputBox("Which value would you like to to delete?")
If Str = "" Then Exit Sub

x = 0
For i = 1 To LastRow
If .Cells(i, Col) = Str Then
.Cells(i, Col).ClearContents
x = x + 1
End If
Next i
End With

MsgBox x & " " & Str & " Deleted."
End Sub


--
A. Ch. Eirinberg


"Howard31" wrote:

Try this one it gives you the control of choosing which column to search and
which values:

Sub ClearSpecifiedValue()
Dim Sht As Worksheet, Rng As Range, LastRow As Long
Dim Str As String, Col As Variant
Dim i As Long

Col = InputBox("Enter the column you want to search")
If Col = "" Then Exit Sub

On Error Resume Next
Set Rng = Cells(1, Col)

Do Until Err.Number = 0
Err.Clear
Set Rng = Cells(1, Col)
If Err < 0 Then
MsgBox "Enter a valid Column in text format i.e. A, B..."
Col = InputBox("Enter the column you want to search")
Else
Exit Do
End If
Loop

'Get last row in specified column
Set Sht = ActiveSheet
With Sht
If .Cells(.Rows.Count, Col) < "" And .Cells(.Rows.Count) < 0 Then
LastRow = .Rows.Count
Else
LastRow = .Cells(.Rows.Count, Col).End(xlUp).Row
End If

Str = InputBox("Which value would you like to to delete?")
If Str = "" Then Exit Sub

For i = 1 To LastRow
If .Cells(i, Col) = Str Then .Cells(i, Col).ClearContents
Next i
End With
End Sub

The above code is case sensitive, if you want it to be not case sensitive
change the last If statement with the following:

If UCase(.Cells(i, Col)) = UCase(Str) Then .Cells(i, Col).ClearContents

Hope this helps
--
A. Ch. Eirinberg


"TGalin" wrote:

I would like to create a macro that will search Column A and every time it
finds a cell that says "User" or "Theodore Galin" the contents of that cell
will be deleted. Can you help me with this?