Remove AutoFilters Code
Sep 18, 2007
I am using the following code to select rows in a filter.
Selection.AutoFilter Field:=Worksheets(" Dashboard"). Range("G1"), Criteria1:=Worksheets("Dashboard").Range("H1")
This code runs when I click a command button on a userform.
When I load the userform up again I want to clear off the previous filter before I filter again as there are 4 columns which have filters on.
I have tried using ActiveSheet.ShowAllData before filtering again but this code falls over if there isn't a filter already on.
View 4 Replies
ADVERTISEMENT
Jun 11, 2008
I have an issue where users to a work book are selecting autofilter and then saving the document with rows filtered out, which I wish to prevent or at least warn them to remove the filter before allowing the save function.
The document is never 'Save As', it's always 'Save' from the toolbar icon.
They should be permitted to save the document, but only after this check has been completed and a message issued if the filter is on.
View 4 Replies
View Related
May 28, 2009
2 autofilters i am using, they are both initiated using VBA code and criteria on a control sheet however it appears that one is cancelling the other out, for example i manage to get my date filtered but no times, or if i alternate the run order i get times filtered but no dates i have enclosed the code below ...
View 10 Replies
View Related
Jun 2, 2006
I am trying to figure out if it is possible to retrive the list of filter criteria in each filter of the filters collection using VBA. I could trawl through the data and retrive a unique list but I think it would take longer than if I could use what is already there.
I intend to use the list in a combo box elsewhere in the workbook......it's all to stop inquisitive little users accidently changing data and whining that thier figures are misteriously wrong.....
View 5 Replies
View Related
Jan 25, 2009
I have this code below which autofilters a range and selects the data i need, there is also a small offset part that leaves the active cell as the one in the top lef of the needed data. I also have the row count of the data in cell G1. What I need to do is something along the lines of:
View 3 Replies
View Related
May 22, 2009
I have searched these boards (and found wonderful ideas), but can't find a better solution.
Is there a quick way to clear all the autofilters on a page (or all of them in 1 row)?
I am using this code, but it is very slow.
For i = 1 To 21
Selection.autofilter Field:=i
Next i
View 9 Replies
View Related
Mar 26, 2009
I want to have a small button at the top of the sheet that when pressed, will automatically reset all filtered autofilters back to show all.
View 9 Replies
View Related
Aug 22, 2006
I have a vertical list of data which is sorted in to "Regions" 1 to 7 there could be up to 25 instances of each Region, but I have no way of knowing.
I want to be able to select each Region and place it in it's own column.
View 9 Replies
View Related
Sep 10, 2008
I have a spreadsheet with two modules. In an effort to reduce the file size of an archive, I’d like to remove some of the code (i.e., Module1). I have a macro that attempts to do this.
There are three parts to the operation. The first removes Module1; the second Quits the application; the third saves the changes. If I run each of these operations separately, it works fine. However, I’d like to do this with only one macro (SKK4). For some reason, it does not remove the Module.
Here is my test
Sub skk1()
ThisWorkbook.VBProject.VBComponents.Remove_
ThisWorkbook.VBProject.VBComponents("Module1")
End Sub
Sub skk2()
Application.Quit
End Sub
Sub skk3()
ThisWorkbook.Close SaveChanges:=True
End Sub
Sub skk4()
skk1
skk2
skk3
End Sub
View 9 Replies
View Related
May 14, 2006
Private Sub Dupe_Remover()
'29/06/2005 by nhunter
Application. ScreenUpdating = False
Dim R1 As Range
Dim drow As Integer
Dim lastitem As String
Set R1 = ActiveCell
loopst:
If Trim(ActiveCell) = "" Then
Goto procend
End If............
View 9 Replies
View Related
Sep 18, 2006
I have a VBA sub "Create Invoice". After I run it, I would like to remove it, using a CleanUp sub.
Is it a good assumption that a VBA routine is simply an object like any other object?
If so, what is the VBA code to remove a sub?
Does it matter whether the sub is a free standing module, or a sub that is attached to a worksheet?
View 6 Replies
View Related
Jun 24, 2007
I have developed a series of spreadhseets in which I use a macro to derive and insert the data from a SQL Server db. These spreadsheets may have one or more worksheets, some of which start as blank "canvases", others are preformatted with static data.
They are used for client reporting so are heavily formatted and include graphs; some manual preformatting, some coded, some graphs drawn completely by the code, some already in place - I just populate the data source. The VBA for the most complex one extends to around 5-6000 lines of code.
While this code does not add a huge amount to the footprint of the files, I feel that it is inappropriate for the spreadsheets to be distributed to the end clients with all this code included. The code is password "protected" but we all know how effective that can be although that is not really the point anyway - I simply feel it would be more professional if the code were not there at all.
There doesn't seem to be any options on a "Save As" method which enable the resulting sheets to be saved without the macros. I have tried recording a macro in which all I did was to delete the macros concerned - nothing was recorded. So a macro to remove macros seems to be out of the question.
These spreadsheets are held in a master folder on a server and are set as "read only". The users start the a sheet, run the macro (via a menu item which displays a selection form) and then save the resulting spreadsheet as another file in another location. I am wondering if there is some way I can cut into this process and develop my own "save" routine.
View 4 Replies
View Related
Apr 10, 2014
I have a set of text in rows which includes duplicates like this:
Column A
Text 1
Text2
Text1
Text500
Text500
I then need to remove the duplicates and put them in column B. I normally use the remove duplicates button in Excel to do this but seeing as I have to do this task every day, I was hoping there would be either a formula or VBA solution which automatically does this?
View 4 Replies
View Related
Mar 23, 2014
I have the need to remove an ellipsis from an active cell within a spreadsheet. These are inserted manually by agents in a call centre. However when this information is migrated onto another system, any ellipses cause the second system to crash. (I don't know the reason behind this).
I have been asked to look at the possibility of removing these using code. I currently have a macro running to remove any punctuation and do spell check on a command button for the active cell and was wondering what the code would be to add to this macro. I am using the replace command to find "." etc and replace with " ".
View 6 Replies
View Related
Sep 2, 2009
Is there code available that will hide the Tools Menu within excel?
View 5 Replies
View Related
Apr 12, 2012
I am trying to create code that I can use to to update a macro module automatically so I don;t have to keep having my users manually import when we have update to the module.
Here is the code I have so far.
Private Sub Workbook_Open()
Call UpdateTJMacros
'This is in the module we are replacing
Call MyOpenWorkbookMacro
[Code] ...........
View 3 Replies
View Related
Dec 15, 2012
share a code to remove BLANK ROWS.
I have data on ROW#1, #5, #10...etc,etc.. I want to remove rows in btwn (which is blank) Instead of doing manually
View 2 Replies
View Related
Jan 26, 2007
I have built a tool that automates the creation of some excel models. When each one of these models is created and deployed I need it to have all VBA code removed from it.
What vba command can I run that will strip all vba code out of a chosen workbook?
View 9 Replies
View Related
Aug 20, 2014
I have a list of addresses, which are comma separated, all ending with a PostCode
How Can i strip out the PostCode field?
View 3 Replies
View Related
Apr 2, 2009
What I’m after is the macro code to remove all the Hyperlinks from the data in the cells in a worksheet.
View 2 Replies
View Related
Oct 15, 2009
Having some trouble with the syntax to translate the content of a string. The string will always contain stock tickers, but the format varies. I need to normalize them into something like ticker(space)exchange code.
For example, cell 1 can contain:
ABC.EF US - I need to remove the .EF so I'm left with ABC US
ABCDEF.GH TT - I need to remove the .GH so I'm left with ABCDEF TT
The length of the part preceeding the "." will vary, as will the last two characters (the exchange code). I expect that there will always be two characters immediately after the "." which are to be removed.
View 3 Replies
View Related
Feb 6, 2014
I have a spreadsheet which was formatted from VB code. Within the sheet there are four date columns where some dates show as 00/01/1900 (due to them having '0' in at the time of running the code). I am trying to run some code to blank out all cells showing the 00/01/1900 date, but the code just isn't touching it. I reverted to simply recording the 'find and replace' function and inserting into my code, but those irritating dates still remain.
Code I have used is as follows:
[Code] .....
View 4 Replies
View Related
Feb 27, 2014
I have a set of data in column A that looks like this:
White:0:0|Counter Height:0:0|Orange:40:0|Counter Height:40:0|Green:40:0|Counter Height:40:0|White:40:0|Bar Height:40:0|Orange:80:0|Bar Height:80:0|Green:80:0|Bar Height:80:0|
The delimiter "|" separates the unique values of an item (in this example they are dining stools of differing heights and colors), I need to only keep the lower value and remove the larger one.
The example above contains both White:0:0 and White:40:0 as two different values associated with the same dining stool. I need to only have White:0:0 remain and White:40:0 to go away.
View 2 Replies
View Related
Feb 5, 2014
I have a file that I want a code to look for certain data and when found move the row to another sheet. The code needs to look at the active sheet because the name of the sheet will be different everytime I use it. It will also need to create another sheet named 'Online' that these rows are moved to.
The code will need to look for data that begins with either OAP, MCP, CPP, F4P, VAP, VWP, ITP and MEP. These will be followed by numbers i.e MCP123456. Once these are found I need the entire row removed to the created sheet.
The columns that these numbers will be in will vary on each file I use it on so it needs to look at the entire sheet rather than specific columns.
Also any cell that has data that begins with either MH and JD followed by 6 numbers that row needs deleting completely.
View 9 Replies
View Related
Mar 22, 2007
I have a macro which opens one excel file, then copies the data into another, dead easy. However the first file is 'downloaded' from a bespoke package, where (for whatever reason) the package appends a number of spaces (" ") after data in one of the columns,
So sometimes the data will contain one, ten or more extra spaces (no telling how many) ie, it could look like "AB ", "AB ", or "AB " etc
Ideally What i need is a small bit of code that once the data has been imported to my sheet it can run and 'strip' extra spaces from the column, lets say column f, to leave all the data in this column to look like:
"AB"
"AF"
"CD1"
"VFE"
I am drawing a blank, any simple lines of code?
View 9 Replies
View Related
Dec 20, 2006
I have a macro that copies 3 sheets to a new workbook but I do not want to copy worksheet properties. Attached is my Macro that I use but when the user accessed via the webpage It will have sheet properties(macro) attached. my Desire is to not have macros on my new work book.
Sub WEBPAGE()
Dim wbkNew As Workbook
Dim wbkCurrent As Workbook
Dim xlCalc As XlCalculation
xlCalc = Application.Calculation
Application.Calculation = xlCalculationManual
On Error Goto CalcBack
Application. ScreenUpdating = False
Set wbkCurrent = ThisWorkbook....................
View 9 Replies
View Related
Dec 22, 2011
I have the following code:
Sub Macro5()
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveSheet.ChartObjects("Chart 243").Activate
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(2).Delete
End Sub
However, if there is no SereisCollection(1) actually present in the chart I get an error. How can I work around this? I will need an IF statement I assume, just not sure what it will look like.
View 4 Replies
View Related
Sep 26, 2013
I need to write a VBA code to remove the value of the cells in column B if the value in column A is empty. In my excel sheet, the user will paste hyperlinks in column B. If any link does not meet certain conditions, the cell in the same row in column A will be empty.
I want to have a change event so that after pasting links, it automatically deletes the links from the cells in column B if the cell in column A is empty
View 5 Replies
View Related
Dec 15, 2013
I have a workbook that has ben imported into excel and I need to extract the Cabinet sizes (red text) from ColumnC On Sheet2 leaving out the rest of the info. I have a button on the home sheet that has the code that I am running to generate this report it will do most of what I want but I am stuck at extracting the cabinet sizes.
The data is coming from the SheetComponetListing worksheet and going to Sheet2. I have manually created the end result that I am looking for on the CabinetSize worksheet. [URL]....
View 3 Replies
View Related
Aug 8, 2009
I have found a very useful UDF for removing non-alpha characters from strings. (See below, Credit for posting to Stanley D Grom - Ozgrid post ´Removing Non-alpha Characters From Text´).
Option Explicit
Private Function RemoveCharacters(InString As String) As String
Dim intLoopCounter As Integer
Dim intStringLength As Integer
Dim intASCIIVal As Integer
intStringLength = Len(InString)
InString = LCase(InString)
For intLoopCounter = 1 To intStringLength
intASCIIVal = Asc(Mid(InString, intLoopCounter, 1))
If intASCIIVal >= 97 And intASCIIVal <= 122 Then
RemoveCharacters = RemoveCharacters + Mid(InString, intLoopCounter, 1)
End If
Next intLoopCounter
End Function
Two requests:
1. Could the UDF be modified such that any part of a string contained within brackets is also removed (e.g. "NLGA High Street (West-Enfield), EN6" becomes "nlgahighstreeten")?
2. Can an argument be added to the format of the UDF, such that numbers (0 to 9) are either included or excluded (e.g. RemoveCharacters(A1,1) where the argument ´1´ would include any numbers (0 to 9), so "NLGA2003 High Street (West-Enfield), EN6" becomes "nlga2003highstreeten6")? ´blank´or ´0´would exclude these numbers, i.e. would return "nlgahighstreeten"
View 5 Replies
View Related
