I have inherited a few workbooks for some monthly reports, and there appear to be several macros in the book that are not used anymore. I noticed them when I tried to run one of my own macros using Tools-->Macros--> Run Macros. While they are listed in the run macros list, I can not select "Delete" and there are no modules in the VBA editor besides those I inserted myself. If I try to run one of them I get the error "Reference is not valid."
View 9 RepliesI'm making excel macros for the first time and getting things figured
out, but I have a few questions:
What code do I use to delete columns that have all empty cells EXCEPT
the top cell (row 1) that contains any of a number of headings? I need
to insert this into an existing macro.
What code do I use to fill color every other row (preferably even numbered)
light grey after selecting a "currentregion" ? Again, to put inside an
existing macro.
A related question: When I find code help on the web, it usually
starts with "sub" and ends with "end sub" so how do I insert that into
an existing macro? It looks like a "sub within a sub" doesn't work.
I have used the following Macro that deletes the Macro code. It then saves the file and quits excel.
*************************************
Sub removeAllCode()
Dim awi
Dim awcl As Integer
Dim count As Integer
Dim i As Integer 'loop counter
On Error Resume Next
count = ActiveWorkbook.VBProject.VBComponents.count
For i = 1 To count
Set awi = ActiveWorkbook.VBProject.VBComponents.Item(i)
awcl = awi.CodeModule.CountOfLines
awi.CodeModule.DeleteLines 1, awcl
Next i
Set awi = Nothing
ThisWorkbook.Save
Application.Quit
End Sub
*************************************
However when I open the Excel File, I still get the Macro Prompt. I have checked in Macro list as well as in VB Editor and no Macro code exists
If I have the file manually (or do a save as) and then reopen the file, I no longer get the Macro prompt.
I have tried various examples listed in different forums/sites but nothing works.
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies View RelatedI have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
What is the commands or script for deleting a macro automatically using
another workbook macro.
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
View 9 Replies View RelatedI have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.
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.
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
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.
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 RelatedI 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 RelatedI 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 RelatedI 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 RelatedI 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.
The loop cycles through my sheet correctly but wont delete any cells.
View 10 Replies View RelatedChecks 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"
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)
Deleting Columns. I've got 2 codes here,
This:
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 Relatedi 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.
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).
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.
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.
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] .........
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
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.
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