Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VB to clear any filters when closing a spreadsheet

Hi

I want to enter some vb to a Workbook_BeforeClose option that automatically
clears any filters in a specific sheet as the workbook is closed. Example of
recorded code is below which works but I have the sheet protected so I do not
believe below code is optimal for a protected sheet. Is there simple code to
simply clear any filters?

Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter
--
Kevin


Post your answer or comment

comments powered by Disqus
When closing a file via macro, I continue to get the message do I want to
save this file. Can someone help me bypass that message.

I don't know a whole lot about Visual Basic and found code for a macro to do spell check when a worksheet is protected.
Sub Spell_Check()
ActiveSheet.Unprotect Password:="purchasing"
Cells.CheckSpelling SpellLang:=1033
ActiveSheet.Protect Password:="purchasing", DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub
I would like to have this macro automatically run when the user closes the workbook. Any suggestions?

Thanks,

I am trying to write code to clear any filters in pivot tables if they exist and running into a brick wall. I had been using the following code but its not working Any suggestions?

Sheet1.Select
If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If
Thanks for your help.

I have an excel workbook set up with a marco that removes the save prompt
when closing. I want to add a message box that prompts to print or email when
closing. Is this possible?
The workbook is order forms for customers and is never saved, so they don’t
have to remove old info every time they need to order

I need to know how to write the code for a command button to clear all
filters for a spreadsheet that contains several columns of lists. I want to
click the button and it clear all fitered list back to "all"

Is there a way to close a spreadsheet via a macro without it prompting to be
saved?

I have a macro that pulls in a text file, formats it and then saves the file
as a delimited text file. When I go to close the file it asks if I want to
save the changes, even though I have not made any since the macro ran and
saved it.

How do I use a command button to close a spreadsheet and exit Excel?

Why do I get 'run time Error 9: subscript out of range' when closing a workbook? The workbooks opens successfully but does not close.

Workbooks.Open "C:test1.XLSX" 'works fine

Workbooks("C:test1.XLSX").Close SaveChanges:=False ' gives error message

I am still having issues getting a working drop down to change pivot filters! I have a spreadsheet with a drop down that should set the filter in pivot table 3 to be the value of cell V6 I get Runtime Error 5.

ActiveSheet.PivotTables("PivotTable3").PivotFields("IP1").CurrentPage = _
        ActiveSheet.Range("V6").Value
Any help appreciated!!

Is there a macro to automatically save and close a spreadsheet after a designated time?
I am trying to eliminate the issue we have with a particular network file that is sometimes locked due to some user having it open every time.

Hi:

I need to separate names and addresses in a spreadsheet which I want to use
as my source document for a merge letter. Both the names and addresses have
commas that separate the various fields and I understand I can use these
commas to separate the fields into columns. How do I do so?

Thanks
--
Bajan Boy

Hello,

I've got a routine that copies information to another worksheet, then saves and closes that worksheet. The save goes well, but the close causes the error "Excel cannot complete this task due to available resources, choose less data...."

I've searched for the possible cause of this and have not enable any type of sharing on my workbook. The size of the file I'm trying to save and close is aroun 7Mb, and increases the more data that goes into it.

The other problem I have is when I turn on the automatic calculation after running the code, it takes ages calculating the cells again... I've attached my total code below

I'd be gratefull for any other suggestions that you see

Regards

