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
ADVERTISEMENT
Feb 7, 2009
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 3 Replies
View Related
Oct 19, 2007
I would like to Use my Excel VBA program to search each row in a csv document for a name (located in a cell ) if the name exists then I would like to delete the entire row.
Whenever I try to do the above in excel, even when I save in csv format the file formating or something gets changed and the file which has to be procesed through another program then process incorectly.
Is there a way to do what I need while retaining the formating which must obviously be changed when the file is opened in excel?
View 9 Replies
View Related
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
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
View Related
Sep 11, 2013
I want to select a range of rows 11, 14 and 23 to 24 in column j, plus the 2 rows to the right of column J and run a sum formula on the range. My procedure is selecting rows 11/14/23/24 and doing the sum for only column J. How do I make it so it runs the sum formula on all 3 columns. My code is copied below. I am fairly new to VBA.
Code:
With ActiveSheet.Range(Cells(27, j), Cells(27, j)).Select
Set sumRng = Intersect(Range("15:21, 25:25"), Columns(j)).Offset(0, 2)
ActiveCell.Value = WorksheetFunction.Sum(sumRng)
End With
View 4 Replies
View Related
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
Jan 11, 2005
I'm attempting to write a macro that will select all the rows which contain a value in a specific column. Say for example, all the instances of "test" in column C, I need to select the entire row with that value in the column.
I should also mention that by using an autofilter, only specific row numbers are selected. The source spreadsheet is dynamic and will not always display the same value in the same row, and thus I need something a little more adaptable.
View 9 Replies
View Related
Oct 2, 2008
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
View 2 Replies
View Related
Nov 21, 2008
I am inserting a subtotal above my data set, the first cell that this subtotal takes place is "R1", then I have an unknown number of subtotals going right, depending on the number of months FCSTed by my sales managers.
Attached you will find a subset of the data and you will see that I need to select "S1 : iLastColumn(1)".
View 4 Replies
View Related
Jul 20, 2009
Is it posibile to make a macro that automaticaly delete rows based ona a condition. Condition is to delete rows that have B column value "" or zero. For example:....
View 12 Replies
View Related
Aug 8, 2012
I have a data dump. I download everyday with 200 rows and 20 columns. I need to remove any rows where there is no value in either columns C or D. Is there a quick way to do this?
View 7 Replies
View Related
Feb 11, 2013
I have a cell with today's date on it. i.e., cell A1 = today()
Then, I have rows of data with one of the columns with a date on it. I've put in an object (button), where when the user presses this button, I want all the rows of data that do not have the date on cell A1 to be hidden.
Then on the second button, when the user presses the button, to unhide all the rows that were previously hidden. To make it easier, I can simply state it to unhide all the previously hidden rows (but NOT columns - there are still hidden columns which I want it to stay hidden).
So in sum, using table below. If I have on cell A1 - today's date is 2 Mar 2013, pressing first button would hide the row with Jane Y's record. Then pressing second button would then unhide all previously hidden rows.
DATE
NAME
DEPT
1 Mar 2013
John X
Accounting
2 Mar 2013
Jane Y
Operations
3 Mar 2013
Joe Z
Marketing
How can I accomplish this?
View 6 Replies
View Related
Jul 3, 2007
complete this coding?
Sub DeleteBlankRowInColumnA()
'this macro scrolls down Column A if the cell is empty the row is deleted.
Dim A As Integer
A = 0
Do Until A = 142
If Range(0, "a1") = 0 Then
Selection.Delete shift:=xlUp
End If
Loop
End Sub
View 9 Replies
View Related
Sep 7, 2009
I am wanting to paste formula from N1 till N X (X = varaiable row) where X reliant on Column A Row X. If Column A Row X has character "=====" it should paste N1 Formula all the way down till N Row X which is equal to A Row X containing "=====" .
I might be sounding complicated over here but it is a simple equation.. I have tried to approach this in the capacity i could by condition if Column A row x is blank delete the row.
View 2 Replies
View Related
Apr 19, 2013
I have tried to write some VBA to copy and paste rows based on the condition in cells A:A in sheet one. So far I have managed to put something together which will paste the first row and insert a new row below but keeps on pasting the same row. But not much else! Not sure I have tried to make it more difficult than what is it.
I would like it to copy a row based on a certain condition ("WRW") in A:A sheet one and paste it to sheet2.
VB:
Function DoOne(RowIndex As Integer) As Boolean
Dim Key
Dim Target
[Code].....
View 3 Replies
View Related
Apr 24, 2009
I have an Excel spreadsheet that is given to me weekly, but I'd like to remove repetitive and empty rows before presenting it to someone. It is set up like this:
Row 1, A1, contains the word Project.
Row 2 is blank
Row 3 is blank
Row 4 is blank
Row 5, A5, contains the word Organization.
Row 6 is blank
Row 7, A7, contains a 6-digit number starting with 3.
Row 8 is blank
(all of the above starts over again (loops) approximately 30 times)
The final row contains the phrase "Grand Total"
*Row 5 is repetitive and is not required. I'd like to delete it.
I have too many empty columns. Getting rid of them (and Row 5) would greatly shorten my spreadsheet.
I am aiming for:
Project
301111
Project
301112
Project
301110
(Repeat until finished)
Grand Total:
View 4 Replies
View Related
Sep 18, 2009
I have created a worksheet which contains seven columns of data and many rows. I need to copy entire rows into nine separate worksheets depending on the data in column G.
I know nothing about VBA but have read enough to realize this is the way to go with this. Would someone point me in the right direction on how to set this up? I've attached the file so you can better see what I'm talking about.
View 5 Replies
View Related
Aug 17, 2007
Attached is a portion of the file I will be working with, just to illustrate what I would like to do.
I have a macro that copies and pastes data - it uses these Tasks as templates. The macro copies these rows for each task that is required under the headings (Task #1 - Task#?). What I would like is to hide the rows under Station Maintenance Task #0 and it's 6 rows that are associated with it, as well as Task #0 under CMS and the 12 rows associated with it. In order for the macro I already have work properly is that these would be unhidden for the macro to run and then re-hidden afterward.
I searched on the forum for this topic, and found hide/unhide where you would put in the beginning row and ending row - however since there will be numberous rows added in, this type of defining rows would not work.
View 4 Replies
View Related
Aug 28, 2007
I have a worksheet with part numbers, and I want to delete all the rows with part numbers matching a part number in another workbook (I could import the list into the part number workbook in a new sheet if it makes it easier). The part numbers in the other worksheet would be in column A.
View 5 Replies
View Related
Jun 17, 2008
I'm looking to create a macro that copies a row of data and pastes it within a range when a condition is met. For Example, if A1 of the header Row matches A2 within a Range of A2:A100, I want to Copy the A1 Row and Paste Special Values over the Match in A2. Likewise if the match is contained in Row 100, I would like to Copy from A1 and Paste Special Values into A100.
View 2 Replies
View Related
Mar 1, 2013
Having book1 and book2 (that is closed). Is there a way that if I erase a value "X" in book 1, update book2 (without open it) deleting the complete row/rows where the value X is found?
View 4 Replies
View Related
Mar 22, 2007
Is there a way to color fill an entire row based on a value in a certain column? Say I have a large file and one column is "yes" or "no" If I wanted all of the "yes" rows to be colored -
View 5 Replies
View Related
Mar 7, 2008
I want by using some code I've seen on this forum or using the macro writer and then tweaking the code. So with that said, I've written the attached code but I know there is probably an easier way to write it. It cycles through about 12 sheets using the same below code, but I didn't list that code.
Sub Hide_Rows()
Dim i As Integer
For i = 3 To 418
Sheets("AFA - UMBI").Select
If ActiveSheet. Range("b" & i).Value = "2008-2" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2008-3" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2008-4" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-1" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-2" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-3" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-4" Then
Rows(i & ":" & i).EntireRow.Hidden = True
End If
Next i
End Sub
View 7 Replies
View Related
Jan 21, 2008
I'm trying to Multi-Select rows in order to format all rows, in "One Step" if the cell in col. "A" = 1. I managed to write the hereunder code but I'm still frastrating because I don't like the first RNG setting command. How can I avoid declaring RNG as row(1) and still be able to run the code. I assume I need some other Range set for RNG which, unfortunately, I could'nt find and the command I used includes row1 in RNG although cell A1 = 5.
Sub Conditional_Multi_Rows_Select()
Set RNG = Rows(1) ' this is my problem
LR = [A65536].End(xlUp).Row
For R = 1 To LR
If Cells(R, 1) = 1 Then
Cells(R, 1).EntireRow.Select
Set RNG = Union(RNG, Selection)
End If
Next
RNG.Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 3
End Sub
I had in mind to use:
Set RNG = Rows(65536)
but this will not make it a "neat" vba code, either.
View 9 Replies
View Related
Jun 6, 2008
I Have workbook-1 with 5 cells and 10 rows and in 5 th (E) cell i have value of 1 or 0 .
i want to selectively copy entire rows which has value = 1 in cell(E) and paste these rows into 2 nd row(A2 cell) of workbook-2 using Macro
View 5 Replies
View Related
Aug 11, 2008
On my main worksheet, I have a list of stocks in the "A column", so for example reading down the list would be:
Vodafone
Vodafone
Ericsson
Nokia etc...
I have a seperate worksheet for each different name (about 100), and I wanted to know if there was a way to select all the information relating to the name "vodafone" for example, and to copy and paste that into the worksheet called vodafone. The problem is copying and pasting 100 times would take ages and wanted to know whether there is an easier way using VBA.
View 3 Replies
View Related
Jul 27, 2012
I have eight sheets that have lists of questions in, which I want to copy across to a results sheet if the answer to a question is 'Yes'. Each question takes up rows B:H inclusive, and I would want to copy them to rows B:H in the results sheet. The "Yes" value will be found in column F of each row.
How can I set up a macro to copy the entire rows (without formatting) into a results sheet properly? I've tried every solution I can find but always hit a roadblock somewhere.
Ideally I would like to have a 'populate' button on the results sheet that would find every question that was answered 'yes' across the eight survey sheets and import them into results sheet.
View 2 Replies
View Related
Nov 20, 2012
I have multiple sheets with the same format.(all columns have same format) in column "A" i have a value lets say "B1-1".
What I need to do is to copy from all sheets the rows containing in column "A" the same criteria("B1-1") to a new sheet and skip the rows that have a blank cell in Column "A".
View 5 Replies
View Related
Feb 4, 2010
I am self-taught (arguably by a pretty shoddy teacher), and am determined to figure *some* of this stuff out. I need a code to look at the rows on one worksheet and based on the value of column A for each row, copy cells A through E in that row to cells A through E of a blank row on a corresponding worksheet. I found a code from another post that does just what I need it to with one small exception. The code was posted by DaveGuggs and is as follows:
View 2 Replies
View Related
