I have below VBA code which I recorded in order to do the selection of filter value.
[Code] ......
But how can I make range in above code to be dynamic? Because every time data would be different so range would vary..
I have a vba script that makes a pivot table, works well, but I need to link the pivot table filter to a 'namedrange' cell on a spreadsheet. I have found the code below on google but can't seem to get it to work, when the cell is updated the pivot does not refresh.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
I have a spreadsheet with columns A to X.
There are about five variables which, when entered, should format the row their on. For example, if I select "Waiting List" from a dropdown in column A, the row is formatted to have a blue fill.
I'm currently using this code:
VB:
Sub waiting()
Dim sh As Worksheet
Set sh = Sheets("Applications")
sh.Range("A1:X1").AutoFilter Field:=2, Criteria1:="Waiting List"
sh.Range("A2:X2" & sh.Cells(sh.Rows.Count, 1).End(xlUp).Row).Interior.ColorIndex = 37
sh.ShowAllData
End Sub
I have five such macros.
The problem I am having is that when there is no entry for the macro to work on, it just formats rows 2-21 anyway, and overwrites any other formatting.
I have attached my workbook so you can take a look and see what I mean. Firstly, select "Waiting List" in cell B2, then run the macro "waiting". It should work fine.
Then, delete the content of B2, and run the macro again.
I am writing a VBA code to make an advanced filter. Instead of a fixed criteriarange, I would like it to be dynamic. As each time the number of criteria is different.
e.g.
Range("A1:G30").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("L1:L18"), Unique:=False
I want the criteriarange to change. e.g. It include all the data if I put in 20 data in col L.
We have 2 dynamic ranges (input, output) on different sheets (sample data below).
Sheet1> Input
Dynamic range 5 columns
Sheet 2>Output
Dynamic range 4 columns
- Include only rows IN=1
- Sorted by LEVEL (BIG to SMALL) and BUY (SMALL to BIG)
- Keep duplicates
What formulas should I place on Sheet2 avoiding Pivot tables or VBA?
Sheet1> Input (16 data rows)
A B C D E
Level Buy Sell Firm IN
16620 4.00 null F1 OUT
16610 5.10 0.80 F1 OUT
16600 11.40 6.60 F1 1
16590 24.50 18.60 F1 1
16580 44.90 37.10 F1 1
16570 66.90 59.60 F1 1
16560 84.40 78.70 F1 1
16550 95.00 90.30 F1 1
16540 99.80 95.60 F1 OUT
16530 100.00 98.00 F1 OUT
16611 6.66 0.497 F2 OUT
16600 9.09 1.96 F2 OUT
16589.1 20 12.5 F2 1
16578.2 41.66 33.33 F2 1
16567.3 73.33 65.21 F2 1
16556.4 90.9 84.61 F2 1
16545 98.5 91.66 F2 OUT
Sheet2> output (10 data rows)
A B C D
Level Buy Sell Firm
16600 11.4 6.6 F1
16590 24.5 18.6 F1
16589.1 20 12.5 F2
16580 44.9 37.1 F1
16578.2 41.66 33.33 F2
16570 66.9 59.6 F1
16567.3 73.33 65.21 F2
16560 84.4 78.7 F1
16556.4 90.9 84.61 F2
16550 95 90.3 F1
I bring in data with these same columns A thru J. I do not know how many rows will be comming in. I have create a macro that uses the advanced filter. I have a criteria range set up on sheet 2 with 6 variables, the main data is on sheet 1. This works fine if I use all 6 variables. I wish I could select from a list box which of the 6 or all variables I need(I might select 2,3 or more variables), & have that information populate the criteria range. I only filter in place(no copying), there is only filtering on column B, the label for the criteria range will always be "Name" it will be located on sheet 2 D:1. Is this possible or should I be trying some other function? Here is the macro that uses all 6 criteria. I have searched and could not find what I was looking for.
'add criteria range
Sheets("Sheet2").Select
Range("D1").Select
ActiveCell.FormulaR1C1 = "Name"
Range("D2").Select
ActiveCell.FormulaR1C1 = "e11*"
Range("D3").Select
ActiveCell.FormulaR1C1 = "e12*"
Range("D4").Select.........
I have a very large data sets that I'm working with in excel 2007 and I have to continually filter and unfilter the data by groups. I want to know if there's a way that I can have the values from my formula update themselves each time I filter/unfilter the data. Suppose I have a table like this:......
Where there are two groups, A and B, and each person has an age, and then the third column we have a ranking. This ranking in third column is computed by sorting the data by age and then by group, and then we set the first entry to 1 and then every other entry has an "if" equation where if the group is the same as the previous column then we add 1 to the ranking, and if not then we set it equal to 1.
Now say that just as a thought experiment we want to only look at females and have the rankings for females. If you do a basic filter and select only show girls what you get is:.............
I have multiple Userforms with labels etc, all controls are similarily named (so I could do this) and I can refer to the Userform dynamically but the Label caption/Controls don't change, any idea's how to get them working.
View 7 Replies View RelatedI have a weekly report which lists items and their available date. This list is always changing - new items added, old items removed. These items are required for 4 different projects and the item listed says which project the item date is for. There is also the same item across different projects which also have different dates which also appear and disappear each week depending on requirements.
I want to be able to capture the date for each of the items per project per week. Ultimately, I want to be able to track the movement of the date per item per project. I have attached a sample of how I want it to do.
I have a table which takes the average of the last three years. The formulas are in cells below the data. The data is set up to be first data down to the oldest data. each time data is added, a row is put in on top. how do i get the formula in the cell to update dynamically. What would the average formula look like in the cell? Do you just say =average(name of file!RANGENAME).
View 5 Replies View RelatedI have a range which will change in size & in content, & I want this to be a Named Range at whatever size it is.
Reason I want to is because I want to make a Validation List with this dynamic range. I also want a Validation list which lists the content of 2 or more dynamic ranges which may or may not be on the same worksheet - is this possible?
i.e.
First dynamic range: called "Milestones" at A11
Second dynamic range: called "Activities" at A25
& make a Validation list that will list content of both
I am trying to create a function using vba and the advanced filter function.
The spreadsheet I am using changes from week to week so it can have different amounts of columns and also rows.
This is my code.
Sub FilterCGML()
Dim rngToFilter As Range
Dim rngFilterCriteria As Range
Dim LastRow As Long, LastCol As Integer
With Sheets("Main FTE")
Range("b2000").Value = "Cost Code"
Range("b2001").Value = "5*"
It works ok if I have values in columns A:X but if for example I only have information in A:S I get a runtime error.
I know how to find the last column and last row by using
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
LastCol = Range("IV1").End(xlToLeft).Column
But I don't know how to modify my code to use these.
How can I FILTER a range and display the unique items, one below the other, WITHOUT blank cells - with only a FORMULA. What I came up with is shown in the attached WB. I would like to present the countries like in C11:C15.
View 5 Replies View RelatedI found a great bit of Advanced Filter code that works great, and fixed a problem of clearing a cell breaking the filter.
But if I want to increase the criteria from 1 row to 2, so you can start to include And , Or operations, it breaks the filter. Even an attempt at a manual one fails, until you put the criteria range back down to one row, then it's fine again.
I've tried changing the Target Row to >2 but that didn't work. how to make the criteria range bigger, and no problems of breakage if you clear the cells? It makes for a very useful automated Advanced Filter.
Here's the code :
[Code] .....
Database = the named area of raw data.
DATA is the name of the raw data worksheet
The criteria range should be AZ1:BC3, but of course royally breaks it...
I am trying to filter a table of dynamic size. My table begins at P3 (first row of data, not column header) and ends somewhere at the bottom of Column AA. I am getting an error "AutoFilter Method of Range Class Failed." Why? How do I fix this?
Here is my code
VB:
If Target.Range.Address = "$B$4" Then
With Sheets("Days Past Due")
LastCell = .Cells(.Rows.Count, "AA").End(xlUp).Row
MsgBox LastCell
ActiveSheet.Range("P3:AA" & LastCell).AutoFilter Field:=17, Criteria1:="MABST"
End With
End If
I have a Filter set up across a few columns of data. Lets say one of the columns is called "Hours", and another is called "User". There is another cell called "Totals" outside of the Filter.
What I would like to have is a self-updating Totals cell that will Sum the total number of Hours for whatever the Filter is currently showing.
Examples:
If I set the Filter to show only UserA's data, the Totals cell will Sum the Hours for UserA.
If I set the Filter to show All Users with Hours greater than 2, the Totals cell will Sum all the Hours greater than 2.
If I set the Filter to show UserB and UserC and Hours less than 5...well you get the idea.
Can I write a function that loops through the data that meets the Filter's criteria? Do I need to create a macro to update the Totals cell each time the Filter is changed? Is there an easier way that I don't know about?
The range element below needs to be dynamic. A1 and down meaning if i was to do this out of VBA I would hold CTRL+SHIFT+DOWN but i cant do that from macro record as it doesn't work, I'm guessing this Range("A1:A73") is the element I need to change but I don't know what to?
Range("Main[#All]").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Input").Range("A1:A73"), Unique:=True
I need to filter data which is 3 months from todays date or older. I'm going to use it in a macro which downloads the information from our accounts system.
I have set up the filter and it works fine when I manually put the Date in, but when I use the date 'formula' for todays date -3 months and reference the cell its in, it does not work. Here's what I have so far :
The filter from the macro :
Range("A:I").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("J1:J2"), Unique:=False
The way I work out the date 3 months from now (in cell K2) :
=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))
Cell J1 has the relevent Column Header
Cell J2 has the following formula :
=" < K2 " (there are no spaces I had to put them in so the post appeared correctly!)
I have a 6000+ row database with 5 columns across. The 5 columns have the following headings: Hair Color, Eye Color, Age, Location, and Salary.
I have created macros that will filter each column based on criteria - for example:
[Code].....
As you can see, at cell J10 and K10, I list the filter that is used for a particular search. Some searches, will only have 2 search criteria, some may have all 5, etc.
On a separate worksheet (in the workbook), I have a "Report Tab", my question (after a long build up) is how do I dynamically change the columns and values on the "Report" tab depending on the search that I perform? For example, if I did a two variable filter (Hair and Salary), those would be the only two columns on the report - if I did all five variables for the search, all columns would be on the report, and so on.
I need a function/macro that will find all rows that have a specified value in column A and extract selected columns to a new spreadsheet. More, I need it to do it for every value in column A.
I would also like it to skip creation of new worksheet if value in selected row and column is null.*
I've been trying to combat this problem with advanced filters, which helped, but due to size of the data and range of values in column A it takes an entire day to process manually. Because the data is exported to another program after it's processed, it can't stay in the same sheet, also, linking back to the original sheet doesn't work because the data changes all the time.
(Auto)Filter or a listbox somehow functioning like it?
Hi Excel guru's, i've got the following question:
Can I fine-tune the AutoFilter function so it filters more flexible? ....
attached is a spreadsheet effectively replicating a criteria box I’m using to do an advanced filter on a large amount of data (5000+ lines). The criteria can be anywhere from 1 to 7 different lines. What I’m trying to achieve is code that will look at the specified criteria box, determine how many rows of criteria actually exist, and then use that code to perform the advanced filter function on the data.
Below is the code I’ve put together so far. The problem is that this code can only determine that there are 7 total rows of criteria to use, and not the exact number of rows of criteria. For instance, say I only wanted to use 2 rows of criteria, I’m hoping the macro would only use those 2 rows instead of picking up all 7 rows.
See criteria box on spreadsheet for example. In this particular example, I’m wanting the macro to only use B49:I51 as criteria. The ideal solution would be for the macro to look at the criteria box and determine the last row used that is not filled with “1”s. Does anybody have any thoughts on ways to tweak my code to get it to achieve this?
Sub RunDynamicSelection_Click()
Dim wsSheetDS As Worksheet
Dim wsSheetRS
Set wsSheetDS = Worksheets("DataSheet")
Set wsSheetRS = Worksheets("ReportSelection")
With wsSheetDS
.AutoFilterMode = False
With wsSheetDS. Range(("A4:N4"), wsSheetDS.UsedRange.Rows(Worksheets("DataSheet") _
.UsedRange.Rows.Count)).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _....................
If I am using the "filter" function in Excel and some of the values in my list change such that the filtering criteria requires certain rows to be add/removed. Is there a way to have this filter automatically update as opposed to hitting the "reapply" button each time?
View 1 Replies View RelatedI have a spread sheet that has three sheets, PROJECTS - TIMES - LISTS Time sheet.xlsx PROJECTS contains a list of 'In Progress' and 'completed' projects for work, with relevant other data, I.e. PROJECT No. / STATUS etc This will grow to 1,000's of rows TIMES will contain the amount of time a person spends on a PROJECT on a daily basis. This will grow to 10,000's of rows LISTS contain basic validation list for the first 2 sheets.
Part A
I would like to have a validation drop down list for the PROJECT NO column in the TIMES sheet that only lists the PROJECT NO from the PROJECTS sheet where STATUS <> COMPLETED. I.e. I only want to display the PROJECT NO's for current projects.
Part B
Even though I only want the PROJECT NO used, can the drop down list also contain other column's data for reference information only before one is selected.
Im sorting a dynamic range as mentioned in this Sorting a Named Range. My range is called drWarningTypes and is defined as:
=OFFSET(DataSource!$A$2,0,0, COUNTA(DataSource!$A:$A)-1,1)
When there is only one cell in the range, then running the following sort function includes A1 also in the search (and also adjoining columns).....
I am looking for a Macro that will allow me to update the range of a Defined name range such as every time I add a name into the list, the only thing I will be doing is passing the client name and the list of client will be automatically updated and my drop down ListBox will show the new client name, which can be selected by the user.
View 14 Replies View RelatedI have a column of data that keeps getting new information in it. what i need is a named range that i can use for a chart, first point in the named range will be the first value in the column, second point will be avg. of point 1 and 2, then 3rd point will be avg. of 1,2,3 etc.
View 1 Replies View RelatedI am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put:
=OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1))
I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.
My searches have not produced anything that I could apply to this situation.
I'm trying to write VBA that would:
1. Search a Workbook for Dynamic Ranges.
2. When a Dynamic Range is found the code would:
A. Determine the current coordinates for the range.
B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name $Column$Row:$Column$Row"
3. Save Changes.
4. Close File.
My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.
I have a range of data that will grow as the days of the month pass but I need to run calculations on this range. The data is 13 columns wide but the number of rows will increase daily. I remember using a formula in the Define Range that would automatically take into account new additions using the OFFSET function but cannot for the life of me think how exactly to do it.
View 4 Replies View Related