Free Microsoft Excel 2013 Quick Reference

Deleted Macros?

I was messing around in Excel and accidentally deleted all of my macros I think. I used:

Application.DisplayAlerts=False
ThisWorkbook.ChangeFileAccess xlReadOnly
Kill ThisWorkbook.FullName
ThisWorkbook.Close False

Is there anyway I can get them back?


Post your answer or comment

comments powered by Disqus
Is there any way to write code that will delete macros from an excel work book?

Hey all,

I browsed the first few pages of searching for "row delete macro" and couldn't find anything that suited my needs very much, so here goes:

What I need to do is automatically delete rows where the entries in column B (date) and column E (string) are the same. It is a very large group of data, so filtering is not exacly an option here.

Here is the twist on it...

I have a column F that contains either "Accepted-Active" or "Accepted-Closed". If I delete a bunch of rows that contain the same column B and E entries, I want to save the row that contains "Accepted-Closed", otherwise I don't particularly care which of the rows is saved.

Need any more info? Just post, I will be refreshing every minute or so.

Hello everyone,

I see that there are lots of options when it comes to row deleting macros, but am I able to use a wildcard, for example delete all rows where column A contains 0292*, or use greater but less than, and delete all rows where column A contains a code between 02920000 and 02930000?

Thanks, Kenny.

Is it possible to have a macro or vba code that will delete all other macros in a workbook. I have tried searching the threads and found one self deleting macro, I think this is what I need, but I dont really understand what is happening. What I really would like is a macro that can delete all other macros within a workbook, even if macro security is high!...any help would be greatly appreciated

thanks in advance bronny

HELP!!!!

Yes the infamous duplicate deletion macro problem!!!!!

I need a dup delete macro, but heres the kicker, i need it to search by 3 things, not just one column. I need it to delete by the columns A, D, and E. but heres the kicker again, The list cant be sorted. it needs to stay in its current order. What im doing is bringing a new report into my master list. I have worked the stuff on the list, and i need to delete anything at the bottom that might be a duplicate of the top

so i have as an example

a b c d e f g
1 a1 b1 c1 d1 e1 f1 g1
2 a2 b2 c2 d2 e2 f2 g2
3 a1 b3 c3 d1 e1 f3 g3
4 a3 b4 c5 d6 e7 f8 g9

So in the example the data in the colum 1 and 3 match in the specified columns A D and E, so i would need to delete the row 3. its ok that the data in b c f and g arent exact. just a, d, and e cant match. Any help would be MOST appreciated.

The following function was posted on this site to delete macros:

Const vbext_pk_Proc = 0

'----------------------------------------------------------------
Sub DeleteProcedure()
'----------------------------------------------------------------
Dim oCodeModule As Object
Dim iStart As Long
Dim cLines As Long

Set oCodeModule =
ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
With oCodeModule
On Error GoTo dp_err:
iStart = .ProcStartLine("myProc", vbext_pk_Proc)
cLines = .ProcCountLines("myProc", vbext_pk_Proc)
.DeleteLines iStart, cLines
On Error GoTo 0
Exit Sub
End With

dp_err:
If Err.Number = 35 Then
MsgBox "Procedure does not exist"
End If
End Sub

When I try it out, I get the following error:

Method 'VBProject' of object '_Workbook' failed.

Anyone have a clue about what I'm doing wrong?
Many Thanks!

--
Ray

I would like to delete EVERYTHING below a header row so I can copy new data in its place, lets say row 10 would be the header row. Is there a delete macro to do this?

All -

I have been learning about how to delete macros in vba. It works. But take a
look at this code (in "Module1"):

Sub delete_save_quit()
Dim vbCom As Object
Set vbCom = Application.VBE.ActiveVBProject.VBComponents
Application.DisplayAlerts = False
vbCom.Remove VBComponent:= _
vbCom.Item("Module1")
ActiveWorkbook.Save
Application.Quit
' Application.DisplayAlerts = True
End Sub

