Does anyone have SIMPLE code for this that can be run in a normal Sub. My worksheet will always be named "Cleaned". I have seen some posts regarding Functions and other things that seem more complicated than necessary.
View 9 RepliesHow do I delete a worksheet but ONLY if it exists, if it doesn't exist, ignore the command. I'm afraid to mess up the other code in the macro/sub, so here it is...
View 2 Replies View RelatedI have working data on list sheet and added data on compiled sheet.
I need to match duplicates and delete these rows from compiled sheet.
I've tried coping to the list sheet and use "Remove Duplicates", but it rids me of the data that has been processed and has fills applied.
I'm looking for a macor that can do this work for me without losing data on list sheet.
This data reaches into the thousands and I'm looking to run this off the CASE column.
I have a list of numbers and I have corresponding text files which I named by using these numbers as reference
is there a possible macro to delete those rows which have corresponding text files by checking in a specific folder if that file name exists
Before closing a workbook I want to check if a sheet called temp exists. If it does then I want to delete it.
View 9 Replies View Relatedi would like to write into a macro the ability to check and see if a sheet named "Pie Chart" exists, and if it does delete it without being prompted whether or not i want to delete it.
View 5 Replies View RelatedI have some code that works almost perfectly. I am trying to automatically delete rows in which no data exists in cells in a certain column.
the problem that I am having is that is seems to go into an infinite loop.
For example, if I delete row 5 and then iterate to the next row, row 5 becomes what used to be row 6 and now my code is on row 6...essentially skipping a row.
Here is an example data set (note that the set is for 20 rows):
1
3
6
15
36
37
here is the
Sub Macro2()
r = 20
Min = 1
Do While r >= Min
If Cells(r, 1) = "" Then
Cells(r, 1).EntireRow.Delete
Else
r = r - 1
End If
Loop
End Sub
I can delete Sheet5 using this macro. I would like to put this in the workbook so that when I close the workbook it will be activated if Sheet5 exists. If Sheet5 does not exist nothing will happen.
Sub DeleteSheet5 ()
Application.DisplayAlerts = False
Sheets("Sheet5").Delete
Application.DisplayAlerts = True
End Sub
how to mark the post as solved other than go advanced and selecting solved from the drop down menu can you tell me how?
I'm trying to check if a named range "ActiveCells" exists before deleting it in VBA but can't seem to get the syntax right, I have tried: If Range("Activecells") Is Nothing Then Resume Next Else: ActiveWorkbook.Names("ActiveCells").Delete
AND
If ActiveWorkbook.Names("Activecells") Is Nothing Then Resume Next Else: ActiveWorkbook.Names("ActiveCells").Delete
Can anyone point me in the right direction? I have tried searching but I can only find threads about checking for named objects or about using the toolbars to add/delete named ranges.
I have the following code, but it takes longer than expected to run. Is there anyway to speed this up? I am not sure if autofilter is a option. I just want to search through range A16:Z16 and if the word "FALSE" exists delete the entire column. The word "TRUE" is the only other word that would exist in range A16:Z16
[Code]....
I try to do the following I get an error 'Subscript out of range'
View 3 Replies View Relatedi would like to know whether a worksheet exists or not... say, the worksheet name is sheet1, i need to check whether the worksheet exists... the point is, i need to delete the worksheet if it exists and execute a code... if the worksheet doesn't exist also, i need to execute the code... so im getting a little confused with this... i need something like this...
if sheet1_exists then
delete the sheet
end if
my_code.......
I have a spreadsheet with columns set up like this: RowName, Date, Value1, Value2, Value3 etc. The date is in format YYYYMMDD. What I am trying to do follows, in some sort of pseduo fashion.
View 2 Replies View RelatedI have an array that opens a workbook containing close to 100 worksheets, and copies specific sheet names into there own individual workbooks. The problem I have now, is that I assume the worksheet exists in the workbook, but often times it does not Is there a way to add some sort of "catch" that will 1st verify the worksheet exists instead of my code crashing?
View 9 Replies View RelatedI have a macro that 'opens all' workbooks in the specified folder. I've copied it over, but need to only open all the wbk's if a specified worksheet exists w/in the wbk..I know there is a dim ws statement that can be used, but how do i use it w/ the current macro?
Option Explicit
Sub recTestOpenAll()
Dim x As Integer
Dim WB As String
Dim wbk As Workbook
For x = 1 To 100
WB = "G:Rule Test FilesREC " & x & ".xls"
On Error Resume Next
Set wbk = Workbooks.Open(Filename:=WB)
On Error Goto 0
If Not wbk Is Nothing Then
End If
Next
End Sub
I am writing a macro which first needs to look and see if a certain worksheet exists. If it doesn't then I want it to create it but if it does I first want it to delete the sheet then create a blank one with the same name. At trhe moment I have this but it is scrappy and doesn't work very well:
On Error Goto AddSheet
If Sheets("MONEYSHEET").Activate Then Goto Data
AddSheet:
Sheets.Add before:=Sheets("LOOKUPSHEET")
ActiveSheet.Name = "MONEYSHEET"
Data:
It also does not delete the sheet if it already exists, just edits the existing one.
I'm looking to use the value from a series of dropdowns (made via data validation lists).
Cell AM5 is a dropdown of named ranges made form a seperate sheet
Cell AO5 is a dropdown that uses '=INDIRECT(AM5) to lookup the values in the named range.
I need cell AO5's value to be used to filter rows in the current worksheet. The current problem is when i use the first dropdown in AM5 it still displays the last value, untill i use the dropdown to select a new one. This value typically will not be found and i do not want my code to execute in these cases.
how do I automatically add beside the name the colum title where it previously existed. see the attached.
View 2 Replies View RelatedI'm creating a macro to select, modify dimensions and place shapes on a excel sheet. (I'm talking about pictures insered and stocked in a specific sheet of my workbook)
When the users insert a new picture he has to set a name for each of them.
A combobox contain the choices, when an item is selected, the macro identify, size and place the corresponding picture.
But I have a bug if a shape doesn't exist when I try to select it :
Sheets("fiche de controle").Select
'select the sheet with the pictures
ActiveSheet.Shapes(Item_old).Select
'select the shapes "Item_Old"
Item_Old is a variable corresponding to the picture name.
If the user made a typo, I have a bug.
Someone knows how to test if the shapes exist to display a meesage if not ?
Or somethig to avoid this kind of bug ?
I have a spreadsheet which contains 30,000 lines Column A contains an email address. This spreadsheet was merged from several and so I know that there are duplicates in it.
How can I look up column A and if an identical value (email address) is found further down the sheet on another row. Delete that duplicate row?
I'm not concerned that the data may not be identical in other columns. If the email is a duplicate delete the next and all other rows that contain that email address.
I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.
I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".
I am maintaining an issues list in excel. File contains two worksheets/tabs: 1) Open Issues & 2)Closed Issues
One of the columns is Status. When an issues is in 'Complete' status, it is removed from Open Issues and added onto the 'Closed Issues' worksheet.
There are many issues closed daily and its quite a manual task to individually delete rows in one sheet and paste them at the end of the last row in the other sheet. Want to have a button/macro that would find any rows that have the cell for the status column w/ value 'Complete', delete it from 'Open Issues' sheet, and append it to the 'Closed Issues' shseet.
ive done basic c++ programing years ago, but know nothing about vba or excel macros.
I have a workbook with 3 worksheets. I am trying to delete 2 of the three but "Delete Sheet" is grayed out in both the "Edit" menu and RtMouseClick menu. The sheets are not protected and the workbook is shared. Do I have to un-share the workbook in order to delete sheete? If so.
View 9 Replies View RelatedNeed to delete the line on this worksheet and post how they did it.
View 2 Replies View RelatedI'm trying to delete the buttons on the top of this worksheet, and for some reason am not able to do so.
View 3 Replies View RelatedI was trying to print a workbook which was about perhaps 20-30 sheets of roughly one page of printed info each. However, I saw the print job and it said it was printing over 7,000 pages!!!
It turns out there were about 4-5 worksheets which were blank (and also didn't have gridlines even though gridlines were checked on in options).
When I switched the sheets to page break view, lo and behold each sheet contained a few thousands "pages". I simply can't seem to reduce it down to a relatively normal size.
I tried going right past the edges of page 1 and deleting all columns/rows to the right and bottom of it to no avail. I selected the whole worksheet and removed all borders and any cell content with no luck.
I saw other "solutions" on the internet about copy/pasting the other unaffected sheets to a new workbook and deleting, but this seems cumbersome.
I am tring to make a macro that will find all charts in a worksheet and delete them.
the macro is essentilally a monthly refresh that cleans out a template of charts that were pput in the previous month. As the chart names and the number of charts might vary from month to month I just want simple code to select all charts in the worksheet and delete them.
I am absolutely new to visual basic thing but have a good idea of what I am doing in excel. I have seen some code to delete pics but can't figure out what it is that I am suppose to do. What I am doing is coping and pasting something from the internet. I do not need any of the pics that are pasted into the worksheet and am tired of deleting them individualy.
View 5 Replies View RelatedAn old post provided the following macros that purportedly prevent users from deleting a worksheet. Unfortunately, it also makes it so you can't delete ANY worksheet in ANY file, which is of course not what was intended. Need figure a way to remove this nuisance? All attempts to delete a sheet keep looking back to the offending workbook, even though the macro has since been deleted from it.
Try pasting the following two event procedures into the Help sheet module:
'==========================>>
Private Sub Worksheet_Activate()
Dim CB As CommandBar
Dim Ctrl As CommandBarControl
For Each CB In Application.CommandBars
Set Ctrl = CB.FindControl(ID:=847, recursive:=True)
If Not Ctrl Is Nothing Then
Ctrl.OnAction = "RefuseToDelete"
Ctrl.State = msoButtonUp
End If
Next
End Sub................
Can someone help me out with this script?.
I keep getting "Subscript Out of Range Error"
I'm trying to delete all the code on sheets(1) of the active workbook.