Macro To Autofilter Out All Non Numerics In Column
Jul 11, 2007
working with a macro to auto filter out all NON-NUMERICS in a column chosen by the user. heres the code i'm working with now:
Sub DeleteAllAlpha()
Dim rTable As Range
Dim lCol As Long
Dim vCriteria
On Error Resume Next
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else
Set rTable = . CurrentRegion
On Error Goto 0
End If
End With
If rTable Is Nothing Or rTable.Cells.Count = 1 Or WorksheetFunction. CountA(rTable) < 2 Then............................
I'm using Excel to map automotive parts to vehicle applications (two different spreadsheets) for importation into a relational Access database. Production dates for parts rarely match the on-sale dates of the cars, so I've set up a macro that:
1. copies the start production date for a part record into the vehicle workbook at the top of the 'discontinued' column, 2. copies the end production date for the part into the vehicle workbook at the top of the 'release' column, 3. uses these pasted data points as criteria for the autofilter in the vehicle workbook as 'vehicle release date' < 'part end production date' and 'vehicle discontinued date' > 'part start production date'.
I find that the macro works perfectly in the autofilter for the vehicle discontinued date, but not for filtering the vehicle release date. The date is copied and pasted OK. When I check the custom filter dialogue box, the date has been entered in with the 'is less than' menu item selected, but it doesn't bring up any records unless you click on the 'OK' button once the dialogue box is open. I want to run the macro without having to run the autofilter manually at all. To troubleshoot the issue, I broke up the macro into two separate macros, one for release and the other for discontinued. Problem remains, even though the only difference between the two macros is the relative cell addresses. I've checked formatting of the cells for text vs. numeric and that doesn't appear to be a problem.
I've used LOOKUP in the below and it works great. LOOKUP(C8,{"A","A-","A+","B","B-","B+","C,","C-","C+","D"},{9,8,10,6,5,7,3,2,4,1})),)
But I've not been able to use that knowledge (or Excel help) to get the following to work.
LOOKUP(D108,{<1,>=1,<1.25,>=1.25,},{"D","C","C","")))...etc. where D108 is in the range <1.0 to >2.0, and the letter grade must correspond to D108 to Letter Grade <1.0 D 1-1.25C 1.25-1.5B 1.5-1.75B+ 1.75-2.0A >2.0 A+
I'm trying to figure out the whole Regular Expression thing...so I figured Hotpepper's EXDATA UDF would be a good place to try and rewrite with Regular Expression...I got it to work...but not sure why as it seems opposite of what I would have expected...
eg pattern [^/d] i thought would give me everything except numerics...but it appears to be giving me numerics....
here's the code and sheet...can someone help explain what's going on? .......
Is there any formula or easy way to automate the changing of a numeric input into words, eg if you input 100 in one cell, the output in another would be "one hundred"?
I'm trying to write a program for work. One sheet (whereiseverything.xls) will have a list of parts column E and where it is in the process column (k). Column E of this sheet will have multiple part numbers. (Sometimes duplicated.) I would like to figure out how to write a code to Auto filter (whereiseverything.xls) sheet and copy only one P/N from (whereiseverything.xls) column E onto another Workbook Worksheet (Commit status.xls) column. After that the sheet must copy all of the locations of that P/N from whereiseverything.xls column K into and under the P/N of the Commitstatus.xls.
It will continue to autofilter and copy from where is everything, the "one" p/n and all of its locations into another empty column of Commitstatus.xls until it no longer has part numbers to autofilter on whereiseverything. I am extremely green on VBA programming but here is my first attempt.
Windows("Where is everything commits .xls").Activate Windows("whereiseverything[1].xxx").Activate Columns("D:D").Select Selection.AutoFilter Windows("Where is everything commits .xls").Activate
Windows("whereiseverything[1].xxx").Activate Selection.AutoFilter Field:=1, Criteria1:="7516113-905" \<--this Is one p/n Columns("J:J").Select Selection.Copy....................................
I have the AutoFilter Field:=4 piece of code but what if the column number changes?
How can i assigned AutoFilter field to be the actual column name. The column name is Current State and have declared it in the below code as string --> strSearch4 = "Current State"
Should i assign strSearch4 to AutoFilter Field? If so how?
Sub CommercialView() ' ' CommercialView Macro '
' Dim wrkbk, sourceBk As Workbook Set sourceBk = Application.ActiveWorkbook 'Clear Filter for all Columns START With ActiveSheet If .AutoFilterMode Then If .FilterMode Then .ShowAllData End If Else If .FilterMode Then .ShowAllData End If End If End With 'Clear Filter from all Columns END
'Copy the required columns and add them to the destination spreadsheet START Workbooks.Add Set wrkbk = Application.ActiveWorkbook sourceBk.Activate wrkbk.Activate sourceBk.Activate
what i need to do is count all the instances in column h excluding headers so in the attached sheet. in a2 i need it to return one of the teams ( in this instance sheffield wednesday) and then in cell b2 i would like it to return how many times (sheffield wednesday) appears in column h in c2 draw. in d2 i would like it to return how many times (draw) appears in column h and the same again in e2 and f2 for the other team. i need it to understand that it may be any team as the next set i use may be for instance utd v chelsea etc etc. in row 3 there will be ( which there isnt now a filter )
I have a sheet with many labels arranged horizontally across the columns. I would like to Autofilter one of the columns but not see the filter drop downs for any of the other columns...
I have an autofilter set up that hides all rows with 0 in the first column. This information is automaticaly filled in from information on another sheet. Using a worksheet code from a previous thread I have got all the worksheets to automatically refresh which is great.
Private Sub Worksheet_Calculate()
If Me.FilterMode = True Then With Application .EnableEvents = False . ScreenUpdating = False End With
With ActiveWorkbook .CustomViews.Add ViewName:="Mine", RowColSettings:=True Me.AutoFilterMode = False .CustomViews("Mine").Show .CustomViews("Mine").Delete End With
The issue I have is that there are 52 worksheets (it's a weekly rota) and if any change is made anywhere on the sheet, all the worksheets then refresh which takes just over a minute (about 40 changes need to be made each day).
Does anyone know if there is a way that the information will only refresh on all sheets if changes are made in colums A? as this is the only information that is taken through to other sheets. Or is there another way of doing this?
I have a column of dates: 1 day per row: 01/01/10, 02/01/10, 03/01/10, etc in sequence, without gaps (European date format). The list can begin with a different start date depending on initial user input and is not always a Monday. I want the user to be able to autofilter so that it only shows dates that fall on a Monday.
Private Sub Worksheet_Activate() Dim dDate As Date Dim strDate As String 'disable autofilter if already enabled With ActiveSheet .AutoFilterMode = False End With 'autofilter column a only, from a12, to filter Mondays If IsDate( Range("a12")) Then dDate = Range("a12") strDate = Weekday(dDate) Range("a12").AutoFilter Range("a12").AutoFilter field:=1, Criteria1:=strDate End If End Sub
I have an Excel 2003 worksheet that has a list (Data > List > Create List), which displays the AutoFilters for each column in the list. I am seeking a macro that will filter the results (Custom > does not contain "Closed").
I would like to assign the macro to a button as the casual user might not understand the AutoFilter use.
The worksheet in VBE is defined as "Sheet3 (Audit Findings)" My list has headers on row 7 (A7:K7) I would like the AutoFilter to return all results except those marked as "Closed" in column K.
I am fighting a quixotic battle against a problem, that I realise may well be unsolvable. I have a worksheet that consists of approximately 27,000 rows in a four-level structure, like so:
1 1.1 1.1.1 1.1.1.1
To make the sheet easier to navigate I have created an expanding/collapsing tree structure by using simple hide and unhide rows, and on the last level of headings (1.1.1) I have cells that change between "+" and "-" depending on whether the fourth-level subset is currently visible or hidden. Naturally I need to keep the cell as "+" when the set is collapsed, and "-" when expanded. I have accounted for all methods user can change the hidden settings of those rows, but one:
When users autofilter the list, the +/- signs obviously screw up, because filtering resets the user-set hidden-settings. Suddenly I have expanded subsets with "-" on headings that were previously "+". Now this is obviously a vanity problem, as the +/- sign is not integral to the working of this macro, but the problem is that with three different levels of headers the worksheets starts to look a little cluttered.
Finally, my question: Is there any way to trigger a procedure to reset these signs upon/after autofilter? I realize that this is not a built-in Excel event, but a workaround will do just fine. Any way to detect that an autofiltering has taken place without having to check the.
I am looking for an Excel macro that will Autofilter a worksheet to display all rows with a empty value in one column (e.g. column B) as well as display the entire row above the aforementioned row with a blank cell in column B. I need it to be "reversible" so that I can click the Autofilter button (or advanced filter button) to show all rows again.
I got a problem using AutoFilter with VBA in Excel.
It works well for regular filters, but filtering the date column does not work as intended. The column is formatted as date, I can filter it manually and absurdly, if I run my code, it filters nothing but when I check the filter and then only click ok (no change being applied to the filter criteria), it starts filtering correctly.
I am running into a roadblock, I have added an auto filter to my spread sheet, however now when I use it the filters I select are not showing up on the column that is being filtered. For some reason they are 2 -10 colums over, a totally seprate column it is kinda weird.
When activating Autofilter in a wide table, the only way to determine which criteria field was selected is to try and identify which drop-down arrow has become blue. When working on a laptop, it is rather difficult to identify the difference between the black arrows and the blue ones. Is there a way to have a macro or event procedure that will cause the selected criteria field (or fields) change its background color (into yellow, for example)? This way, the yellow fields will "stand out"… No more searching...
My problem is following: I have a list of data that are classified according to a particular character, and I want to copy (with auto filter through the macro) the relevant information to the appropriate place in the sheet where it belongs. That's no problem. The problem is that if I want to copy data, classified by a character that is not listed in the table (that is not in the filtering criteria), then all the data are copied to the appropriate place. But I do not want to copy in this case nothing. How should such a macro look like?
I have a main workbook with 40,000 lines of data for various locations. Column A shows the locations. They all have the same fields in column B to N
I am looking for a macro that will filter on column A (Location name) & for every location in that’s in there, Copy it, open a new worksheet, paste the data for that location into it, plus, name the tab the same as the location name that’s been pasted in there.
I am trying to create a macro to autofilter a sheet based on the value a user will enter into a popup box. I have found bits of code which I have been attempting to figure out and use somehow however I am getting more lost.
I can create a basic macro to autofilter, the problem I am having is that I am unsure of how to link this to an input/popup box of some sort
a macro (that i will link to a control button) that will autofilter a data set. The problem i have is the macro i wrote below, It might not be the best looking macro in the world, but it would work for my purposes if i can get the part that does the autofiltering to be more dynamic. meaning, instead of a hard coding "Retail" in the macro, id like it to reference a cell so that the user can type whatever they want, then click the button and it will filter based on what they type in.
this is what i have Sub Filter_Button() With Sheet2 AutoFilterMode = False Range("A6:M6").AutoFilter Range("A6:M6").AutoFilter Field:=2, Criteria1:="Retail" End With End Sub
I know how to filter based on cell value, and how to auto filter "does not contain", but is it possible to combine these? i.e. Filter OUT the value of a cell from a range?
I recorded a macro to custom AutoFilter 2 columns in a worksheet. The information will be filtered between 2 dates; i.e. >= cell B2 and <= cell C2.
Both cells are formated to dd/mm/yy so i entered 1/12/06 (for 1st Dec) in cell B2 and 31/12/06 (for 31st Dec) in cell C2.
When i enter these 2 dates, click the button to run the macro and check the custom filter in the "Planning" worksheet it appears that the dates change to 12/01/06 and 21/12/06 and no records appear - although i know at least 65 rows should show.
If i then enter the dates in B2 and C2 as mm/dd/yy instead (but leave the date format of the cell as dd/mm/yy) the filter works perfectly; i.e. enter 12/01/06 and 12/31/06. However I plan to issue this spreadsheet out to other staff and as every other date in the spreadsheet is in the uk format dd/mm/yy i dont want to confuse them by insisting they use the mm/dd/yy format for this one function.
Has anyone ever encountered this before? and if so is there some way i can fix it?
Heres the code for the filter:
Sub Monthly_Stats()
Sheets("Planning").Select Selection.AutoFilter Field:=5, Criteria1:=">=" & Sheets("Filtered Statistics").Range("B2").Value, Operator:=xlAnd _ , Criteria2:="<=" & Sheets("Filtered Statistics").Range("C2").Value, Operator:=xlAnd Selection.AutoFilter Field:=82, Criteria1:="<>" End Sub
I read your reply to the thread below, and used your Worksheet_Calculate routine from it. It only works for the first sheet in my workbook. I have multiple sheets in the workbook, and when I run the routine on an activesheet other than the first sheet in the workbook, it doesnt work.
Is there a way to get this routine to work on a worksheet other than the first one in the book?