Problem looking for a solution: I want to remove the module, and also save
and quit automatically since I am running excel via the command line (.bat
file). As a note, what follows is true even when I rename the subroutine to
"Auto_Open", which is what I want since everything needs to be
automated...opening the workbook, (running some macros), deleting the entire
module, saving, quitting.

Here is the behavior of this subroutine: Since I do not want to be prompted
to save the workbook, I set DisplayAlerts=False. If I do this, the module is
not deleted, but the workbook is saved (modified date is updated). If I do
not DisableAlerts, I will be prompted to save, but the module WILL be deleted
upon saving. It doesn't seem to matter where I put the
DisplayAlerts=False...before the "Set" or after the "Remove" doesn't change
the behavior I am seeing either. Lastly, I thought that I may need to
DisplayAlerts=True for some reason, so I would also try adding the line at
the end of the above subroutine. All this does is prompt me to save (this is
after the code says 'save' and 'quit')! help please...sounds like there are
some great vba programmers here...

--

- chris

I'm a newbie at this so please be tolerant of the incorrect terminology
as I try to explain what I need. I have created a purchase order form
in Excel 97 using macros for automatic numbering, data verification,
printing, saving, etc. Not knowing how to incorporate a database for
data entry I created the worksheet three pages long which covers orders
from 1 to 60 items in size. On each page I imbedded graphics (logos)
and created a "Save" button and "Print" button which execute a data
verification macro before saving or printing. Each page also has a
"Help" button. The "Save" button automatically assigns a filename and
saves the file to our file server for permanent storage. The saved
files don't need the buttons or macros so I decided to delete any
unused pages, remove all buttons and delete all macros. The problem is
that it is taking a long time to save a file. I'm looking for better
ways to do the clean-up. Any suggestions would be appreciated.

Here is what I am doing...

For deleting buttons I use:
ActiveSheet.Shapes("Button 10").Select
Selection.Cut
I have 12 of these.

For deleting extra pages I check the first data cell of each page and
delete the page if that cell is blank as follows:
===code starts===
If Range(strP2DataCell1) = "" Then
If Range(strP2DataCell2) = "" Then
intCounter = strP2FirstRow
Do Until intCounter > strP3LastRow
Worksheets(1).Rows(strP2FirstRow).Delete
intCounter = intCounter + 1
Loop
' Delete embedded MS Word Objects (DECC logo on pages 2 and 3)
ActiveSheet.Shapes("Object 36").Select
Selection.Cut
ActiveSheet.Shapes("Object 43").Select
Selection.Cut
End If
'If the first two "Quantity" fields on Page 3 are blank then delete all
'rows that make up page 3
ElseIf Range(strP3DataCell1) = "" Then
If Range(strP3DataCell2) = "" Then
intCounter = strP3FirstRow
Do Until intCounter > strP3LastRow
Worksheets(1).Rows(strP3FirstRow).Delete
intCounter = intCounter + 1
Loop
' Delete embedded MS Word Objects (DECC logo on pages 3)
ActiveSheet.Shapes("Object 43").Select
Selection.Cut
End If
End If
===code ends===

For deleting macros I use code that probably came from help from this
group.<g> This is not my code:
===code begins===
If objDocument Is Nothing Then Exit Sub
i = 0
On Error Resume Next
i = objDocument.VBProject.VBComponents.Count
On Error GoTo 0
If i < 1 Then ' no VBComponents or protected VBProject
MsgBox "The VBProject in " & objDocument.Name & _
" is protected or has no components!", _
vbInformation, "Remove All Macros"
Exit Sub
End If
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
On Error Resume Next
.VBComponents.Remove .VBComponents(i)
' delete the component
On Error GoTo 0
Next i
End With
With objDocument.VBProject
For i = .VBComponents.Count To 1 Step -1
l = 1
On Error Resume Next
l = .VBComponents(i).CodeModule.CountOfLines
.VBComponents(i).CodeModule.DeleteLines 1, l
' clear lines
On Error GoTo 0
Next i
End With
===code ends===

