Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Encrypt the contents of a specific group of cells | Excel Programming | |||
Is there a formula to clear the contents of a specific cell? | Excel Worksheet Functions | |||
how to delete contents of cells having specific data | Excel Discussion (Misc queries) | |||
hide rows with specific contents | Excel Programming | |||
Delete specific cells contents in a row with some locked cells in the same row | Excel Programming |