Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Exclude Certain Rows

I would like to exclude certain rows from

For r = 1 to 1000

does anyone know how i might go about this?

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Exclude Rows Containing ZEROS In Column L
I analyze logged data that often contain ZEROS in column L, always starting on row 35. That's bad data. The first row with good data contains the number 700 in column L, but this row number is unpredictable.

How to make a VBA code to exclude the rows that contain the number 0 in column L, searching between cell L35 (including) all the way to the first row containing the number 700?

I need to limit the range to be scanned for ZEROS because sometimes there are valid ZEROS in column L, but those would be in rows below the rows contain 700 in column L.

View Replies!   View Related
Exclude Rows Based On Condition In 1 Column
I have a worksheet that I want to export to a csv file. However, there are multiple rows that I want excluded in the export. These lines are recognized by the word "No" in column E. Is this possible?

View Replies!   View Related
Delete Visible Rows After Auto-Filter & Exclude Headings
I am asking if it is possible to delete filtered rows? With code.
What I mean is after setting a filter, then deleting all shown rows except row 1, (Titles)
I did a search but nobody seams to have asked this yet, so recorded it, but that did not seam to enlighten me much either.
Or is this the wrong approach, should I delete using a loop, using the filtered criteria as to say delete row, or move on to the next row?, this would be far more time consuming as when all can be deleted at once if possible of corse.

View Replies!   View Related
Formula To Exclude #div/0! From A Max Value
is there a formula to exclude #div/0! from a max value. say you have a column with a few #div/0! is there any way for the max not to be #div/0!

View Replies!   View Related
Exclude Headings
I use currentRegion to add the item to the combobox, but i don't know how to add without including the first cell in the range,as usually, it is the field name.

View Replies!   View Related
Sum Column Exclude Duplicates
I've got data for different regions of our company. For this example, what I need to do is count the number of jobs in the "South Atlantic" region (Column T) that are NOT "Wal-Mart Stores, Inc" jobs. In order to do this, I put a "1" in Column F so that the formula would have something to sum:

=SUMPRODUCT(--(T2:T1079="SOUTH ATLANTIC"),--(H2:H1079<>"WAL-MART STORES, INC"),(F2:F1079))

The problem is that some job numbers, which are located in Column G, are duplicated. As a result, this number is higher than it should be.

So what I need to do is only count each job number once. How do I do that? Is there a way to modify the existing formula? Or do I need something completely different?

View Replies!   View Related
Exclude Empty Cells
I have this calculation which works fine if all cells have a value. But it will happen that cells in the range are empty.

=SUMPRODUCT(--(1*LEFT('Courses input'!C4:D6,1)-C3<=0), --(1*MID('Courses input'!C4:D6,3,1)>=2))/E3

What can i do to exclude empty cells in the range from calculation?

View Replies!   View Related
Inputbox To Exclude Data
I need to have an inputbox for users to enter multiple store numbers. What I need to do is actually filter out the store numbers that was entered into the inputbox
my code so far is:

'Inputbox to filter out new stores
Newstore = InputBox("Enter New Store Numbers seperated by a space", "Enter New Stores", "119 120")
x = Split(Newstore, " ")
For i = 0 To UBound(x)

Range("A1").CurrentRegion.AutoFilter Field:=2, Criteria1:=Array(x) _
, Operator:=xlFilterValues
Next i

View Replies!   View Related
Exclude The ' # From And Advanced Calculation
=IF($M272="DUPLICATES";SUMIF($D:$D;$D272;F:F);"") and


it looks up duplicate values in D:D

well It considers #s to be duplicate too and then makes the summation

How can I exclude symbols like that # from the sum???

View Replies!   View Related
Exclude Non Mature Data
I have data that I calculate by month and at the bottom I calculate a weighted average. However, I want to exclude the last two months (data points) because they are not mature.

So every month when I update the spreadsheet, it would be the two newest data points to exclude.

View Replies!   View Related
Conditional Formatting Exclude 0?
I want to conditionally format the highest and lowest number in a range of numbers in a single column (Column C). The numbers in Column C are created by a formula that simply adds the data in the two cells in the same row of Columns A & B.

Numbers are entered into columns A & B daily. The column C formula has been filled into enough rows to complete the entire year. Since future days do not have numbers filled in for Columns A & B, Column C for those days results in a sum of 0.

I have set up conditional formatting for Column C so the cell of the highest number in filled in green. I want to conditionally format Column C so the cell with the lowest number is filled red, but it automatically fills all the cells for future days in Column C in red because the sum displayed there is 0.

Is there a way to exclude the 0 sum cells? The date is filled in as simple text, rather than formatted as a day. Is that the answer?

View Replies!   View Related
BeforeDoubleClick Exclude Range
I have the following code which toggles the value of cells by double clicking on them. I need to exclude certain ranges of cells though. I tried password protecting the sheet, but that haults the code. Is there a way to exclude the cells or exit sub if the targeted cell was in the range of Rows("10:10") or Columns("A:B") for example?

View Replies!   View Related
Ask Macro To Exclude Sheet By Name?
I have a workbook event where the macro inserts the username and date into the comments of the cell that has been edited.. However I would like it to exclude the 'Index' worksheet that has its own macro for indexing all the sheets in the workbook. As every time the sheet is clicked and the macro updates the sheet names all the cells comment.

View Replies!   View Related
How To Exclude Certain Data Points
I'm running a query of 30 and 60 days worth of data but I don't want certain dates to be included in the queries. How do I exclude certain dates and at the same time pull in day 31 to include this in my 30 day high or average etc.?

For example:

1/19/2007 4.7
1/18/2007 4.5
1/17/2007 3.8
1/16/2007 4.9
1/15/2007 3.75
1/12/2007 3.75

I want to exclude 1/15 because it is a holiday and it just repeats the data from 1/12. So I want it to pull in the data for 1/12 if I were doing a 5 day query. As I said before I need to do a MAX, MIN, AVERAGE and STDEV of 30 and 60 days but my formulas are skewed because of the holidays.

View Replies!   View Related
Macro To Exclude 1 Sheet In WKB
I have a macro code (attached below) which i am using to do some conditional formatting for all my worksheets in a workbook. I would like this macro to skip 1st 2 sheets in this workbook.

Can some1 kindly edit my following code so that macro does not runs on 1st 2 sheets namely "Sheet1" & "Sheet2" and continue running for all remaining.

View Replies!   View Related
Exclude Zeros From Array Formula
I have 16 columns in a single row that I am trying to apply to an array formula. That works, but I need to exclude all cells with the number "0" from the array range. I think I can do this myself in VBA but I don't want to run a macro every time something changes. Is it possible to do this in excel?


It is possible that A1:A4 is a non-zero range and A12:16 may be a non-zero range. (Zeros will always be grouped in 4s)

View Replies!   View Related
Time Calculation That Exclude Blanks
All I would like is to put the result of this Excel formula in the D column:


starting from row 10, provided that both B and C for the row exist (are nonblanks).

View Replies!   View Related
Max Number Of A Spreadsheet But Exclude 3 Numbers
i have a worksheet filled with numbers in each cell (the worksheet might have a total of 150 rows and 150 columns of numbers).

the numbers typically range from 1-3 or 1-9 or 1-150.

however, there are also multiple 997, 998, and 999 numbers in the cells.

i need to identify the highest number in the worksheet that is not 997, 998, or 999.

View Replies!   View Related
Exclude Outliers In Average Calculation
Is there a good way of excluding an outlier in an average calculation. In the example below will I exclude 1000 from the average-calculation.

The way to decide excluding-values can either be a percent based on the range or everything that is a higher than a user defined value. It can also be more than one outlier.
A user defined function is OK with me, if it is impossible to use the built-in functions.

View Replies!   View Related
Exclude Weekends In Date Counting
I have been trying to count dates in my spreadsheet. The dates are supposed to go back in time (what they do). But I want them to jump over the weekends' dates instead of landing on them.

What I have is Start Date, Req'd Qty, Days it takes to make, Date it is required to start production (which I have a problem with).

1 26-Nov [Thursday]
2 1500
3 5
4 'Problem' I used for this cell that formula: =if(A2>0,A1-A3,"") , as I don't want it to show anything if there is no requirements.

It comes back with 21-Nov which is Saturday when I want it to come back with 19-Nov (Thursday) as we only have 5 working days in a week.

View Replies!   View Related
Sumproduct Formula - How Do I Exclude Values
I've used the sumproduct formula very sucessfully in a workbook. The workbook is used to monitor discrepancies routed to other departments. Column U has the status of the discrepancy (Open, Closed, Cancelled etc). The below formula returns the number of discrepancies raised to a particular department. Now I need to tweak the formula to exclude values "Cancelled" found in range $U$119:$U:417.


View Replies!   View Related
Exclude Weekends And Holidays From A Formula.
I have been asked to create a worksheet to calculate the percentage of the utilisation of a number of loan cars per month. Each of the cars may be used on each and every day of the week but the percentage utilisation is to be calculated on working days only. However it is essential to record the name of the driver of the car at weekends and holidays. Column A is populated with the dates of the month. Colum B is populated with the relative days of the week. In column C and subsequent columns I record the name of the driver of a particular car registration. Is there any way that I can count the number of drivers of each car on working days only and not weekends or bank holidays?

View Replies!   View Related
Exclude Sheets From Hyperlink Index
I am currently using the VB code from an older post in here.
Index Worksheet Plus

The problem I got is that i do NOT want all sheets to pop up in the index page. I got several sheets that I do want to exclude from the index listing. The sheets I want to exclude is not hidden.

Is there any way to exclude sheets from the index ?

View Replies!   View Related
Exclude Minimum Two Values In Average
I have looked on here and I can find the minimum value in my range but what I am trying to do is find the minumum two values. Or rather, i want to find the average of the remaining values.

If the lowest value happens THREE OR MORE times, i still only want to exclude two of them. Same thing goes if the second lowest happens more than once, i only want to exclude at most two values from the avg.

currently, i have 12 values in my list.


list --> one in each cell going across

25, 50, 87, 56, 99, 80, 81, 82, 50, 100, 98, 99

i have the answer as 83.2 doing it by hand. the 25 and one of the 50s would be dropped and the rest would be averaged.

View Replies!   View Related
Exclude Sheets Based On Tab Color
Is there a way to exclude sheets from index based on tab color

View Replies!   View Related
Exclude Hidden Cells In Average
I have a large data table that has autofilters on the headings.

I also have a "Data Summary" Sheet in which I would like to get a conditional average.

Here is my *working* array formula:

=AVERAGE(IF('Data Table'!$C$10:$C$65536=A2,'Data Table'!$BP$10:$BP$65536))

The problem is, it averages all cell values (conditional upon the C column's cell being equal to A2 of course), whereas I only want to average the VISIBLE cells that match the condition.

I have tried using a little VBA with:

Function Vis(Rin As Range) As Range
'Returns the subset of Rin that is visible
Dim Cell As Range
Set Vis = Nothing
For Each Cell In Rin
If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
If Vis Is Nothing Then
Set Vis = Cell
Set Vis = Union(Vis, Cell)
End If
End If
Next Cell
End Function

and then trying the formula:
=AVERAGE(IF(Vis('Data Table'!$C$10:$C$65536)=A2,Vis('Data Table'!$BP$10:$BP$65536)))
but it does not work.

View Replies!   View Related
Exclude Blanks In Validation List
Is there any way to exclude the blank in data validation-under validation criteria-lists. an example is include.

View Replies!   View Related
Exclude Files In List From Filesearch
I am using the following to compile data into a master workbook

With Application.FileSearch
.LookIn = SrchDir
.SearchSubFolders = False
.FileType = msoFileTypeExcelWorkbooks
.LastModified = msoLastModifiedAnyTime
.Filename = "*.xls"
If .Execute > 0 Then
For Each vaFileName In .FoundFiles
' If vaFileName = Exclude Then GoTo n ' maybe something like this ?
ProcessData vaFileName
n: Next

and then processing each file, and adding it to the master. This is run every time a user opens the workbook. The number of workbooks in this folder is fairly large and more are added every day. What I would like to do is, compile a list of file names that have already been processed (I can handle that part) and then exclude them the next time the master book compiles from that folder. so if the master was run September 16th, all the data up to that point is already in the master and a list of those files is on a sheet in the book, now if the book is opened again on October 1st, it will look at the list first and only add the data from Sept. 17th through Oct 1st.

The file names I'm dealing with are named by date ie: 9-16-06.xls. The folder they're in is on a server so I can't just kill the directory after processing so that only new files are there, other people may need access to them. I think this may be handled at the "If Execute > 0" part of the code but am not adept enough to know how to do it. Possibly set "Execute" as a variant and have it cycle through the list

View Replies!   View Related
Exclude Unwanted Data After Macro Filter
I have a sheet for addresses (Column 1 = Name, Column 2 = Address line 1, Column 3 = City, Column 4 = Postcode) and I run a macro that Filters that data based on the the city to different Tabs. This works fine except that sometimes, once the data is filtered I will find one or two addresses on my new filtered sheets that I really wanted to exclude. As my main address Tab information changes regularly, Ideally, I would like to see the Item on the City filtersheet, Double click it, and this would have the effect of deleting it from the City filtersheet but also copying it to an "Exclusions" Sheet which I could use before I run my normal filter macro to filter out these bad addresses.

View Replies!   View Related
AVERAGE Calculation To Exclude Blank Cells
I have an array formula that calculates the average of numbers between two dates:

{=AVERAGE(IF('Date Range'!$B:$B>$H$4,IF('Date Range'!$B:$B<=$B$3,'Numbers to Sum'!$C:$C)))*100}

The start date is the day after the date in H4 and the end date is in cell B3. The dates are in column B and the numbers to sum associated with these dates are in column C.

Some of the cells in column C are blank and my formula is taking these blanks into account in calculating the average, while I would prefer not to count them in the calculation.

Can someone please suggest how I could amend the formula above to accommodate this?

View Replies!   View Related
Conditional Formating :: Exclude N/A Cells From Calculations
I have a single column of data that is, perhaps, 100 rows long.

I have ticks, crosses and 'N/A' in various cells within this column. There are no cells that are blank.

I want to count the number of ticks within the column and want to be able to say:

If the number of ticks is = 100% then it's green.
If the number is >75% and <100% then it's amber.
If the number <75% then it's red.

I also want to exclude the N/A cells from the % calculation.

View Replies!   View Related
Macro Sort A-Z, Exclude Some Text So It's Not Sorted
I've setup a macro button to sort some stockpile items from A-Z so entering quantity on hand can be done more easily. Each cell is a drop-down selection so at various times, there can be different stockpile items in the list, thus why, I setup a macro to quickly sort the list.

However, I've got some text (Select Stockpile Item) that I wish not to be included in the A-Z sort as this might put items starting with T or later at the end of the sort.

Sub SortMySpecialStockpileA2Z()
If MsgBox("Are you sure?", vbYesNo + vbQuestion) = vbNo Then
Exit Sub
ActiveWorkbook.Worksheets("MyStockpile").Sort.SortFields.Add Key:=Range("K4") _
, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("MyStockpile").Sort
.SetRange Range("K4:L110")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
End With
End If
End Sub

View Replies!   View Related
Formula To Add Numbers And Exclude Text Values
I need a formula that calculates the values of 5 seperate cells located in different places throughout my sheet. The value will either be numerical or display a text value (normally "RD"). If it displays text I do not want that value counted. So it literally just totals the number values.

View Replies!   View Related
Reference Previous Weekday To Exclude Holidays
I am trying to compare values from this week to last week with the exception that will overlook holidays. ie, from Monday to last Monday. But I am running into an issue if there is a holiday in the week as my formulas are looking for the value 5 days prior to the current day.

View Replies!   View Related
Print To Exclude Fill Colors On Worksheets
The bright colors work well, but when they print the sheet on a laserjet black/white printer, the colors make the data almost impossible to read.

Is there a way to allow them to print the sheets and, by default, not print the background fill colors?

View Replies!   View Related
Exclude Specific Parameters In A Column While Using SUMIFS
I have a SUMIFS function written that reads where some of my budget is coming from. I track several different departments but am only responsible for some of the departments' budgets. So i need to exclude two of the departments when the SUMIFS is reading the other parameters from my list.

From the example i attached, how would i go about writing the formula in I3 to skip the Cost if it comes from Dep 2 and Dep 4?

View Replies!   View Related
Average Dynamic Data But Exclude Zeros
I use a SQL query to export a subset of data from our server. After manipulating the received data I need to Average one column that has been sorted into a number of blocks consisting of variable numbers of rows and post the result to the ajacent cell. I have tried the Countif worksheet function but am unable to resove the syntax problem.

Sub SumBlock()
Dim First_Row As Long
Dim Last_Row As Long
Dim iTotalRows As Long
Dim iCount As Long
iTotalRows = Range("A65536").End(xlUp).Row
First_Row = 2
Do While Last_Row < iTotalRows - 1
Last_Row = ActiveSheet.Range("j" & First_Row).End(xlDown).Row
iCount = Application.WorksheetFunction.CountIf(Range("J" & First_Row), ("J " & Last_Row), ">0")
ActiveSheet.Range("K" & Last_Row + 1).Formula = "=Sum(J" & First_Row & ":J" & Last_Row & ")/iCount"
First_Row = Last_Row + 2
End Sub

View Replies!   View Related
Average Non-contiguous Cells & Exclude Zeros
I would like to average a non-continuous range of cells while also excluding all zeros.

I am averaging hours worked and the hours are found in cells:

B2, D2, F2, H2, J2, L2, N2

Some of these cells contain zeros at this time and I do not what to include the zeros in the average.

I have found formulas that would work but they are all with continuous cell ranges.

View Replies!   View Related
Exclude Blank Date Fields For Vaildation
I am using the standard validation from the tool bar. What I am trying to do is in
check the date in a cell and if the new date is Greater and or equal to another date in another cell. The problem is I am having is when the cell that I am checking for a date is Blank the validation does not work and one can put in a date. I tried unchecking the "Ingore Blank"

Spreadsheet example attached.

View Replies!   View Related
Exclude Network Path In PivotTable Based On Sharepoint
I have an excel file linked to a sharepoint 2007 network space. Everytime I open the file, my pivot tables will not refresh, because the source of the pivottable seems to update automatically to include the network drive name on them which somehow causes a problem. Is there anyway to solve this? I put a snippet of the source in the pivottable as an example.

"http ... /PMO/Shared%20Documents/ Finance/Budget/2008/2008%20What%20If%20Budget.xls'!=WhatIf"

View Replies!   View Related
Advanced Filter: Exclude Blank/Empty Cells Criteria
Im using an advanced filter that uses the following criteria
Days Late Note(s) Note(s)
>90 <>*agreement* <>*QTR*

This shows all data over 90 that do not incl the words agreement or QTR in a column marked Note(s).

My problem is that I do not want to show records that are Null in the Note(s) column.

<> Does not work (possibly because it is text and not Numbers)

as this does work if used on records that contain numbers.

View Replies!   View Related
Include/Exclude Specific Instring Characters From A Querry Search
I have a querry- file search application macro which searches all directories and subdirectories found within the provided string path and brings in specific data from spreadsheets in those directories. I would like to have the ability to exclude some of the subdirectories which are currently being querried.

I'm wondering if it is possible to add additional code that will allow me to either include or exclude specific directories being querried.

Here's an excerpt of the ....

View Replies!   View Related
Data Validation To Exclude Letters But Allow Numbers Beginning With Zeros
I am using Excel 07 with Vista, and creating a spreadsheet in which users will be inputting a record into every row.

There is a row that requires a number to be entered, where some of the numbers will begin with 0 or 00 or even 0000. I want to make sure no-one incorrectly enters a letter, however with data validation set to whole numbers, it formats the cell to change an entry of "005667" into just "5667". There is no set length for the field.

The only way to have "005667" is the change the column to text, but then obviously that defeats the whole purpose!

View Replies!   View Related
How To Calc Stdev But To Exclude 0's. Prefer No Array Cntrl + Shift + Enter Calcs
I have a list of numbers and i want to calculate avg and stdev but i do not want "zeros" or "blanks" to enter into the calculations. I have a formula for avg but i need one for stdev.

View Replies!   View Related
Exclude Column In A Range Sort That Includes The Column
is it possible to leave a column out of this sort code as in A the very first column which just numbers the rows?

Here is my sort ....

View Replies!   View Related
Using Count Function Exclude Zeros In Count
I am trying to count data using several criteria

1.Need to add data from for a certain category, say "blue" + data during a certain date but exclue the ones with zeros

My formula using arrays look like this, but it is still counting data with zero in the cell as an item

=count(if(A4:A400(text by category)=”blue”,count(if(O4:O400(date)<”04/01/07”,count(An4:An400)[Actual data],-(countif(An4:An400,0))))))

View Replies!   View Related
"IF" Statement To Exclude A Range
I have a list of users in Colum N and in the next column each user has to assign a code from a drop down list... that part is ok... In the next column I have a formula that returns a value of 1 if the code has been assigned... =IF(N4="","",1)... This is the bit i am struggling with... i now need the value to be zero if the user name appears within a range called "excludePersons"

View Replies!   View Related
Clear Rows Vs. Deleting Rows To Avoid #REF! Formula Error - Macro
I have a macro which is copying data from several worksheets into one consolidation worksheet. When determining where to paste the data into the consolidation sheet, the macro includes some logic to find the last row that has data in it (using e.Range("A65536").End(xlUp).Row, where "e" is a variable holding the name of the consolidation worksheet).

Once all the data is on the consolidation worksheet, I have a second worksheet with formulas that link to the consolidation sheet. The issue I have is that the first step of my consolidation macro deletes all data on the consolidation sheet to ensure that no data is double-counted). I am deleting the data with logic that simply deletes all rows from 3 to 65536. Once these rows are deleted, Excel returns a #REF! error on my second worksheet which is linking back to this data.

Rather than deleting the rows on the consolidation sheet, I have tried using the Clear and/or ClearContents commands instead. This works (i.e., my formulas no longer error out), but results in the consolidation macro running very slowly (~15 minutes, compared to

View Replies!   View Related
Maro, Insert Rows Where Rows Determined By Number Of Carriage Returns
The best way to explain my problem is to look at the table below:

How it looks now: ApplePrice 1
Price 2
Price 3FruitDeliciousPearStore 1
Store 2FruitVery DeliciousHow I want it to look:ApplePrice 1FruitDeliciousApplePrice 2FruitDeliciousApplePrice 3FruitDeliciousPearStore 1FruitVery DeliciousPearStore 2FruitVery Delicious

View Replies!   View Related
Repeat Rows By Mutiplying Current Amount Of Rows By Cell Value
I have a table which contains a list of branches eg;


I then have a spreadsheet which contains a number of columns and rows.

I want to create a new spreadsheet in the first column it will contain the branch from the table above then the rows and columns in the above spreadsheet

The rows and columns would then repeat for each of the other branches in the table. IE if I have 4 branches in my table and 100 rows in my spreadsheet my new spreadsheet would contain 400 rows

View Replies!   View Related
Copyright © 2005-08, All rights reserved