Much thanks,

BrianG

How to delete (Macro) button in the attached file (tab #2)? Please list steps. Thanks

I had a macro running in a workbook which prevented deletion of the active sheet. I decided I didn't neet it and deleted the macro.

Now whenever (in ANY workbook, new or old) I rightclick on the sheet tab to delete a sheet, it tells me that it cannot find the macro I deleted. I've exited Excel and gone back in and created a brand new workbook and it does the same thing. The PERSONAL.XLSB doesn't have any macros in it.

Please help. It's driving me mad.

Hi,
My goal here is to automate the "OZGRID CONDITIONAL ROW DELETE" macro from: http://www.ozgrid.com/VBA/row-delete-criteria.htm, and apply it to all sheets in a workbook. My problem is in automating the process. There are 3 imput boxes, which I want to remove. I managed to eliminate 2 of them, by hard coding data. On the third one, which is "Step 1" I just can't figure it out. Here is the applicable part of the code:

	VB:
	
 Range 
Dim strCriteria As String 
Dim lCol As Long 
Dim rHeaderCol As Range 
Dim xlCalc As XlCalculation 
Const strTitle As String = "OZGRID CONDITIONAL ROW DELETE" 
On Error Resume Next 
Step1: 
 'We use Application.InputBox type 8 so user can select range
Set rRange = Application.InputBox(Prompt:="Select range including header range" _ 
, Title:=strTitle & " STEP 1 of 3", Default:=ActiveCell.CurrentRegion.Address, Type:=8) 
 
 'Cancelled or non valid rage
If rRange Is Nothing Then Exit Sub 
 'Awlays use GoTo when selecting range so doesn't matter which Worksheet
Application.Goto rRange.Rows(1), True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
What I tried to do was to hard code the "rRange", and eliminate

	VB:
	
"Application.InputBox(Prompt:="Select range including header range" _ 
, Title:=strTitle & " STEP 1 of 3", Default:=ActiveCell.CurrentRegion.Address, Type:=8)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The default for the imput box is: $A$1:$J$51, which is the correct range, only when I place that after "Set rRange = " I get "Compile error: Invalid character" with the "$" highlighted. I tried adding quotation marks around the range, and got "Compile error: Type mismatch". I also tried using A1:J51, and "A1:J51", but to no avail. I don't understand what else "Set rRange = " could be looking for.
So, my question is, how can I eliminate this Application.Inbox, and hardcode my range? I have attatched a sample workbook, with the vba sub on "This Workbook".
I really appreciate this great resource of Ozgrid, and all the great advise available.
Thank you Ozgrid and the many gurus!
Jmdigs

Hi,

I created a macro accidentally and I want to delete it, but it won't let me. When I open up developer-code-macros and click on the macro, the only button highlighted is "create." When I tried to open a new one and save it under the same name (in order to delete it), it wouldn't let me and it created a new macro.

Any suggestions? There are tons of macros so I need to be consistent with the names.

Hi,

I managed to save workbook as values only by the help of this forum thanx for the help

Now is it possible to disable the commond buttons, when the workbook is saved as values only or can we delete the macro's.

Many Thanks

Hi,

I recorded some macros on a workbook, and then realised that I didn;t need them, so deleted them. But now whenever I open the workbook, the "Enable/Disable macros" window pops up. And when I enter a formula into a cell and press enter, it tells me that the macros have been disabled. Is there a way to delete all trace of the macros? Or will I need to copy it all into a new workbook?

Thanks.
Adeline

Hi everyone-I need some help on a catalog I am putting together in Excel. Here's the problem.

I have a spreadsheet with products and their specifications. In the last column of this spreadsheet, there is either a part # or #N/A.

What I would like to do is create a macro that will completely delete every line that has an #N/A in the last column.

I cannot just sort the spreadsheet by the last column because it would change the order of the products.