Gareth


	VB:
	
 DBASE_PREP_COPY_TO_DATABASE_FILE() 
     
     ' DBASE_PREP_FORMULAS_COPY_DOWN Macro
     ' Macro recorded 6/01/2005 by Gareth Robins
     ' FUTURE UPDATES MUST COPY OVER LABEL HEADERS IF MORE FIELDS ARE ADDED
     
    Range("P2").Select 
    If Selection.Value = 0 Then 
         
        If MsgBox("Are you sure, any errors are awkward to correct?", vbYesNo + vbQuestion) = vbNo _ 
        Then Exit Sub 
         
        With Application 
            .ScreenUpdating = False 
            .Calculation = xlCalculationManual 
        End With 
         
         
         ' This copies down the second block of formulas down to row 2562.
        Windows("FRONT END.xls").Activate 
        Sheets("dBase prep").Select 
        Range("B11:X18").Select 
        Selection.AutoFill Destination:=Range("B11:X2562"), Type:=xlFillDefault 
         '  Range("B11:X2562").Select
         '  Range("A1").Select
         ' NOW WE COPY THE CREATED DATA TO THE DATABASE
         
         '  OPENS DATABASE BY WAY OF CLICKING HYPERLINK
         '  Windows("FRONT END.xls").Activate
         '  Sheets("dBase prep").Select
        Range("AC28").Select 
        Selection.Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True 
         
         ' PREPARE SPACE IN THE DATABASE FILE
        Windows("DATABASE.XLS").Activate 
        Range("A62972:J65536").Select ' deletes space at the bottom of the dbase
        Selection.ClearContents 
        Range("A2:J2561").Select 
        Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow 
        Range("A1").Select 
        Workbooks("front end.xls").Sheets("dbase prep").Range("o3:x2562").Copy 
        With Workbooks("database.xls").Sheets("database").Range("A2:J2561") 
            .PasteSpecial xlValues 
        End With 
        Application.CutCopyMode = False 
         
         
         ' THIS PROCEDURE DELETES ALL THE "ND" DATA AND REMOVES BLANK ROWS.  THIS REDUCES THE DATABASE SIZE BY APPROX 70%
THUS ADHERING TO THE 65K ROW LIMIT
         'Windows("DATABASE.XLS").Activate
        Columns("A:J").Select 
        Selection.Replace What:="ND", Replacement:="", LookAt:=xlWhole, _ 
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ 
        ReplaceFormat:=False 
         ' THIS SORTS THE DATABASE BY DATE TO ENSURE THERE ARE NO BLANK ROWS
         'Columns("A:J").Select
        Selection.Sort Key1:=Range("c2"), Order1:=xlAscending, Header:=True, _ 
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
        DataOption1:=xlSortNormal 
        Range("A2").Select 
         
         'Windows("database.xls").Activate
         ' ActiveWorkbook.Save
         ' ActiveWindow.Close
         
         ' DELETE UNECESSARY INFO FROM DBASE PREP to keep saved file size down
        Windows("FRONT END.xls").Activate 
        Sheets("dBase prep").Select 
        Range("B19:X2562").Select 
        Selection.ClearContents 
        Range("B10").Select 
         ' RETURN TO INPUT SCREEN TO CLEAR INPUT ARRAYS & SELECT 4CAST TYPE
        Sheets("Input Screen").Select 
        Range("D104:AA143").Select 
        Selection.ClearContents 
        Range("AD104:DE143").Select 
        Selection.ClearContents 
        Range("DJ104:DQ143").Select 
        Selection.ClearContents 
         
         
         ' HIDE THE INPUT ARRAY TO FORCE USER TO SELECT 4CAST TYPE AND HIT DISPLAY
         
         
        With ActiveSheet 
            .Unprotect Password:="Secret" ', UserInterfaceOnly:=True
            .PivotTables("PivotTable1").PivotCache.Refresh 
             
            .Protect Password:="Secret", UserInterfaceOnly:=True 
            .EnablePivotTable = True 
        End With 
        Rows("97:148").Select 
        Selection.EntireRow.Hidden = True 
        Range("e150").Select 
         
        Windows("database.xls").Activate 
        ActiveWorkbook.Save 
        ActiveWindow.Close 
         
         
        With Application 
            .ScreenUpdating = True 
            .Calculation = xlCalculationAutomatic 
            .CommandBars("web").Enabled = False 
        End With 
         
         
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi All,

I am using the following code to highlight the row of the selected cell. Only problem is if I close the WB on B3 then row 3 stays highlighted upon close. When I reopen it is still highlighted and unless I select a cell in row 3 it stays highlighted. I tried adding some code to clear all rows on WB open and I also tried adding a line that clears the sheet everytime there is a selection change but both options are slow. Anyone have any ideas to clear the cells on close maybe? I tried a couple of things but could not get it to work.
Code:
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Static rr
 
    If rr  "" Then
        With Rows(rr).Interior
            .ColorIndex = xlNone
        End With
    End If
    r = Selection.Row
    rr = r
    With Rows(r).Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
