If I create a pivot table, I'm able to click the arrow to the right on my row header, unselect "Show All" and then manually check boxes next to the rows I want to show. The pivot table then shows only the rows I'm interested in.
I can't figure out how to do this with filtering outside of the pivot table. I can select AutoFilter and then select "All", "Top 10" or individual rows, but I don't have the option to use a checkbox style selection to choose multiple, but not all, rows.
How can I set this up to have checkboxes associated with the AutoFilter dropdown so I can pick and choose which rows show?
I have developed a user form in VBA which is used for entry into an excel worksheet. From this data, I need to do the following:
1) Create 2 output files for Job data entitled "J" and another file entitled "S". The "J" and "S" is captured in a field in the data. I need to hit a button that allows these 2 output files to be produced, so I need to 'loop' through rows of the data to do this/
2) In addition to 1), the user needs to be able to 'select' the date range they would like to see 'J' and 'S' data over. This will be based on a field called "Startdate'. How could I do this with the button above-can I parse an input value to a form and select the 'J' and 'S' files on this.
Can a list box (or other type) be created that would come up and allow a selection of multiple items. I do not need the items to be viewable beyond the selection, only to be ablle to sum different segments of the rows that were selected.
As example: Upon clicking in cell B 141 of the sheet a box would come up that would include the descriptions (in column C) of all items that are between rows 1 and 140 that have identifeid by a "y" in Column B. Then you could select as many as you desired and the sum of the quantity (column d) of the selected items would then appear in D141.
If I put data into one of two cells I want it so that the other cell cannot be selected. I can't say I've struggled with this for days as I really don't know where to start
I am trying to use the COUNTIF function but over a selectable range. in other words instead of a fixed range like =COUNTIF(A1:A11,"=yes") I want to be able to choose the range Axx:Axx selected by drop down menus.
I am attempting to create a Menu (food) in Excel 2007. I have a list of foods that I would like to select once one is selected the next list would be for the next item and so on. I know how to create a drop down list but once I select an Item I would like the next list to narrow down the field (or choices). I am not sure I explained this well enough so here is another way to put it.
First Column would be a choice of meats. Beef, Chicken, Pork, etc. If you select say Beef the next column would be a veggie, Carrots, Peas, Green beans. But with Beef you cannot select Carrots. and so on.
I would just like to know if there is a way to add items to a drop down list that cant actually be selected.
Asking because I have a table from which the dropdown list gets populated and the user then selects a particular row but would like the header fields to also be added to the list just for reference.
I have attached the spreadsheet in question for reference.
Various test items are stored in columns. (First row of each column is the description of the test item)
Problem: 1) To reflect certain testdata in a chart I want to be able to select a different column (Y values) by means of a dropdownlist. X values are just the number of items (1 to ...)
How to make the charts data source selectable?
2) It seems that the input range of a ddl should be a list of items.
In my case they are in the first row of each column so only the first item is shown.
I do data entry for a webstore and one cell is used to map out the exact product category/subcategory path. I currently have to copy the appropriate path from a long list on one page and then paste it into the cell. I have to do this a hundred times a day. It would be nice if each cell within that column can have these values stored in them so I can just click on the cell and open up a drag down box and select the needed value. Is this possible to do in Excel?
The following macro copies a range from "ThisWeek" worksheet to a selectable worksheet "Week1, Week2, Week3, etc. (determined by value in cell P13 (1,2,3,etc.)) The copy destination is currently cells F5:F36 but I want to make this range selectable also (ex. G5:G36, H5:H36, etc).
For example: If "ThisWeek" worksheet (fixed) cell $B$7 = "Adam", I want to match/lookup this name on row 4 (column F,G,H,etc) on the destination worksheet and copy the range of data to a column under the heading labeled "Adam". (No need to verify if heading exists) Hope I have explained my problem clearly.
I have a workbook that is used for storing test results that relate to people in different groups.
B4, C4, D4 are the surname, first name and group headings. This is what I want to filter by.
Column E is empty
F,G,H,I,J,K are the results.
Column L is empty
M is total of results
What I want to do is set an autofilter from B-L, only have drop downs on B,C,D. But in addition to this I want to have it that the range can dynamically change if another column of results is inserted.
To calculate my formulas in the worksheet I named/defined M4 as 'Total' so that I could then offset from this named cell to determine the range of test result columns, which works well. I just don't know how to do this in vba.
I have this to just get the autofilter running:
With ActiveSheet .Unprotect ("password") .AutoFilterMode = False .Range("B4:L4").AutoFilter
.Protect ("password") End With
I need the range to be B4: offset of the cell 'total' 0,-1
and I need to loop through from F - offset of the cell 'total' 0,-1 and turn the dropdown off on each of these headers.
I am trying to number col A from 1-??? with auto filter on. The only way I know how to do this would be to type 1 in the top col and drag it down, however when autofilter on, this doesnt work. Is there a way to do this, with or w/o VBA?
I use Excel 2007 and need to find the best way to delete rows selected after Auto-filter. This autofilter selects multiple criteria for a particular column. The closest post to do what I am looking for is: [url]
But this doesn't really help me firstly because it pops out an input box and asks to enter 1 criteria. I have multiple criteria for each column depending on which I'd like to delete rows.
Alternately, I have been trying this code belwow but its too time consuming for my 50,000 rows.
Sub CodeCleanup() Dim r As Long r = 65536 For I = 1 To r If Cells(I, 30).Value = "R" Then Rows(I).Delete End If Next I End Sub
I have a spreadsheet that is split in two parts , one with headers in row 16 with data flowing down to row 190. In row 192 I have another set of headers with data flowing down from that to row 300.
I have a userform (roughly at cell B13 in the attached) that filters the first block of data into either Company, Syndicate, EU Corporate or ALL. (ignore the other filters) which feature in range B18:B190
What I want it to do, is that when one of the three options is selected, ie Company, is that all rows from 193 down are hidden other than those that are also Company (in the test case there is just one row). The same is true for when Syndicate or EU Corporate are selected in the userform, and if the ALL is selected then none are hidden.
I have a Userform that it makes possible to step through the the spreadsheet. It works with Previous and Next buttons. The Userform opens with a doubleclick. This works fine, but when I use an autofilter and the criteria reduces the number of rows, the Userform shows the hidden rows as well as the filtered rows. How could it be made that it only steps through the visible rows?
I have a Userform that allows you to step through the the spread sheet that works with Previous and Next buttons. This works fine.
When I use an autofilter where the criteria reduces the number of rows the Userform shows the hidden rows as well as the filtered rows. how can I make it just show the visible rows.
I have created a file where I use the Subtotal function. Once I collapse the information to only give me the Total, I would like to copy the Total rows into another worksheet. However, when I do this I get blank lines in between. I am trying to find a way to delete the blank rows in between the Total rows I need. Is there a way to do this with the auto filter function?
I have a spreadsheet with over 20000 rows of data and have used autofilter to find the information I want to check. Once I have these rows I want to check whether the cells in Column AE are higher/lower than the number (in this case '3'). Once it finds a cell that meets this condition then it copies and pastes the whole row so that I can review the entry.
The problem I'm having is that it doesn't work all the time. I've tested it a number of times in break mode & played it throughout and it works as expected, whereas other times it picks up the top row (header) and duplicates it or it picks up hidden rows and pastes them in sheet 2 if the condition is met. I want it to just pick up rows if they are visible (using the filter) if the condition is met.
Here's what I have so far:
Code:
Sub Lvl3v2() 'Level 3 Dim i As Integer Dim r As Range Dim c As Range
i = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
The master sheet contains company information; each row represents a (company)record.
The first column contains the country code where the company resides, e.g. DE.
I dug up this piece of For i = 12 To 1 Step -1 Set rngAutoFilter = Cells(1, i) rngAutoFilter.AutoFilter Field:=1, Criteria1:="DE" Range(rngAutoFilter, rngAutoFilter.End(xlDown)).SpecialCells(xlCellTypeVisible).Copy _ Destination:=Worksheets("Sheet2").Cells(1, i) rngAutoFilter.AutoFilter Next i
I found this code to work allright but, it only copies the singel cells containing "DE" and the topfirst row. At least that is what is the visible result.
How do I modify this code to copy EACH complete row containing "DE" in the first column?
I'm wondering if anyone has a answer to the problem of deleting all the rows that are hidden by an autofilter. We currently have a spreadsheet used within the office that catalogues all applications received, and we want to select all the applications that are relevant to a certain month with the autofilter and use a macro to delete those that are irrelevant, before emailing the spreadsheet to a client. Manually deleting all irrelevant rows would be time-consuming.
The current code I have is:
With Worksheets("Sheet1") If .AutoFilterMode Then With .AutoFilter.Filters(1) If .On Then Else: Rows.Delete End If End With Else: End If End With
The theory is that the code first selects the relevant worksheet, determines whether autofilters are on, selects data that is being covered by the autofilter, indentifies those that is being displayed and does nothing, and identifies those which are not displayed and deletes them - in theory!
Is there a simple way to count unhidden rows? I saw Counting number of unhidden rows =SUBTOTAL(103,D11:D7180) and, frankly, I'm wondering if there is a way to do it without a formula. I don't need the count in a cell, per se, just a quick count of the unhidden rows of a worksheet for usage elsewhere.
I am trying to display a certain number of visible rows, using AutoFilter. My idea is to use a helper column that will 'number' each visible row, but I'm not quite sure what functions to use for this.
I have a spreadsheet in Excel 7 for a lifelong cashflow model. The main worksheet is called 'Projection' with ages in rows & income (from a variety of sources) and expenditure (of various kinds) in columns fed with data from the Input sheet.
On the 'Input' sheet I have a button called Format Graphs which controls the following macro:
Can anyone explain clearly (using layman terms) about how to go about randomly choosing specific number of rows from a set of autofiltered records?
For e.g., If there are 1000 rows of data in a worksheet and after applying autofilter to certain column(s) [one or more] say i get some 75 rows. Then how can x no. of rows be chosen where x can be input by the user or calculated based on a certain percentage? Say if out of these 75 filtered rows, 8 rows have to be chosen randomly - where 8 can be input by the user or also be calculated as 10% of 75.
Also, is it possible to choose these x records from the filtered set of rows without actually copying them to another sheet?
In column C27 and down, the user can input a date. In column M27 down, the user chooses pass or fail.
N8, contains a date chosen by user as the "From" date and P8 the "to" date.
Cell o11 is "Passed" and cell 012 is "failed"
The user can choose a date range and input the from and to date in N8 and P8, this will count the number of pass and fails and input the number in O11 and o12.
The 1st sub populates a series of sheets with data present in a master sheet called Overall. I'm using ADO because it's fast...
The 2nd sub loops through a range of criteria (also happen to be sheet names) and autofilters a range using the array items as the criteria. The filtered rows are then deleted (excluding headers).
The 3rd sub simply runs 1 and 2.
The 1st sub completes almost instantly. The 2nd sub is also darn quick if I run it BEFORE the 1st (which is not as intended).
I have tried compliling both into a single sub and still the whole autofilter bit takes ages.
I have a workbook that has several sheets in it with various rows of data on each sheet. I would like to have a macro to loop through all the sheets in the workbook and copy and paste the rows into a new summary sheet. The rows to be copied should only have data in Column A, in other words if Column A of a row is blank I want it skipped. Also Row 1 of every sheet contains my headers, and I am only using columns A through M. It needs to be able to copy rows even when autofilter is in use.
I have several sheets with about 250,000 rows per sheet.
But, even I sort by Column A, there are STILL hundreds or rows that are total blank interspersed down the page . . . I can't autofilter for blanks because there is too much data . .
