Deleting A Row Using VBA
Dec 28, 2009
I am in need of code that will match a variable, "TheName", to the values in a range named "Phases". If a match is made then I want to delete the entire row which the value is matched in.
example:
PHASES
cat
dog
pig
My variable, "TheName" = Cat
I want the code search only the range "Phases" and delete the entire row in which the variable "TheName" is found. so in this case it would delete the entire row in which the value of "cat" was matched.
View 9 Replies
ADVERTISEMENT
Jun 23, 2006
I am trying to find certain words in a column and delete the word and characters following. For example, Say I have a column of info as seen below. This is a test of me. I am just experimenting with this stuff. Deleted (6/15/01) Let me know what you think. I am not sure about it all, but I guess I will figure it out. riviledge1 (01/05/06) Now let's see what happens when I try to test it.
I want to find all the "Priviledge1 (01/05/06)" and replace with nothing. Please note, the date will change with each record, so I need to figure out how to tell Excel to find "Priviledge1", delete it and the date behind it. So I want to delete "Priviledge1" and the next 11 characters including the space.
View 3 Replies
View Related
Apr 6, 2008
i have created a user from called "delete"
from this the plan is that you will be able to select the name of the tab that you wish to delete from a drop down list (which will contain all the tab names) and click on a command button and this will then delete it
View 9 Replies
View Related
Mar 31, 2007
In Excel 2007, when I delete a row/cell, text is associated with deleting row/cell but some lines, polygon ... can not associate with deleting row/cell . So the lines, polygon ... in mistake location.
View 12 Replies
View Related
Jan 8, 2009
I have data in a column that has numbers letters and characters in, all that I require is the first 9 numbers (these are always the first 9 characters), any idea how this can be achieved.
View 5 Replies
View Related
May 7, 2009
I m writing a formula that will highlight duplicates. I want to use the supplier code (column D) as the search criteria. can the formula identify these duplicates by entering the word 'duplicate' in column L.
View 3 Replies
View Related
Nov 10, 2009
I have a long list of names with their locations in brackets. What formula should i use to delete the brackets and their contents only?
View 3 Replies
View Related
Feb 22, 2007
I would like to run a macro that will delete the selected row in a protected worksheet but only delete it if the row does not have locked cells. I want users to be able to delete the rows they no longer need but only be able to delete them if the row does not have formulas that are locked.
BTW-I am working in an older version of Excel.
View 12 Replies
View Related
Aug 15, 2007
The loop cycles through my sheet correctly but wont delete any cells.
View 10 Replies
View Related
Jul 9, 2008
Checks if there is a sheet name and if there is a sheet of that name then it is deleted.
the sheet name is a number entered through an inputbox and "Average"
View 10 Replies
View Related
Nov 7, 2008
Just finished amending a piece of code to enable me to search for an agents name in a list of files. This is then copied to a new worksheet (named by the name entered in the search). What I need is to cut(or delete) the row from the original sheet (sheet 1 in this case) thus leaving me the remaining list of files.
Below is how the code is setup at the moment....(just copies the row)
View 12 Replies
View Related
Dec 26, 2008
Deleting Columns. I've got 2 codes here,
This:
View 3 Replies
View Related
Jan 2, 2009
I have a column that has all the digits in the phone number. What I would like to do is remove the first 8 characters example 111-222-3333. I want to remove all the ones dash twos and dash. Can someone help me please ?
View 7 Replies
View Related
Mar 30, 2009
i need is a script that will add two boxes together to get a sum in a third box, but where it is tricky for me is that i need the third box to retain the summed value even when either of the first two box values are deleted.
For example, say i have box A1=10, B1=40, and C1= SUM of A1 & B1 (so C1=50). Easy enough, but where im lost is that i want to be able to delete the value of either A1 or B1 and then have C1 still = 50, so then i can input another value into either A1 or B1 to continue to add to C1.
View 6 Replies
View Related
Sep 16, 2009
In column G of my excel 2003 worksheet named: Data, I have a list of people's first names. These names were extracted from an external database.
Some of the first names actually contain peoples second names or partner's names.
Example:
John & Jill
James and Sue
Kerrie-Anne
Joanna Lyn
I need a macro to please delete people's second name.
So in the example above I need to keep all names that are joined by: & or and or - (such as John & Jill, James and Sue, Kerrie-Anne).
Also, in the example above I need to delete all names that are joined by a <space> (such as Joanna Lyn).
View 11 Replies
View Related
Sep 21, 2009
I have an excel spreadsheet that contains about 1000 rows and about 25 columns. The file contains employee information, name, id, cost center, department, title, FTE...etc. Column E contains the cost center which is a 7 digit number (i.e. 8001234). Within the 1000 rows of data there somewhere to 70 cost centers. I would like to delete all rows where a cost center does not match a list of 13 cost centers. I'm thinking I need some kind of array where I type in the 13 cost centers in the code, but I'm unsure of the syntax within VBA.
Also, the second step of the code I'm looking for is to delete all columns except for Column B, E, J and L. Those, by the way are Name, Cost Center, Job Title and FTE.
View 2 Replies
View Related
Oct 8, 2008
I am using this code to populate column J with a date when yes is selected in colum I.
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column = Columns("I").Column Then
If .Value = "Yes" And IsEmpty(.Offset(, 1)) Then .Offset(, 1) = Date
End If
End With
End Sub
However i also want any value entered in J to be deleted when No is selected.
View 2 Replies
View Related
Oct 26, 2011
I want to delete all rows in the column of the ActiveCell when the ActiveCell.value < 0.01. Could you tell me why the code below doesn't work? It deletes the right rows but keeps looping without stopping
Sub DeleteZeros()
Dim Col As Long
Dim StopRow As Long
Col = ActiveCell.Column
StopRow = Cells(Rows.Count, Col).End(xlUp).Row
Range("A1").Select
[Code] .........
View 6 Replies
View Related
May 15, 2012
Im trying to write a macro to delete the last 5 characters of a cell,
Its the whole column to which i need to apply this to
View 8 Replies
View Related
Sep 18, 2012
I have some shapes on a worksheet that need deleting on change of a combo box. Unfortunately I'm unable to use the below code as not all the shapes need deleting, just the ones that are named in a table on another worksheet.
For Each s In ActiveSheet.Shapes
s.Delete
Next s
Also not all the shapes named in the table will be on the worksheet as this depends on what is selected from the combo box.
View 2 Replies
View Related
Apr 25, 2007
I have a macro that deletes rows. For some reason, the macro bombs out when the selection seems to be too big. Why is that?
deleterow_min = Sheets("Sheet1").Cells(1, 6)
deleterow_max = Sheets("Sheet1").Cells(1, 7)
Rows(deleterow_min & ":" & deleterow_max).Select
Selection.Delete Shift:=xlUp
View 9 Replies
View Related
May 5, 2007
Public Sub Delete_Blank_PO()
Dim iLastRow As Long
Dim Rng As Range
Dim r As Long
Dim x&
Sheets("ZF17.4").Activate
For x = Cells(Rows.Count, 2).End(xlUp).Row To 6 Step -1 '***set coumn 6 as range
With Cells(x, 6)
Select Case "" '.Value
View 9 Replies
View Related
Dec 24, 2007
I have a spreadsheet with thounsands of rows. Some of the rows I'd like to delete because thier values are below 5%. I wrote a VBA code, which primary function is to look for a cell and delete the row if the value is less than or equal to 5%. The code looks fine but I get an "Infinite Loop" anytime the first row value is not true. Below is the code,
-jungleman1
Sub Delete_Zero_Factors()
Dim i As Integer
Dim CUSIP As String
Sheets("Report").Select
Range("B5").Select
i = 0
While Not (IsEmpty(ActiveCell.Offset(i, 0).Value))
CUSIP = ActiveCell.Offset(i, 0).Value
Do While (ActiveCell.Offset(i, 1).Value
View 9 Replies
View Related
Feb 29, 2008
I have a list of data (this list is variable) and I want to delete the rows that have the word Revaluation in column L.
I tried the following but it did not work
Sub Delete()
Dim Lastrow As Long, i As Long
Lastrow = Cells(Rows.Count, "L").End(xlUp).Row
For i = Lastrow To 1 Step -1
If Range("L" & i).Value = Revaluation Then Rows(i).EntireRow.Delete
Next i
End Sub
View 9 Replies
View Related
Apr 1, 2008
I'd like a formula or format of some kind of basically any process which can delete a row if certain criteria are met.
If C2, D2, E2, etc up to I2 all contain the figure 0, I want the whole row to be removed. Can this be done in a formula?
View 9 Replies
View Related
Aug 6, 2008
I've got some code in workbook A that reformats another workbook B. Basically, it moves all the sheets across to A, creates a pivot table and chart, then saves itself as a variant of the original filename B. The last things it does is to delete the one sheet that was originally in A, and then delete the module that the code is running from (using some code from Chip Pearson):
ActiveWorkbook.VBProject.VBComponents.Remove ActiveWorkbook.VBProject.VBComponents("Module1")
The fact that a module can delete itself blows my mind - but there are a couple of things I'm not sure about:
1. Presumably anything that's in my sub after the delete module line won't get executed?
2. If the saved-as workbook (Bvariant) is closed without saving, the module is still there when it is reopened. Is there a way to prevent this without putting new code into Bvariant?
View 9 Replies
View Related
Sep 7, 2008
I have a large spreadsheet of over 5,000 rows and 20 columns. What I would like to be able to do is find a formula(?) that will enable me to retain rows of data when Cells in column B and column E contain duplicate data but delete entire rows if the contents of cells in column B and E are not duplicated.
For example:
Column B contains Part Numbers
Column E contains Manufacturer
If a part number (Cell B)has been made by two or more manufacturers (Cell E), retain all relevant rows.
If a part number has only been made by one manufacturer, however many times, delete all relevant rows.
What I'm trying to be able to achieve is retain data so that I can analyse it when 2 or more manufacturers have produced the same parts.
View 9 Replies
View Related
May 4, 2009
This thread is somewhat of a cross post, the cross post is here
[url]
The cross post relates to a MS-Word related question.
However, I do have the same question, just for MS-Excel.
Right now my Macro runs when my Excel application opens.
But when my users are Xing(closing) out of the Excel application is there a way to remove all VBA upon closure of the Excel sheet.
View 9 Replies
View Related
Aug 3, 2009
Private Sub UserForm_Initialize()
With AddDelete
ListBox1.RowSource = "Phone"
End With
End Sub
(Userform is called AddDelete)
I am trying to remove an item from the list box, by way of 'delete' button (command button) and cannot seem to remove from the list box and the spreadsheet
The list is spread over two columns and I can't seem to find a way of doing it, surely there is something simple I'm missing here..?
View 9 Replies
View Related
Aug 5, 2009
In a part of my code I have something that will delete all hidden rows, like:
For Z = 1 To TotalRows - 1
If Cells(TotalRows - Z, 1).EntireRow.Hidden Then
Cells(TotalRows - Z, 1).EntireRow.Delete
End If
Next Z
I've done it in reverse to be quicker, but it's still quite slow. Granted, I am deleting several thousand rows, but surely there's a quicker way? Thinking about it logically I would presume that (in general) selecting stuff first in Excel and then deleting them appears to be quicker, but I'm not sure how to pull that off.
View 9 Replies
View Related