End Sub
Thanks...

hello,

i have a workbook which shows data in rows, across columns 'a' thru 'k'
in column 'L' of every row i would like to add control button. clicking the button clears the contents of cells 'a' thru 'k'
i don't want to clear the entire row - just a - k
clicking on the control button in one row will delete the contents of that row, columns a- j

ie click control at L5 - clears a5 - k5 etc

I have tried the following...
1.

Range("A4:I4").Select
Range("I4").Activate
Selection.ClearContents
End Sub
when copied to controls in other rows, this just clears contents of row 1.

2. I guess I need to do something similar but with activecell..tried this without success...

ActiveCell.Offset(0, -9).Range("a4:j4").Select
Selection.ClearContents

however i get a run-time error 1004.
Any help would be greatly appreciated!
thanks

I have a sheet which is used by many people. I am finding that people are using the filters but not turning off the firlters when the save and close. This is causing issues for other users as they then need to try to figure out what filters are on. Is there a macro which will trun off all filters when you close the sheet/workbook?

Thank you

Stephen

Big spreadsheet (by my standards). 6 columns, but 64,000+ rows. User has the option to open it "Read Only". No functions or macros except for a MAX for one of the columns and data filter is on. Data is entered manually. When I try to open it at home, I get the progress bar at the bottom, but it will stop one or two bars short of 100%, and then Excel locks up. When I started working with it again a few days ago, there were 3 copies of the "official" tab so that I could have a safe playground to try stuff. ~15 megs. I deleted two of the tabs, so the size is down to 8 megs. I opened the 15 meg version with no problems. I worked for several hours, entering info, saving periodically, no problems. All of this on my home computer. The next day I started to have the lock up every time I tried to open the file. Neither closing Excel and trying again nor rebooting and trying again are successful. I have an 8 yr old Gateway, 1.8 gig Pentium 4, plenty of hard drive available. I am running 2000, half a gig of RAM. I've opened this spreadsheet many times before at home, no problem. When I take the spreadsheet to work I have no trouble opening it. I'm running 2003 at work, but sorry, I don't have any machine specs. Does anyone have a clue as to why this spreadsheet is now causing Excel to lock up on my home computer? Other spreadsheets open with no problem. Thanks.

EDIT- I now know the "why", but not the "why of the why". Saved w/o filter on, or filter on but not filtering, no problem to open. If I save it with the filter on so that I'm only seeing a very small percentage of my rows, I can save it, but I can't open it later. There is no significant difference in file size among all of those conditions. So DonkeyOte is probably right in pointing out the filter. I'll just have to remember to not be filtering when I save it in order to bring it home to work on. RAM *might* be a part as well. I added a gig so it's now 1.5 gigs at home, but 2 gigs at work where I have no problem opening the spreadsheet.

Hi all,

Part A
I don't think this one is too hard, but I've never had to do anything like this...

I have a collection of workbooks each with about 50 sheets/workbook.

I need to clear the contents of cell B15 in every single sheet of each workbook.

Is there any way I can do this quickly? I have hundreds of worksheets where this needs to be done.

Part B
In the same set of workbooks, I have a hidden worksheet that is the source of a Data Validation drop down list for B15. I need to clear any data from the cells in column A and replace it with a single value.

Any ideas on this one?

Thanks in advance for helping out a newb.

Thanks,
Brady

Good evening all, Yesterday I asked a question about automating a macro when switching from one worksheet to another. Erik van Geit kindly offered me the advice to use a "deactivate" code in the worksheet. Unfortunately, because of the way I have written the macro, using this code results in the programme getting in to a loop.

If I can explain further: The macro copies and pastes a row of cells from worksheet 1 to worksheet 2. However, using the "deactivate" event trigger, the macro does not start until worksheet 1 is closed, i.e. worksheet 2 is opened. At that point the macro returns to worksheet 1 to copy the cells. It then returns to worksheet 2 to paste the cells, but in doing so it closes worksheet1 and the macro kicks in again........ ad infinitum!!

