Selecting Rows Based On Cell Criteria

Mar 19, 2005

I'm sure there is simple code for this but I have excel spreadsheets like the sample attached that are consistant in the types of fields but the only data I want to strip out are the rows where column A has a "G" in the cell, I tried using this

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

but this doesn't work when there is only one row of data. The number of rows with valid data will vary each day. I then tried an if, then statement and a loop but I could never get the loop to stop. I basically want a macro which parses through the worksheet and selects the rows that have that "G" in column A so I can copy them and paste them in another worksheet.

View 8 Replies


ADVERTISEMENT

Selecting Entire Rows Based On Contents Of Cell?

Jul 15, 2013

I have a large set of data regarding the activities going on in particular rooms. The data contains activity name, start time, day, duration, room and size.

I would like to select all rows that contain a cell in the activity name column which contain the words 'VideoConference'.

I have tried to Search, Select All then index the remaining data using =iferror(index(etc. However some activities occur in more than one room and all variables apart from 'Room' are the same. Thus when I have tried to index the room column I get the same room for both rows.

ps the raw data set consists of +28000 rows so I can not simply look through and copy.

View 4 Replies View Related

Delete Duplicate Rows Based On Multiple Cell Criteria

Apr 21, 2007

I have some VB code, courtesy of OzGrid and Davc4, that works well to delete duplicate rows based on criteria in Column A of the active worksheet (albeit a bit slow on large files).

How do I modify the code below to evaluate duplicate data in Columns A through D? .....

View 7 Replies View Related

Selecting Rows In Table Based On Another List?

Jan 30, 2013

I have attached the data in an excel file here. I'd like to select (or highlight) the rows (based on names) in the table on sheet1 based on the list provided in sheet 2. I've been struggling with using a formula for this one. The list on sheet 2 may be longer than the table on sheet 1(since there are entries on the list which might not be present in the table column 2), and the reverse may also be true. I would like to select "entire rows" of names in the table that are there on the list on sheet 2, not just the names column in the table. See the desired result on sheet 3.

View 9 Replies View Related

Selecting Rows Based On A Column Condition

Jun 4, 2009

I have a worksheet which is created monthly by one of our company's employees which reports results for that months operations and successful audits. These audits are separated into various service lines and on the report which is submitted it takes on the following format:


1|Service Line|Data A|Data B|Etc.
2|___CR_______| Data | Data | Etc.
3|____________| Data | Data | Etc.
4|____________| Data | Data | Etc.
5|____________| Data | Data | Etc.
6|___DD_______| Data | Data | Etc.
7|____________| Data | Data | Etc.
8|___MS_______| Data | Data | Etc.
9|____________| Data | Data | Etc.

What I want to do is use a macro from another sheet that has the effect of: While ServiceLine = "CR" copy the row of that line and move it to my monthly summary workbook, sheet 1. Then when the Service line changes to DD have the macro copy that information to the monthly summary workbook sheet 2, and so on. The problem is, if I test the Service Line column each time it will be an empty string the majority of the time and I'm not sure how to combat that with my If, Do, For, and While statements. As I've thought about it there are two solutions that I can think of: someone knows of a way to test the row only if it has information in it, or if there is a way that I can copy the service line information down until the change in service line so I can test Service Line on each Row.

View 3 Replies View Related

VBA Conditional Format Range Of Cells Based Upon Criteria In One Cell / Repeat For All Rows

Apr 3, 2014

Code is trying to

1. Where there is data in column B
2. Goto first row of data
2. If column J = "Closed"
3. then select Cells from column B to J in that row and colour these cells in red
4. If column J= "Open"
5. the select Celss from column B to J in that row and colour these cells in grey
4. Repeat for next row until you get to cell in column B where there is no data.

Recieve run time error 1004: Method 'RAnge of object_GLobal failed

===============================================================
Sub Colourclosed()
Sheets("Risks").Select
Dim LastRow As Long
Dim i As Long
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = 8 To LastRow
If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select
Selection.Interior.ColorIndex = 3

[code].....

Note: I've managed to do it for one cell ie for Column B by replacing "If Range("J" & i).Value = "Closed" Then Range("B", "J" & i).Select with "If Range("J" & i).Value = "Closed" Then Range("B" & i).Select" This works but only colours in cell in column B, how do i do this so it colours range of cells

View 4 Replies View Related

Selecting A Range Based On Cell Value

Oct 24, 2008

how (in VBA) to select a range within a column where all values are the same?

For example, given the following:

A B
1 ID Value
2 1 A
3 2 A
4 3 A
5 4 B
6 5 B
7 6 C

How would I select range(B2:B4) where all values = A?

View 14 Replies View Related

Selecting Macro Based On Cell Value

Nov 18, 2008

I have a comparison macro that compares one sheet with the next, it is called sub comp1 (). There is a condition whereby columns must be removed before sub comp1 can run. To remove these columns I run the macro called sub colcut (). What I need is the following action:

If cell BJ4 of current worksheet = "RE COMMENT" then run sub colcut () followed by sub comp1 () otherwise only run sub comp1 (). FYI The macro is to be run manually via a button rather than triggered by the value of the cell.

View 3 Replies View Related

Selecting A Worksheet Based On Cell Value

Mar 5, 2009

I am trying to automatically select data from a different sheet to fill data in a cell based on two other cells. I've attached a simplified version of what I'm trying to do (with detailed explanations). I hope it has enough info to get my point across.

I posted this in the "programming" forum because I'm pretty sure I will need to use vba in some form or fashion to get this to work. I am more familiar with access than excel, so I may be missing some easier way to make excel do this.

View 3 Replies View Related

Selecting A Range Based On The Value Of One Cell

Feb 27, 2009

I have rows of data, and the first number corresponds to the number of subsequent numbers that are relevant.

I want to paste that number of values into a second spreadsheet. How do I do this?

e.g.

A1 B1....
6 21 15 14 32 85 14 16 21 25 26 24

In this case I want to copy the 2nd-7th number, as the 6 tels me I want the next 6.

View 9 Replies View Related

Selecting Sheet Based On Cell Data In VBA?

Nov 30, 2011

I have a workbook with many tabs and I am writing a macro that works on the active sheet but needs to pick up data from another tab, which is labelled on the active sheet.

As I want to write this macro only once but different tabs have different tabs they look to, VBA code that slects the sheet named in a fixed cell on the active sheet.

This is the code that currently looks up to the tab labelled 'Cork, FA'. However, if this tab name is stored in cell AA2, I would like the code to choose the tab based on the information in AA2?

ActiveSheet.Range("A1").Select
ActiveCell.FormulaR1C1 = "=COUNTA('Cork, FA'!C)"
CountClients = Selection
Selection.ClearContents

View 1 Replies View Related

Selecting Between Two Worksheets Based On Cell Entry?

Jul 12, 2012

I have a workbook with 3 worksheets which contains a single spreadsheet where the user enters data and 2 worksheets containing all the named ranges and formulas(divided up by product model) used on the data entry worksheet. What I've been trying to accomplish is to choose one worksheet based on which product model I select on the data sheet.

View 1 Replies View Related

Selecting A Range From Starting/ending Cell Rows And Columns

Feb 19, 2010

I need to accomplish selecting a range using variables for the starting cell and ending cell rows and columns.

Pseudo code (how I'd like the syntax to be, but isn't):

Worksheets(mysheet).range(startCellRow, startCellCol : endCellRow,endCellCol).Select
With Selection
...
End With

View 6 Replies View Related

Delete Rows Based On Criteria & X Rows Below

Mar 5, 2008

This sheet has A:K columns and 1:3212 rows. There are 'page headers' that are in the text file that I want to delete (the text file was exported from an AS400 program). The first row that starts the page header has SA341 in column 1. Each page header has 5 rows. I used this code from one of the other threads on deleting rows, but I obviously do not understand the code as it deleted all rows that contained SA341.
Sub DeleteRows()
Column_To_Check = 1
Start_Row = 1
End_Row = ActiveSheet. Cells(Rows.Count, Column_To_Check).End(xlUp).Row
MsgBox End_Row
Search_String = "SA341"
For Row_Counter = End_Row To Start_Row Step -1
If ActiveSheet.Cells(Row_Counter, Column_To_Check).Value < SA341 > Search_String Then
ActiveSheet.Rows(Row_Counter).Delete

End If
Next Row_Counter
End Sub

View 9 Replies View Related

Cut Rows Based On Criteria?

Jan 19, 2014

There are two workseets involved. Inventory is the main and Pending is the second. There are 28 columns of data in each that match based on row 1 headers. I need to figure out how to cut all the rows with "Pend" listed in Inventory column Z to one row below the data in column A of the Pending worksheet. The data can be dates and also notes but no matter what is in column Z it must be cut and moved.

View 6 Replies View Related

Deleting Rows Based On Two Criteria?

Jan 30, 2013

I have a column with dates (dd.mm.yy) and I have a column with names. Moreover, several dates and names are repeated. What is needed, is to delete all the rows in which the difference between dates is smaller than 1825 days (5years) for the same name. (Namely, if I have three rows 01.01.1996 - "A"; 01.01.2002 - "A" ; 01.01.2005 - "A" I want all the rows with "A" to be deleted)

View 5 Replies View Related

Delete Rows Based On Two Criteria Within Each Row

Sep 24, 2009

I have a sheet with say 1000+ rows that is a QA report of possible mistakes found in a employee rostering tool.

The report finds all occurrences where the staff member only has one coffee break rather than the two they are entitled to. However, on days where they have training, or other half day commitments, on of the coffee breaks is not entered into out tool. This means we are getting a whole lot of entries that we do not require(i.e. one coffee break error).

The tool uses icons but the cells do have a single unique character under each icon type. For example, the coffee break cells have a "T" and training cells have a "&" underneath the icon (without quotes). The staff members day is separated into 15 minute blocks and each block occupies a cell in a column. This means each staff members day spans many columns in a row (i.e. C=8am, D=8:15am, E=8:30am etc...).

What I have been trying to accomplish is to create a macro in VB code that will locate any row with both a "T and a "&" and delete it. This will eliminate occurrences that we are aware of and leave only genuine errors. There are other combination's that I would like to include also such as:
"T" "["
"T" "#"
"T" "@"
"T" "]"

The first row is headers and the first two columns contain team names and staff names which I am trying to exclude (because names contains "T"'s ).

I have spent many hours now looking for example code on Google, this forum and other forums, however most of the examples I have found are looking for two criteria within a column or specific criteria that is not suitable to my application (i.e. values <> certain numbers etc...).

View 8 Replies View Related

Hide Rows Based On A Criteria

Oct 6, 2009

What am I doing wrong here?

I have a code and it doesn't error out, but it won't hide the rows either. I'm pretty sure the red is what needs to be altered. I've tried adding "Selection.", "Rows." and "Cells." and none of them are working.

View 12 Replies View Related

Delete Rows Based On Criteria?

Jan 23, 2010

I had a raw data sheet in which i need to prepare a statement just like the attached worksheet.

My requirement is to delete all those rows in the department column except the department which starts with "C" Just like "CNN" & "CNN-IN".

Rows with data containing the words starting with "CNN" should not be deleted .The rows can contain words with "CNN" or "CNN*"(here * denotes anything after the word CNN)

I had just formatted the whole worksheet for easy reference.Actually the raw data is extracted from other program which is very clumsy & irregular.

The department column might be in any column.

View 14 Replies View Related

Counting Rows Based On Different Criteria

Oct 28, 2008

I've got two worksheets. One contains data from a query, the other worksheet should count rows in the queryresults based on two criteria. See example.

I tried doing it with sumproduct in the normal variant and the array-variant...

View 8 Replies View Related

Hiding Rows Based On Criteria

Dec 22, 2011

I am stuck with a task.

I need to hide all rows in a worksheet except the rows which contain the word which the user inputs through find (CTR+F).

The input word should be captured in a variable and this should be searched in all rows & hide all other rows in the sheet which does not contain this word.

View 2 Replies View Related

Deleting Rows Based On Criteria

May 29, 2012

I have this excel file where I every day have over 10.000 rows. I have 2 sheets, one called "Data" and once called "Include list".

The Data sheet contains a list of all of our customers and their customer IDs. The Include list sheet should contain the Customer ids which I want to keep in the Data sheet.

So what I have done is to loop through the Data sheet. If you are in the Include list sheet you should not be deleted fromt he Data sheet. If you are not then the row should be deleted.

I have actually done this and it works but the problem is it take a lot of time to run. I tested in earlier today and I had to break it after 20 minutes which is way too long for our users to wait.

This is the code I wrote:

Sub Include()
Dim FindString As String
Dim Rng As Range
Dim RowNr As String
Dim Lookup_x As String

Application.ScreenUpdating = False

[Code] ........

So my question is, is there any other way to speed this process up? The ScreenUpdating part I have tried but it didnt really speed it up as much.

View 1 Replies View Related

Deleting Rows Based On Two Criteria?

Apr 20, 2013

I have a very large spreadsheet (>10,000 rows) of data. I did Conditional Formatting based on duplicate values for the serial numbers column (B) and then sorted to "Put Selected Cell Color on top". Next I did a sort by the Last Scan Date column M (Oldest to New). The date/time format appears as follows

I used the following macro to delete rows with duplicate serial numbers but retain the row that has the newest time stamp. When I run my macro it's doing the opposite where it deletes rows with the newest time stamp and retains the oldest time stamp.

Code:

Sub Test()
'for Macro to Delete Duplicate Rows and Retain Unique Value
Dim LR As Long

[Code].....

View 9 Replies View Related

Delete Rows Based On 2 Criteria

Nov 30, 2006

Is there a vba code that can delete the entire row based on two criterias? If a row has a location of Canada and is a female, then delete the entire row.

Name Location Gender
Alan Mexico Male
Dick USA Male
Sharon Canada Female <-- This will be deleted if based on criteria
Mike Canada Male

View 3 Replies View Related

Delete Rows Based On Criteria ..

Jul 19, 2007

I am a pharmacist that runs reports and I need to sort and exclude data

It is in Excel 2003 format

I run a report that includes a patients name in one column, the medications name in another column and if the medication was withdrawn, wasted, returned or restocked in another column. Withdrawn, wasted, returned or restocked are treated as different functions and each have their own row entry. Patient and medication name are the same in all situations

I need a macro that will look at the patients name and medication name, see if it is the same and delete all rows that have a withdrawal with an associated wasted, returned or restocked.

View 9 Replies View Related

Move Rows Based On A Criteria

Jul 21, 2007

Cell a1:a3000 contains a lot of trackingnumbers. Cell b1:b3000 I have a "IF" formulas in place to write "ok" if part of the data in "column A" matches my criteria. What i need is a macro that will cut all the rows showing "OK" in column B from "sheet1" to the next availible row in "sheet2"

I have tried converting copy macros to Cut macros, but thye have not worked. I am rather hopeless for the coding part and mostly make macros based on the record feature. Also looked at the ones that have been showed here, but my skills do not make me able to change them to fit my excel sheet. About 8000 rows with data is added into this sheet everyday and the criteria is not always present. That means on some days there will be no "OK" showing in column B

View 3 Replies View Related

Extract Rows From One Sheet Into Another Based On Certain Criteria?

Dec 21, 2013

How can I extract rows from one sheet into another, based on certain criteria?

View 1 Replies View Related

Copy Rows From One Workbook To Another Based On Criteria

Aug 4, 2009

I have been struggling to setup these two workbooks for a bit now, and I can't for the life of me figure out a formula to do what I need to do. Essentially, I have one workbook that contains a list of purchase records for my company, sortable by Date, Vendor, Price, etc. and one workbook that has a sheet for every vendor. What I need is a formula that will search column B for a vendor, Allied Waste for example, and transfer all the information within the rows for every instance that vendor is found to the new workbook.

This is basically just a way where I can input information once in one workbook, where the sheets are divided by month, and the info will automatically transfer to another workbook, where the sheets are divided by vendor.

View 2 Replies View Related

Extracting Certain Rows Based On Multiple Criteria?

Oct 29, 2013

There is no where else for me to go. My problem is: I have a table with over 30,000 rows and columns A - W. The first column A contains UNIQUE ID. Column G contains CATEGORY CODE.

Example.png

Since Unique ID (column A) can be listed more than ones, I need to select only those records (rows) that correspond to the following: Category Code (column G) is either 14, 15, 16, 17, 18 and not any other. I hope you can see the attachment, UNIQUE ID = a;does not satisfy my criteria as it does contain Category Code 14, it also contain other codes. The final result from the sample provided would be extracting data for UNIQUE ID = e (since it is the only record that does not contain any values other than 14, 15, 16, 17, 18).

View 6 Replies View Related

Delete Rows Based On A Text Criteria

Aug 24, 2009

I am trying to figure out how to have some VBA look down Column 1 and where the Cell's string value = "GRANDTOTAL", to DELETE that ROW and ALL ROWS below.

Can anyone help me out in writing the Visual Basic code?

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved