Number Of Rows In A Range After Filter
Apr 12, 2007
I have applied a filter to my worksheet and would like to know if there is a way to get the number of rows included in the resulting filtered data.
Worksheets("All Work").Select
iCount = GetEnd(Worksheets("All Work"))
Set Cell1 = Cells(2, 1)
Set Cell2 = Cells(iCount, 6)
Worksheets("All Work").Range(Cell1, Cell2).AutoFilter Field:=7, Criteria1:="Unassigned"
Now that I have it filtered I need to know how many rows are there with data. I'll then set up a loop to read the data from each row. I can't use my getend function because it will still end up with 93 rows and the number of unassigned is about 23.
View 7 Replies
ADVERTISEMENT
Jun 19, 2014
I'm trying to build a report whereby i collate the first punch in and the last punch out from a set of data.
This is a sample data:
02-May-1409:00
02-May-1419:14
05-May-1409:31
05-May-1419:38
06-May-1409:31
06-May-1412:56
06-May-1413:10
06-May-1420:11
07-May-1408:36
07-May-1412:45
08-May-1408:45
08-May-1412:35
08-May-1413:23
08-May-1419:54
My result should be the following:
02-May-1409:00
02-May-1419:14
05-May-1409:31
05-May-1419:38
06-May-1409:31
***these cells should be filtered out
06-May-1420:11
07-May-1408:36
07-May-1412:45
08-May-1408:45
***these cells should be filtered out
08-May-1419:54
View 10 Replies
View Related
Dec 6, 2013
I would like to only view (or otherwise mark) the highest value cells (in column g) for each category (column c),
View 3 Replies
View Related
Jul 25, 2009
I m trying to use an Autofilter to filter my cells with a Number Filter of is greater of equal to 4 and is less than or equal to 5.
But as you can see I would like to customise is using a range of 2 values which i have specified in Cell P1 and Q1.
I manage to figure out how to reference to this cell, but Im not sure how can i put my ">=" and "<=" operators into my code so i can get it to work exactly how i want as shown in Code 1.
Code 1
Selection.AutoFilter
ActiveSheet.Range("$A$1:$K$118").AutoFilter Field:=6, Criteria1:=">=4", _
Operator:=xlAnd, Criteria2:="<=5"
Code 2
Selection.AutoFilter
ActiveSheet.Range("$A$1:$K$118").AutoFilter Field:=6, Criteria1:=Range("P1").Value, _Operator:=xlAnd, Criteria2:=Range("Q1").Value
View 2 Replies
View Related
Feb 10, 2008
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 Related
May 1, 2014
I 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...
View 4 Replies
View Related
Dec 17, 2012
I have understood we use row property or method to count number of rows in a range and when I write a code as below, I expected it would give output "4" because 4 rows are selected but instead it gives only "1" as output so I'm confused about the row property.
Code:
L = Range("A1:A4").rowMsgBox L
View 5 Replies
View Related
Oct 16, 2008
I need to create a variable for the number of rows in a copied range.
The user will copy a range from an external workbook could be any number of rows.
On the target workbook I will be using a User defined Menu button to fire the paste.
I need to know how to count the number of rows that are in the the range that has been copied.
View 9 Replies
View Related
Nov 26, 2008
I would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.
View 3 Replies
View Related
Aug 25, 2014
create a function that counts only the number of rows in a range which contain less than 5 instances of the string "VAC". So if they have less than 5 cells in the row that contain "VAC" count + 1.
View 8 Replies
View Related
Oct 2, 2006
I can't seem to make SumIF or Vlookup do what I want here.
I have a table like that below. I also have a cell on the same sheet called CurrentPeriod in which a user can enter a period number corresponding with one of the values in the first column.
If someone enters 3 in "CurrentPeriod" I want to sum the first three values in the "Actual" column and then divide the result by the sum of the first three values in the "Target" column (effectively giving a percentage of target at the end of period 3)
Period Target Actual %Target
1 74 68 91.9%
2 81 71 87.7%
3 76 87 114.5%
4 76 68 89.5%
5 71 89 125.4%
6 69 81 117.4%
View 3 Replies
View Related
Mar 4, 2014
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23
Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
Attachment 301726
View 4 Replies
View Related
Apr 25, 2014
Is there possible filter a partial number in column?
i have many values in column, i want enter partial value in Texbox (ActiveX).
if i enter in textbox 6840.
Result
64801000
64802000
64803000
##############
if i enter in textbox 5480.
Result
54801002
54802002
54801000
54802000
54801001
54802001
54801005
###################
View 6 Replies
View Related
Jan 13, 2009
I have to find out the top first number aftet filtering the data based on particular column. ie each time when i am taking each filter value the row will start from different number.
so how to find the top row number after filter? U got what i mean?
View 9 Replies
View Related
Oct 14, 2013
I need to filter data between two rows.
First row of data being a category, say 1-5. The second row is data I want the AVERAGE for only if the category is equal to 2 in the column. Looking for formula that uses 'Array of row 1' if = 2 to average of Array row 2???
1
4
3
2
1
5
4
1
2
2
[Code]...
So, filtering to only find the average of 300,500,800 = 533.33.
View 7 Replies
View Related
Jan 15, 2014
This has got to be simpler than I'm making it out to be, but I'm stuck. I have a column that is created by a formula that counts the number of times a name appears in a list and prepends the number to the beginning of the name. The result is something like:
A1:
1JohnSmith021488
2JohnSmith021488
1JaneDoe102187
2JaneDoe102187
3JaneDoe102187
4JaneDoe102187
5JaneDoe102187
6JaneDoe102187
7JaneDoe102187
8JaneDoe102187
9JaneDoe102187
10JaneDoe102187
11JaneDoe102187
I need a filter that results in a list of names preceded by "1" (but not 10, 11, 12, etc.) The correctly filtered result of the above list would be:
DESIRED:
1JohnSmith021488
1JaneDoe102187
My autofilter searches for data that begin with "1", so instead of the above result, I get:
UNDESIRED:
1JohnSmith021488
1JaneDoe102187
10JaneDoe102187
11JaneDoe102187
There's got to be an easy way to do this, but I can't figure how to account for the variable number of digits (1 or 2 digits) at the beginning. It would probably work to set up an advanced filter for cells that begin with a number less than 2, but I don't know how to do that.
View 3 Replies
View Related
Mar 29, 2014
I have a huge data file with dozens of columns and hundreds of rows. To check which values are negative I use conditional formatting. Now because the number of columns are so many I am unable to filter only highlighted cells at once. I have to go through each row and scan for highlighted cells which is very time-consuming. How in hundreds of rows I can filter out which row has any highlighted cells. Or may be some other easier way to pick out the negatives only.
View 6 Replies
View Related
Jun 3, 2007
I have two spreadsheets. Spreadsheet B reads from the master. No entries are made on spreadsheet B, it is visual only. Changes are made only on the master. I want to create a Macro where all blank lines are filtered out automatically on spreadsheet B, in effect only lines with values are displayed.
Once a new task is created, the details are typed into the master spreadsheet and spreadsheet B, located in another building, is automatically updated to display the details. Once the task is completed which can be seconds, minutes or hours later, an "x" is placed in column A of the master. I have formulated spreadsheet B is "" all rows where "x" is listed in the master. Towards the end of the day however, I can have 50 visable entries on spreadsheet B spread over 500 rows.
I want spreadsheet B to display only active tasks from the top of the page down. I currently have those that read the spreadsheet filter out blank rows. Because tasks can be updated at a rate of 10 - 15 per minute with new tasks and the completion of old, I want to eliminate the need for our employees to have to filter themselves.
View 4 Replies
View Related
Nov 20, 2012
I have a large file with cells filled with text (converted from a database, imported into Excel). The text is in rows of column A and always contains a 16 digit number, which is what I need. I am able to clean up this text and show only this number, but then Excel shows the last digit as a '0'.
An example of the text I need to filter the 16-digit number from: GIRO 6838657 K MAHMODBETALINGSKENM. 7062542158461684 STORNOADMINISTRATIEVE REDEN 1 ZIE DE TOEGEZONDEN KENNISGEVINGVAN VERHAAL OF CJIC.NL/VZD
I've altered some of the information in here so this is fake. The text is messy but all I need is the number.
This is the macro I use to clean up the text and leave only the 16 digit number:
Sub CleanUp
Dim e As Variant
With CreateObject("VBScript.RegExp")
[Code].....
It sort of works, but when I run it, I get this number as a result: 7062542158461680 (last digit is changed into a zero).
I've been trying to insert this line into the macro but it doesn't work: Columns("A:A").NumberFormat = "@"
How do I change the outcome of this macro into the actual number?
If this is impossible to do, I can also manage if there is a way to filter only the last 9 digits from the 16 digit number. I can work with that, too.
I use Excel XP (2002) at work.
View 2 Replies
View Related
Jul 13, 2014
I have some data captured in a spreadsheet. The data was recorded every 15 seconds in this format 00:00:15. The total time of recording was 24 hours. I need to filter this data to every 2 minutes and capture it into another sheet.
I have attached an example of the data captured.
View 2 Replies
View Related
Mar 6, 2007
I need to protect and unprotect rows of data on my "Near Miss Data" sheet, periodically.
When data is added via a Userform, one of the columns (column N) will have either a YES or NO in it. Those rows which have a NO in column N, needs to be unlocked. Those which have a YES, need to be locked. BTW ~ The yes' and nos refer to whether it's completed or not
I am planning to CALL the code below to protect/unprotect as needed, but i am struggling with the code to use so i can unlock those rows with NO in column N
Sub Unprotect()
' Simply UNPROTECTS the sheet
Sheets("Near Miss Data").Select
ActiveSheet.Unprotect Password:="lock"
MsgBox "unprotected"
End Sub
Sub Protect()
' Simply PROTECTS the sheet
Sheets("Near Miss Data").Select
ActiveSheet.Protect Password:="lock", DrawingObjects:=True, Contents:=True, Scenarios:=True
MsgBox "protected"
End Sub
View 9 Replies
View Related
Oct 8, 2007
I am using the following code to filter for data I do not want and then to delete those rows and show remaining data. It works fine except when the filter comes up empty and there is no unwanted data to delete.how to improve this code to accomodate this situation?
Selection.AutoFilter Field:=4, Criteria1:=">" & dweekend, Operator:=xlAnd
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete
ActiveSheet.ShowAllData
Thank you for reading
View 9 Replies
View Related
Aug 6, 2008
I'm using a fairly large spreadsheet to put all the cost and benefits of a large area development phased over different years and then calculate the NPV of the total project. The costs/benefits are on separate sheets and are divided into categories with headers. Every category has a summation row as last row.
Because the project is divided into subprojects I created a column which has a dropdown box (using the Validate function) in which I can attribute that cost/benefit to a certain subproject. Using IF functions and another dropdownbox on my Overview page I can get a insight in the total cost and benefits of the subproject I select in the dropdown box.
So far so good of course, but what I really want is not only to be able to get the Overview page per subproject, but also the Cost and Benefits pages. I was thinking about putting a filter on the column which has the dropdown boxes with the subproject number in them, but when I select a number I want all the headers and "summation rows" of all the categories to freeze/stay in sight, because otherwise the output of the filter is useless (for printing and evaluating) ...
View 9 Replies
View Related
Nov 10, 2008
I have a parts index containing 500+ rows of data, I also have multiple columns in which I can sort the data.
I would like to have a button fixed in cell C4, D4, E4, etc... row 4 is the header, that when clicked, will filter all rows that have a selection (non-blanks) for only that column. So If I have data in various rows & column (C5:F500) that when I click on the button in cell D4, only the data listed in the various rows of D5:D500 are shown and columns C, E & F are hidden. Clicking cell D4 will show all data.
I can create the button so that it is sized specific to the cell and follows the cell sizing and I can create a button to filter only columns D or E or F, etc... but that means that each button is a separate VBA, macro.
A while ago I found a VBA code that allowed me to sort rows Asending/Desending depending on which button in which column was clicked... See inset: and this allowed me to add columns without changing the code. So I think If I can modify this code this will work great for me..
Sub SortTable()
'code written by Dave Peterson 2005-10-22
'2006-08-06 updated to accommodate hidden or filtered rows
Dim myTable As Range
Dim myColToSort As Long
Dim curWks As Worksheet
Dim mySortOrder As Long
Dim FirstRow As Long
Dim TopRow As Long
Dim LastRow As Long
Dim iCol As Integer
Dim strCol As String
Dim rng As Range
Dim rngF As Range
View 9 Replies
View Related
May 5, 2006
I have written some code that will allow me to filter and delete any rows where there is a value in a column ( column J in this instance ) more than 2500. The problem is that it only wolrs when i press F8 and step through the code, and not when i press the commnd button and run it.
Sheets("CAIZOLY9").Select
Range("A1").Select
Do Until ActiveCell = "Payment Amount"
ActiveCell.Offset(0, 1).Select
ActiveCell.Select
Loop
Do Until ActiveCell.Value = ""
ActiveCell.Offset(1, 0).Select
If ActiveCell > 2499.99 Then
Selection.EntireRow.Delete
Else
End If
Loop
Range("A1").Select
End Sub
View 7 Replies
View Related
Dec 6, 2006
I need to update a sheet called Database by filtering the word Served in column F (6th field) of another sheet called Detention Register. After the 'Served' rows have been filtered today's date needs to be pasted into column E for all of these rows.
The filtered entries from A:F only then need to be cut & pasted into the next empty A column cell in the Database sheet. Finally, another macro called Update Database needs to be called.
View 9 Replies
View Related
Jan 14, 2009
I have the need to filter out letters put in after a number in a time card spreadsheet. I'm not sure that using a select case is the right approach. I need to allow the user to put in a number and a letter signifying what type of time it is. Each cell equals a date on a calendar. For example if the user puts in 8s then the code will add 8 hours to the total sick time, strip out the s and just leave 8 in the cell. The problem is that I need to deal with all of the other letters/symbols that they can enter. From what I know of VBA which isn't much a Select Case seems to be way to go without using a bunch of nested If statements. Here is what I would like to do but this doesn't work. This is a short example of what I have tried as far as Select Case goes.
View 6 Replies
View Related
Feb 5, 2009
As I have not tried to do this yet this is a hypothetical scenario. Imagine that a column, when filtered, gives cell values such as 3,6,9,12,18,24,36 ..and so on. Is there a way to select, say 12, and then see all the other values that are divisible into 12. e.g. all rows that have 3,6,12. Another example- select 36 and see 3,6,9,12,18 and 36?
On the attached example sheet, column 'O' will be filtered.
View 4 Replies
View Related
Oct 25, 2012
I'm trying to filter and delete unwanted rows as I need row where dates is within a specified range.
How can I do this using AutoFilter? or are there any other alternatives?
View 2 Replies
View Related
Jan 5, 2013
I have a database (ATTACHED) which contain name, roll number, courses taken etc. I want to create different file for different courses showing the roll number and name of the student who have written their name as REGISTER in the course.
View 3 Replies
View Related
