Macro - Filter & Sum
Feb 25, 2009
I need is to run a macro which does the following:
Autofilter is on, so on column AR filter to results that show '1', then on column H for all results there are 6 max but some dont show when '1' is selected as data may not be present.
So once I have filtered down AR and then filtered down H, I need a sum on AN, and AO for JUST those results!
Now I can do all this manually and have a quick mock code but this needs to be done sooner rather than later so im going to cont doing it manually if anyone can help with the code it will speed things up for me tho, thanks!
Sub SpeedUp()
' Location is in H, Quartile is in AR
' I to AM is irrelevant
' SUM of AN
' SUM of AO
Dim Arr As Variant
Arr = Array("F1", "F2", "F3", "F4")
For i = 1 To 4
So basically loop thru, loop thru, change the filtering, subtotal on the new filters copy to a new sheet, or even hold all values in a table or array till the end and i will manually paste
View 9 Replies
ADVERTISEMENT
May 25, 2012
Writing code to do the following:
Save advanced filter settings
Remove filter (or simply set to be 'select all'
Run other code (I have this piece of course)
Put filter back on with same selections chosen as when it was removed
(Need this becuase the code in the middle does not work properly when the data is filtered)
View 1 Replies
View Related
Jun 8, 2009
I'm trying to write a macro that will custom filter a column for cells containing the value in a cell, the macro I have so far is:
Sub Filter()
ActiveSheet.AutoFilterMode = off
LookupVal = Range("C3")
Range("A8").AutoFilter Field:=4, Criterial:=LookupVal
End Sub
However that only returns cells that are equal to C3, normally to do contains I would put "* *" around the value but then that removes the reference to cell C3.
View 2 Replies
View Related
Mar 24, 2007
Having big problems getting an advanced filter macro to work on-going (meaning again and again and again once I update the table it filters from).
Macro is as follows:
Range("A1:O1").Select
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"H1:H2"), CopyToRange:=Range("T1:AH65536"), Unique:=False
Range("A3:O65536").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"G1:G2"), CopyToRange:=Range("AM1:BA65536"), Unique:=False
Do I need to specify the sheet that this filter should occur on? If so could someone indicate how I would get that into the macro?
View 9 Replies
View Related
Aug 6, 2008
I'm tryin to set up a macro to filter and sort and when I record the macro correctly it does not run properly.
I have detailed info below:
I created a sheet "Met Logbook" which contains all links to the source "Master Logbook". This master logbook has a lot of info added daily and we add info at the top of the sheet (we insert new rows every time at the top for different reasons). So what happens is the master logbook always changes its cells (when inserting new rows at the top, all the cells change row number). So I have just linked all the cells and more that we may be using in the Met logbook. This is why I need to filter and then sort the Met logbook (since there will be so many blanks, unuseful info and in the wrong order). (It will be in the wrong order because the "master logbook" has newer info at the top and I have more than one sheet of info for the master logbook) (so my links in the met logbook are somewhat out of order).
So what I need is to have code to filter column B with:
ã
and column H with "Blanks", then I need to sort column A (rec'd) in descending order (so that newer info will be at the top). I will post my Met Logbook sheet on my second post since its a little large.
View 49 Replies
View Related
Sep 22, 2008
Sub Custom3()
Range("A785:BW1455").AutoFilter Field:=2, Criteria1:="a"
Range("A785:BW1455").AutoFilter Field:=3, Criteria2:Range ("N").value
End Sub
Is Field 2 referring to Column B? So if I want to filter on the 9th row heading (going from left to right), my Field: = 9?
In the example above, the 1st criteria revolves filtering on dates with the goal of exceeding a certain date (i.e. > 1/1/08). Is there a way to reference the cell (which is in another workbook which is already open) in the criteria?
View 9 Replies
View Related
Nov 5, 2008
I want to make a filter started from row 2
the criteria should not be equal to B1
weekucase = UCase(Range("B1"))
Rows("2:2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=14, Criteria1:=" weekucase"
Rows("2:2").Select
Range(Selection.Offset(1, 0), Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
End Sub
But it doesn't work
View 9 Replies
View Related
Jan 25, 2013
I have created a macro to use auto filter with the name specified in the vba code, can I specify a range of cell to do Sutofilter.
View 7 Replies
View Related
Feb 15, 2013
I've written a macro in an attempt to automate the advanced filtering in Excel.
VB:
Sub FilterData()
Sheets("Filter").Select
Range("B10").Select[code]....
View 5 Replies
View Related
Apr 21, 2014
i have my "raw data" and in my "summary" sheet, i want to show the data by the data that is filter in range D15:D21
Essentially, when the user filters the data from cell D15, i want the the macro to paste the data row D27 down/across from the "raw data" table. in addition, i am not sure how to create dependent drop down.
for example, in cell D15 it shows "segment"; in the cell below, D16 (tyre size), i want it to list all tyre sizes within that selected "segment"; when i want D17, to show "speed index" for by segment and tyre size etc.
in the table, i want the macro to show the data based on what the user filters from above. i can then do my calculations but getting this to work is the tricky part.
View 14 Replies
View Related
Jun 18, 2014
I am having a spot of bother with my spreadsheet, when trying to automate some functionality. Effectively what I am trying to do is...
- With a comprehensive Project Plan press a button that extracts the information of cells that are marked as Critical.
- This information would pull through onto a separate Dashboard sheet, so that those critical items can get flagged to the Project Team.
- The data cannot be copied as a complete table, as there are various columns of data that I do not require copying.
- I have tried recording a macro with me 'filtering' the project plan for critical items and then copying that data across.
- This however only returns the cells originally marked as Critical, it does capture any changes to cells outside of the range in the code.
So,
- In Column C of 'Project Plan' sheet, I have tasks marked as "Critical" or blank.
- I want to copy data of those 'Critical' rows of data, from Columns B,D,F,I
- This data is then to go into the 'Dashboard' sheet, in Columns B,C,E,F.
I embed the code below, from my feeble attempt:
[Code]......
View 12 Replies
View Related
Jun 27, 2014
I am having trouble setting conditional date filters for a column using a macro. I have two columns, A&B, each with dates in each cell. I want to filter column B to show dates that are less than or equal the corresponding cell in column A.
For example: A1=10th June 2014, B1=11th June 2014 --> The filter is set on column B, and B1 is excluded as it is not <= A1.
View 1 Replies
View Related
Aug 6, 2007
I'm trying to set up a macro to to run and control the AutoFilter in my Excel Spread Sheet. Essentially, all i want to be able to acheive is, instead of inputting the cryteria myself in the auto filter, i want the macro to select the cryteria from a specific cell.
Example...
Running the autofilter, normally you would select "equals or grater than" option and you would input a figure then click the "or" option then input "equals or less than" and a new figure and then click ok. This would sort the range; see the example macro...
Selection.AutoFilter Field:=10, Criteria1:=">=01/09/207", Operator:=xlOr _
, Criteria2:="<=31/09/2007"
ActiveCell.Offset(774, -1).Range("A1").Select
ActiveWindow.SmallScroll Down:=-15
End Sub
What i want to beable to do is to run a macro which will do the above but instead or requiring the user to input the range cryteria for the filter it would point to a cell to get the value from.
' test1 Macro
' Macro recorded 06/08/2007 by Nigel M Bailey
'
Selection.AutoFilter Field:=10, Criteria1:="> Cell A2", Operator:=xlOr _
, Criteria2:="< Cell A3"
ActiveCell.Offset(774, -1).Range("A1").Select
ActiveWindow.SmallScroll Down:=-15
End Sub
In doing this i can validate the selection field and just add a search button which then will be perfect!
View 14 Replies
View Related
Oct 6, 2011
I need a little macro that will filter for the lowest value in a column
View 5 Replies
View Related
Feb 23, 2012
Auto Filter Using Macro
I have a Spreadsheet with names and other data, my question is, can i somehow Auto Filter the First name with a macro that I can assign to a button, what I would like to do is type the First Name in A1 and in B1 have a button that I can press and it will filter all with the name and other data on the same row I have typed in A1 will only be shown.
View 9 Replies
View Related
Jun 18, 2012
I have a recorded a macro to create a Pivot table from my data. In this Pivot I have to make the report filter field to filter the dates from the current month only. Is there a way that I can instruct my macro that in that filter it automatically filter the data from the current month range.
View 1 Replies
View Related
Oct 18, 2012
I have a search form that will filter the result based on the requested category. Is it possible to lock the cells once it has been filtered and then remove the lock after they close the workbook?
View 2 Replies
View Related
Oct 4, 2007
I've recorded a macro which selects "1" in a filter drop-down box then prints some pages, then comes back and selects "2" in the filter then prints some pages, etc etc etc.
I've looked at the code for this and it treats the "1", and the "2" etc as text each time - originally I had the filter on names, but the names change each time I need to do this whilst the process doesn't, so I filtered on numbers instead.
However, I would have to enter the repeated blocks of code down to 1000 to get it to select down to "1000" in the filter. About 3/4 lines each time but with me manually typing in 1, 2, 3, 4 .... 999, 1000.
Is there a way to say in VBA 'repeat filter selection until you've run out of numbers then come back to "All" and stop ?
View 9 Replies
View Related
Jun 24, 2006
I've a workbook in which I run an excel macro to filter out data for five regions W1, W2, W3, W4, W5, the macro creates five sheets (w1, w2, w3, w4, w5) and places them in the same workbook along with the original master sheet called "west".
The data in all the sheets is layed out in an identical fashion except that the the number of rows in each sheet will differ depending on the number of records for each region in the master sheet "west".
I was looking for some vba coding to automatically add sum totals in two columns (I & J) for all the five region worksheets.
The first record always begins from row 4, in all the sheets.
so as an example W1 sheet could have a sum formula in I20 = Sum (I4:I8)
and in COl J as Sum(J4:J18). Row 19 is a blank row, and the intention is to leave a blank row just before placing the sum total in all the sheets.
W2 will have the same starting range but might differ in how many rows to sum .
And so on for the 4 remaining region worksheets in the workbook.
View 9 Replies
View Related
Dec 2, 2006
My problem is modifying existing macro code (see below) provided by Jim.
Sub WriteDetentionRegister()
Application. ScreenUpdating = False
ClearDetentionRegister
SortDataBase
AutoFilterDataBase1
Dim rDataRange As Range
Set rDataRange = DBase.Range("rDataBase"). CurrentRegion.Offset(1, 0)
rDataRange.Resize(rDataRange.Rows.Count - 1, rDataRange.Columns.Count - 2).Copy
DREG.Range("rdetreg").Offset(1, 0).PasteSpecial xlPasteValues
DREG.Activate
With Selection
.Sort Key1:=Range("rOffDate"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortTextAsNumbers
End With
DREG.Range("rdetreg").Select
Application.CutCopyMode = False
End Sub
At present the writedetentionregister macro filters the database sheet for a positive balance (in column H) and pastes these records into another sheet called Detention register.
Is there a way of also getting the writedetention macro to count 7 days from the offence date (the E column of the database sheet) and ONLY on or after that date write that record to the Detention Register Sheet?
The workbook containing the database & the detention register sheets are enclosed.
View 9 Replies
View Related
Mar 18, 2008
I have just started trying to learn VBA as its pretty cool but I am getting stuck on how to filter by non-blanks. It's probably something easy:- here is what i have got so far, but its not working
With ActiveSheet
.AutoFilterMode = False
. Range("A1:V1").AutoFilter
.Range("A1:V1").AutoFilter Field:=22, Criteria1:=0
.Range("A1:V1").AutoFilter Field:=5, Criteria2:="<>"
End With
View 4 Replies
View Related
Mar 30, 2008
I do not know VB coding but is in need of help on data filtering/matching. I have 2 very big excel workbooks with a few thousand lines and am trying to do some filtering/matching. Example below.
The two workbooks I have contains same data in column A and also column D from the first data set is the same as column B in the 2nd data set. I am trying to add column C and D from the 2nd data set to the 1st data set and need to make sure they match up to column D from 1st set and B from 2nd set. It is quite strenuous to do this manually,
A1 2 3 100A
A1 2 3 100B
A1 2 3 100C
A1 2 3 100D
A1 100A AA BA
A1 100B AB BB
A1 100C AC BC
A1 100D AD BD
View 3 Replies
View Related
Apr 30, 2008
I have a set of data that I need to sort and filter and create pivot tables with 2-3 times per week. Example data sheet attached (confidential data removed). I will insert the code that I am using for reference. The issue is that when the data is filtered for 'Approved Timesheets' I need the filter to be based on 2 criteria; If myBaseRow.Cells.Item(1, 21) <> "Approved" Then If myBaseRow.Cells.Item(1, 44) <=StartDate And myBaseRow.Cells.Item(1, 44) >=EndDate Then
The first If statement works fine, but the second appears to be ignored?? I have tried to colour this part of the code blue to make it easily visible.
Private Function PTSubtotals(ByRef PTField As PivotField)
PTField.Subtotals = _
Array(False, False, False, False, False, False, False, False, False, False, False, False)
End Function
Sub Timesheets()
'
' Timesheets Macro
' Timesheet Filter 4/22/2008 by Richard Francis
'
Dim myWorkBook As Workbook
Dim myBaseWorkSheet As Worksheet
Dim myBaseRange As Range
Dim myBaseRow As Range
Dim RowsCounter As Long
Dim StartDate As Date
Dim EndDate As Date
StartDate = Format(Date, "mm/dd/yyyy")
EndDate = Format(Date, "mm/dd/yyyy")
On Error Resume Next
StartDate = Application.InputBox("Enter start date", Type:=2)
On Error Goto 0
If StartDate > 0 Then.................................
View 3 Replies
View Related
Jun 28, 2008
This is quite a easy one. I am a begineer so I don't know how to do it. I keep getting a runtime error. I want create a macro that does the following. I have this data(it is made up).
NameSchoolFee
JohnFDU700
MarkeyNYIT400
NickNJIT500
SamBU100
I want it to autofilter school njit
NickNJIT500...........
What would be the code for this? I want use the code for bigger data?. Also if you have a file which is 100 lines long and your macro does a series of steps for that file. Then you have another file and you want the same steps done but it is 500 lines long. How would you write a macro thatrun the steps taking in the last line and not just a 100 lines.
View 2 Replies
View Related
May 7, 2014
basically i have a macro to filter a lot of data based on peoples names but the people keep changing so I dont want to go into VBA each time to update the name list int he macro.I've created a list of names in a sheet in excel but not sure how to point the macro to filtering by that instead, heres what I have:
VB:
ActiveSheet.Range("$A$3:$H$61").AutoFilter Field:=7, Criteria1:=Array( _
"Name1", "Name2", "Name3", "Name4", Operator:=xlFilterValues
ActiveSheet.PageSetup.PrintArea = ""
[Code]....
I have named the database list for each name and its on a sheet called Team_List
View 4 Replies
View Related
Jun 19, 2014
I have a spreadsheet. This has columns begining with A to S (with data in it)
It has rows beginning on row 5 and goes down to 85 but it gets bigger every day.
I want to create a macro with several phases
Phase 1 - Select the rows I specify, ie pop up box to decide on the rows
Phase 2 - Filter down and select the rows in this selection where column K has a value (unselecting the rest)
Phase 3 - Copying Columns A, B, D, E, F, K, from the selection
Phase 4 - Pasting these columns into A, B, C, D, E, F, of the sheet the macro sits on....
View 14 Replies
View Related
Feb 28, 2014
I have a written a macro to populate a filter criteria with a comma separated list, but the filter fails to work as each individual item on the list needs to be surrounded by speech marks (") and I don't know how to do this.
This may not be an actual vba issue?
The list I am using is derived from =SUBSTITUTE(TRIM(BK9&" "&BK15&" "&BK21)," ",",") There are 120 different cells that this formula references that could be blank. The SUBSTITUTE TRIM functions allow me to just use the cells that have data in. But I can't seem to get the " symbols in there. (I've tried the TEXT function on the original cells, & this doesn't work.
View 3 Replies
View Related
Jul 8, 2009
I'm having with creating a macro to filter some data from one sheet to another. I used the "record a macro" function to get the code I've posted below but part of the code is always changing.
In my workbook I have a main sheet called "Data" and another sheet called "Parts". The code below does what I need it to do, but the problem is that the cell range (A2:I139) is not constant and may change depending on how many rows of information I import on the "Data" sheet.
What I'd like to have happen is either have a dialog box pop up and prompt me to select the cell range (I've tried using the Application.InputBox function but can't figure it out), or since the first cell will always be A2 and the last column will always be I, have excel automatically figure out the last row that contains data.
View 2 Replies
View Related
Feb 9, 2012
I have a large itemised call bill that i need to do some regular analysis on and wondered if I could automate most of it.
In column C is a list of mobile numbers, in column F the numbers they called (this is an itemised bill so each line represents one call, meaning each number has multiple rows) finally in column K is the cost of each call.
I want the macro to look through column F (number called) and if there are less than 5 instances of that number that are under 0.30 each in cost to be deleted.
Example: if in column F the number 07500 100100 appeared once with a cost of 0.29 I want it deleted but if it appears 6 times with an accumulated cost of 3.50 i.e. more than 0.30 per call averaged out, then i want it to remain on the sheet
View 3 Replies
View Related
Jun 7, 2013
I need the macro to filter a table using the name of active worksheet as criteria. The code that I am writing is as below, but it doesn't seem work:
ActiveSheet.Range("$A$1:$AE$421").AutoFilter Field:=19, Criteria1:="Activesheet.Name"
If I hard quote the name of the sheet then, the macro, unchecks all the criteria in the filter and does not show and row in the table.
View 1 Replies
View Related