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............................
View 4 Replies
ADVERTISEMENT
Jul 7, 2006
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.
View 4 Replies
View Related
Oct 8, 2009
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+
View 3 Replies
View Related
Jul 31, 2009
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? .......
View 9 Replies
View Related
Jun 20, 2006
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"?
View 9 Replies
View Related
Jun 12, 2008
I have the following in Column A
28VNC
JR2KL
29C9O
PQS11
I'd like have this result
28
2
299
11
How would I achieve this.
View 6 Replies
View Related
Mar 7, 2007
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....................................
View 2 Replies
View Related
Aug 8, 2013
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
[Code] .......
View 1 Replies
View Related
Feb 28, 2009
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 )
View 4 Replies
View Related
Apr 4, 2007
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...
View 9 Replies
View Related
Feb 5, 2008
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?
View 8 Replies
View Related
Mar 25, 2014
I need a VBA code to do the following:
- The active cell i.e. the selected cell can be anywhere on row 11 (this is a merged cell with row 12 but I can unmerge if causes problems)
- The filter needs to be applied to the rows below the active cell (at most this will be about 10,000 rows below)
- I need to filter for non-blanks only
- Above row 11 there is one blank row and a number of filled rows; these need to be ignored i.e. excluded from the filter
- In this case I will need the filter to work on the cell with 'France Trade Product' in it
- The filter would need to hide rows below this cell except for row 16 where a non blank is found
FilterCapture.JPG
View 1 Replies
View Related
Feb 15, 2010
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
View 2 Replies
View Related
Jul 17, 2007
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.
View 9 Replies
View Related
Jul 13, 2007
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.
View 4 Replies
View Related
Aug 2, 2014
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.
View 2 Replies
View Related
Nov 4, 2013
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.
Here is my code:
Code:
ws.ListObjects(SheetName).Range.AutoFilter Field:=3, Criteria1 _
:=">" & CDate([datecell]), Operator:=xlAnd, Criteria2:= _
"
View 1 Replies
View Related
Dec 8, 2013
I am using the following to autofilter a column based on a month number
Worksheets("Data").Range("$A:$M").AutoFilter Field:=8, Criteria1:=Month(tempmon)
tempmon contains the month number (e.g 9 for september)
when I run this it filters but tempmon is equaling 1 when I know it is set to 9
i have tried
Worksheets("Data").Range("$A:$M").AutoFilter Field:=8, Criteria1:=Month(9)
View 9 Replies
View Related
Jan 25, 2008
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.
View 9 Replies
View Related
Sep 15, 2006
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...
View 3 Replies
View Related
Sep 25, 2009
Im sure this is a very common problem. I tried searching for it but I havent found anything that solves this for me. Here is the code Im using:
View 3 Replies
View Related
Aug 7, 2014
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?
Find attached an example : example.xlsm‎
View 3 Replies
View Related
Jul 19, 2007
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 attach a workbook, of desired results.
View 12 Replies
View Related
Sep 15, 2009
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
View 10 Replies
View Related
Oct 22, 2013
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
View 5 Replies
View Related
May 23, 2014
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?
View 3 Replies
View Related
Feb 23, 2010
I am looking for a vba which will send an email to someone the minute I highlight a row in red?
View 9 Replies
View Related
Jan 9, 2007
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
View 3 Replies
View Related
Jul 30, 2014
I have a table of data which I would like to filter based on a manual cell input instead of the autofilter option.
The table has tickers in column B and has information from column B to U. I would like to filter the data based on a manual ticker that I enter.
View 6 Replies
View Related
May 27, 2009
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?
View 9 Replies
View Related