Copy rows based on a condition

Last update on November 4, 2009 04:59 AM by aakai1056
Published by aakai1056

Copy rows based on a condition




Issue



How can I have excel copy the entire row of data from worksheet Employee Inventory to another worksheet called EEs if column Q contains TERM. I've tried almost everything but nothing is working. Please help!

Solution


Assumptions for this code:
1. Employee Inventory sheet has heading in row one so it will start checking the data in row 2.
2. Column Q does not have any empty cells.
3. EEs sheet will have headings in row one so the data will start copying in row 2. 

Set i = Sheets("Employee Inventory")
Set e = Sheets("EEs")
Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(i.Range("Q" & j))

    If i.Range("Q" & j) = "TERM" Then
    d = d + 1
    e.Rows(d).Value = i.Rows(j).Value
    
    End If
    j = j + 1
Loop

Note


Thanks to Helper for this tip on the forum.
Best answers for « Copy rows based on a condition » in :
Basic MS-Dos commands ShowBasic MS-Dos commands Intro The command prompts: Intro Here is a list of basic commands that you can use in a DOS prompt (e.g. a using a boot disk). To have additional information about these commands, type /? after entering the...
Basic Excel Formulas ShowBasic Excel Formulas Below are some basic formulas for Microsoft excel: Basic formula : ADDITION cell A1 to A10 = sum (A1: A10) AVERAGE cell A1 to A10 = average (A1: A10) MAXIMUM cell A1 to A10 = max (A1: A10) MINIMUM...
Colouring cells on conditions ShowColouring cells on conditions There are many pratical functions under Excel which is not commonly used. Example: If you wish a cell automatically turns red (or other formatting border, frame etc) under one condition: a result, a...
Spreadsheets - Conditional expressions ShowWhat is a Conditional Structure? Conditional structures are instructions that allow to test if a condition is true or not. Conditional structures may be associated together. To successfully execute these tests using logical functions, spreadsheets...
Base64 encoding ShowBase64 encoding The concept of Base64 encoding entails using US-ASCII (non-accented) characters to encode any sort of data in 8-bit form. Email protocols were originally designed to send messages in plain text only. But as email systems vary...
They need your help