Can I add a further element to the deactivate event code, so that the macro only runs when leaving worksheet 1 if there has been a change. If this is possible, then the macro would not be triggered for a second time when it leaves worksheet 1 after copying the cells.

I suppose another approach would be to have the macro run whenever a cell is changed in worksheet 1. I have seen the "Change(ByVal Target As Range)" event. This works if a value changes. Is there a similar command that triggers the macro if the format of a cell changes. e.g. its colour?

I know there are a few different questions here, but hope someone can advise.

Thanks, Dave

Hello Members, I'm not sure if This is a good way to explain this. But, Is This possible??

To clear only the cells in a worksheet. When the last cell U20 has a input number of 0.

I have 7 sheets in the workbook. There is only 1 sheet being used at the time in the workbook to add input numbers into the cells below.

Only the Cells below will have inputs in them. The other cells in the sheet need to be static without any clearing or changing of them.

F7 thru F20
H7 thru H20
J7 thru J20
L7 thru L20
N7 thru N20
P7 thru P20
R7 thru R20
U7 thru U20

Regards, Tom

I have the following code that clears the contents of a whole row.
Code:
Range("A" & Range("A5").End(xlDown).Row).EntireRow.Select
    Selection.ClearContents
I only want to clear the contents of the bottom row from cell A??:S??

Thing is, only col A will give me the correct bottom row, as there may not be data in B:S....

How can I therefore change the above code so that it finds the correct bottom row from ColA, and then clears the contents from A:S of that row. Reason being that i want to save formulas that appear to the left of col S, as they will be needed as the list grows...

I am currently getting the following error message when closing a workbook in
Excel 2003:

Cannot find 'C:Program FilesMicrosoft
OfficeOFFICE11Libraryadaytum.xla'!AdaytumSheetC lose, which has been
assigned to run each time [Workbook1.xls]Sheet1 is closed. Continuing could
cause errors. Cancel closing [Workbook1.xls]Sheet1?

I have looked on the list of add-ins and there are none checked, bit
confused by this. Obviously it's not causing any problems, but it is annoying
me!!

Any help would be appreciated.

Thanks

Laura

I have a large Listbox in a Userform that has 5 columns and can have up
to 1200 row entries. I have run into a curious behavior.

I have buttons to Select all entries in the Listbox and to Clear all
selections in the Listbox. My code is virtually idential to do the
Select All and Clear All. Here's the curious part: when I have ~ 1000
rows in the Listbox, it takes ~ 3 seconds to do a Clear All, but only ~
1 second to do the Select All. With a small number of rows, it
impossible to see any difference, but with a large number of entries it
takes noticeably longer to do the clear I have written the Clear All
code as fast as I can think of (no calculations within the loop). I
also tried turning off screen updating and calculations, but it doesn't
seem to make any difference.

Does anyone know of a faster way to do the clear? Here is my code.

Private Sub btnClearAll_Click()
Dim I As Integer
Dim count As Integer

With NameForm.ListBox1
count = .ListCount - 1
For I = 0 To count
.Selected(I) = False
Next I
End With
End Sub

The only difference between the Clear All code and the Select All
codeis False/True in line .Selected(I) = False.
Any ideas? Why would be it faster to do the selection?

Any info would be appreciated,
Thanks,
Jim

Hi,

I want to stop a loop of

Application.OnTime Now + TimeValue("00:01:00"), "NextSub"
when I close a workbook using Sub Auto_Close, it stops when I close excel but reopens and continues to run in the background when I just close the workbook.

Thanks for you help

Liz

Hi,

How do i get my marcos to automatically clear the clipboard when closing a
file. Currently, the macros close a csv file once the contents have been
copied into a central sheet. I am then faced with the question "There is a
large amount of data in the clipboard, would you like to keep it.........."

Once macro does this to 15 files and it is really annoying to have to keep
clicking 'No' when i run it.

Any help is much appreciated

Cheers

Greg


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