I'm sure there is a really simple answer to this that I am completely overlooking. Any ideas?

Hi, I am trying to write a macro which deletes all modules from a workbook. My problem is that


	VB:
	
objDocument.VBProject.VBComponents.Count 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
is always returning a zero value. I did a bit of searching and found that it is probably down to workbook protection, however I have not personally implicated any such locking. Any help on how to 'unprotect' my workbook or any other diagnosis much appreciated. TIA

Hi,

will some body come to my rescue, as i need to save the workbook by using macro at different location and want the macro to delete all macros before saving the file.
thanx

after importing data (data >> get external data >> import text file) and running a macro that contains

	VB:
	
Cells(i, 5).EntireRow.Delete Shift:=xlShiftUp 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
. i cannot insert any data into the next column below. why is this so? is it because of the excel memory that remembers that a macro has been previously ran in that cell before?

example i import text file into B1 then the data will be imported into different cells using delimiters. the data will occupy from row 1 to 22. after i run the macro which combines fields with similar data the data will occupy row 1 to 15. then when i try to import data into B16. the "import text file" option will be grayed out. why is this so? how do i solve the problem?

Hello,

I'm a first time visitor to this group and hoping you can help me. I'm not very familiar with writing VBA code, so I'm in need of some assistance. I've reviewed the previous posts, but I'm not familiar enough with code to know what to change for the ones that look like they might work. Here's the problem: I have a workbook with multiple worksheets that makes up a production schedule. Three of the worksheets are identical in format, except for some of the information. All share a common column (column "O") that identifies if a job is complete - marked with a "Y". What I need to do is have a macro automatically run upon closing of the spreadsheet that will cut any row that has a "Y" in column O and paste it to the next non blank row on a different worksheet (completed job history). It also needs to delete the row from where it was cut from. The file is large, so I've avoided attaching it. Any help is greatly appreciated.

Is it possible to write a macro that will delete all macros from a worksheet when they are no longer needed? I'm trying to reduce overhead.

Does anyone out there have code that will delete only specific macros while leaving others.

Hello,

I have to remove a number of columns from a particular Excel report. I am running Excel v 5.0 on a Windows 2K network. I used the macro record feature, and here is the resulting code:

Sub ColumnCut()
Columns("O:P").Select
Selection.Delete Shift:=xlToLeft
End Sub

Seems pretty straightforward to me (and I do have a programming background, just not VB.) However, when I run this macro, it deletes the
entire sheet (i.e. all data in all columns A:N disappear.)

I can't believe I can't make something so simple work; I must be missing something very obvious.

Any help would be appreciated. Scorn is optional!

Thanks.

I have macros that mysteriously started showing up in my files and are causing my files to be a minimum of 4MB each. If you go into the Macro tools you can see the names but you can not jump into them, delete them, or edit them. They seem to spread like a virus (many scans reveal no viruses), but if I have a file open with these macros, and open another file - and save it at any time... the macros save in there as well.

Any ideas how to clean these out?

Thanks!

I have a large sheet of data I want to sort and delete rows based on criteria. As an example:

There is 15 columns of data. I want to match column D, E, F, and G. With every match, I want the macro to keep only those rows that contain the first and last date of column K. All other rows should be deleted, leaving only 2 rows for each match of D, E, F, and G. I cannot have it filtered by the specific value of D(i.e. project#) as these numbers may change every week. The same goes for the rest of the columns.

A B C D E F G H I J K L M N O P Q R S
1 1 2 3 4 5 6 7 4
2 1 2 3 4 5 6 7 5
3 1 2 3 4 5 6 7 7
4 1 2 3 4 5 6 7 9
5 1 2 3 4 5 6 8 1

Once macro has run, only row 1 and row 4 should remain. This would go on for thousands of rows.

I am a beginner with VBA and am having no luck trying to write it or find a similar code to modify. Any help anyone can give me would be great. Thanks a lot.


No luck finding an answer? You could always try Google.