Free Microsoft Excel 2013 Quick Reference

Add New Row Based On Criteria

Ok I will have a spread sheet with

Column A B C

A will contain

Document Name

B Will contain Document #

C will contain MULTIPLE Part #'s eg Part1, Part2, Part3

that I will break apart using text to column.

What I am THINKING I need to do is a VBA...that goes through columns C to last column in the current row. If there is text/number/string in any column beyond C...lets say D has a string in it. It will Create a new row in the opposite direction that the VBA is searching through the rows, then it will copy/cut that string from D into the NEW rows column C, then empty column D from the checked row and then copy Row A & B from the checked row to A&B in the NEW row, and then move over to the next column and check for a string and if there is one then repeat then above of creating a new row copying to C in the new row etc. Otherwise check the next row and repeat.


|A|B|C|D|E|F| is how I will show each column in a row


would become


any Suggestions, it's easy enough on paper but then all the fun excel methods/properties I don't have a book on get involved.

Post your answer or comment

comments powered by Disqus
New to VBA, can't quite figure out how to hide rows based on criteria. I need to hide entire rows based on the value in column H, (hide row if value NOT equal to 90 or 0). Help?

Hi, I have been struggling with this problem for many days now and would really appreciate some help.

I have programmed before, but never any VBA stuff, so I'm teaching myself as I go along.

I have a worksheet with contents in two columns.
Column A - I have ID numbers
Column B - I have the number of days

What needs to get done
For every ID number, I need to have inserted x number of rows, where x is the corresponding number for the ID number from column B.

A1 has ID Number 001 and B1 has 2
A2 has ID Number 002 and B2 has 3
A3 has ID Number 003 and B3 has 2

When I execute the macro:

A1 needs to have 001, B1 says 1 with a new empty row below that AND
A3 needs to say 002, B3 says 3 with 2 empty rows below that AND
A6 needs to say 003, B6 says 4 with 1 empty row below that
... and so on

The numbers in column be are NOT sequential or follow any pattern.

I have attached the sample file, along with a macro that I created. However, it works only for the first two rows, after that it keeps pushing down the new rows without getting to the "header" row.

I hope I have explained it sufficiently well. Hopefully someone will be able to help me.

Edit: I should add that the way I tackled the problem was to find out how many rows we have, and store the number of days in an array.

I'd then do a loop that loops based on the number of rows I have, and in that one, nest another loop that would insert new rows based on each member of the array.

The obvoius problem is after inserting the first set of rows, being able to position myself so as to insert the 2nd set of rows (and third, and so on) at the correct points, and not just at the top.

This is the part that is driving me crazy, as it's relative based on how many previous rows have been inserted.


I have searched topic' Insert Rows based on Criteria' in this forum and got some very good hits. But, unfortunately, my case is little bit different then those all and that makes the unique posting.

Here is my issue:

There are many columns in Sheet1 and more will be added for titles of the columns remains unchanged. One of the Col. in Sheet1 is 'Part Number'. Part Numbers are defined as 'Configurations', and each Configurations has additional parts and peices which are broken down in Sheet2. Back in Sheet1, I also have a Col. for QTY. So, if Sheeet1 has a part number that matches in Sheet2, then I would like Excel to copy all 'parts and pieces' and QTY from Sheet2 to Sheet1. Please make sure that I want the QTY to be multiplied if applies. Please see below for an example:

Sheet1 looks like this:

Ordered Date PO Part Number QTY
22-Mar-06 9000262550 TOASTER BTRY KIT-02 3
17-Dec-06 9000388225 E-NET-CRADLE-KIT-03 1
17-Dec-06 9000388225 SPCCE-SLDRT-01 1

Sheet2 Looks like this:

Configuration Type Part Number Total
E-NET-CRADLE-KIT-03 23844-00-00 1
E-NET-CRADLE-KIT-03 50-14001-004R 1
E-NET-CRADLE-KIT-03 50-16002-029 1
E-NET-CRADLE-KIT-03 CRD9000-4001E 1
SPCCE-SLDRT-01 200360-101 1
SPCCE-SLDRT-01 210156-001 1
SPCCE-SLDRT-01 25-62167-01R 1
SPCCE-SLDRT-01 ADP9000-100 1
TOASTER BTRY KIT-02 23844-00-00 1
TOASTER BTRY KIT-02 50-14001-004 1
TOASTER BTRY KIT-02 50-16002-029 1
TOASTER BTRY KIT-02 SAC9000-4000 1

So, after I run the code, it should look like this in Sheet1.

Ordered Date PO Part Number QTY
22-Mar-06 9000262550 23844-00-00 3
22-Mar-06 9000262550 50-14001-004 3
22-Mar-06 9000262550 50-16002-029 3
22-Mar-06 9000262550 BAP9000-100 3
22-Mar-06 9000262550 SAC9000-4000 3
22-Mar-06 9000262550 SPGBD_KITTING_01 3
17-Dec-06 9000388225 23844-00-00 1
17-Dec-06 9000388225 4S-PW-CR-BRKT-02 1
17-Dec-06 9000388225 50-14001-004R 1
17-Dec-06 9000388225 50-16002-029 1
17-Dec-06 9000388225 CRD9000-4001E 1
17-Dec-06 9000388225 200360-101 1
17-Dec-06 9000388225 210156-001 1
17-Dec-06 9000388225 25-62167-01R 1
17-Dec-06 9000388225 ADP9000-100 1
17-Dec-06 9000388225 CCESP-MC9060-SK0H9AEA715 1
17-Dec-06 9000388225 SDSDJ-128 1
17-Dec-06 9000388225 SLH-001 1
17-Dec-06 9000388225 SPGBD_KITTING_01 1
17-Dec-06 9000388225 SPGBD_SD_INST_01 1
17-Dec-06 9000388225 SPGBD_STAGING_01 1
17-Dec-06 9000388225 STX-SCGD-01 1
17-Dec-06 9000388225 STX-STL-01 1
17-Dec-06 9000388225 YCTM-001 1

I have also attached a sample file for your review. If you can help me with this, that would be awesome and very helpful.

I need to insert a new row according to its 'Priority'. So If my SSR has a priority of 5, a new row needs to be entered from number one priority, then two, etc. However, there are three different sections in my column, so it first has to figure out which section to look in, then search where to insert the new row based on the Priority number. For example, let's say I have a priority number of 5 that belongs to the Corporate section and here is the column that is already set:


Mission Critical


I need to insert a new row under 'Corporate' and in between the 3 and 6. I have code that selects the appropraite heading, and just inserts the new row right after the heading, but thats isnt what i need.

            Select Case SSRtype
               Case "Corp"
                  Range("D4:D100").Find(What:="CORPORATE", After:=Cells(20, 4), LookIn:=xlValues, LookAt:= _
                  xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
                  n = ActiveCell.Row
                  Cells(n + 2, 4).Select
                  ActiveCell.Value = SSRname
                  ActiveCell.Offset(0, -2).Value = SSRR
               Case "MC"
                  Range("D6:D100").Find(What:="MISSION CRITICAL", After:=Cells(50, 4), LookIn:=xlValues, LookAt:= _
                  xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
                  n = ActiveCell.Row
                  Cells(n + 1, 4).Select
                  ActiveCell.Value = SSRname
                  ActiveCell.Offset(0, -2).Value = SSRR
               Case "Support"
                  Range("D4:D100").Find(What:="General Support", After:=Cells(100, 4), LookIn:=xlValues, LookAt:= _
                  xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False).Activate
                  n = ActiveCell.Row
                  Cells(n + 1, 4).Select
                  ActiveCell.Value = SSRname
                  ActiveCell.Offset(0, -2).Value = SSRR
            End Select
So anyone got any suggestions??

I have been struggling with a macro for my excel worksheet. I need a macro that will delete rows based on criteria in two columns. Specifically....

EXAMPLE (note row 1 is column headings)


I need to delete all rows in which the value of column C = "ASSEM"
I ALSO need to delete all rows in which the value of column D = "D" or "M"

I would really appreciate any help, thx.

Dear All,
How do I delete rows in worksheet1 containing hundreds of rows based on criteria as in worksheet reference as shown below. Until now I'm doing the deletion manually, which is very cumbersome.
Your guidance how to delete it using formula/program would be much appreciated.

WORKSHEET1 - before deletion based on criteria in Worksheet-Reference (text) . Column D is text others are date and numbers.
1 a1 b1 c1 d1 e1 f1
2 a2 b2 c2 d2 e2 f2
3 a3 b3 c3 d3 e3 f3
4 a4 b4 c4 d4 e4 f4
5 a5 b5 c5 d5 e5 f5
6 a6 b6 c6 d6 e6 f6
7 a7 b7 c7 d7 e7 f7
8 a8 b8 c8 d8 e8 f8
9 a9 b9 c9 d9 e9 f9
10 a10 b10 c10 d10 e10 f10

WORKSHEET reference (have data as in Worksheet1,column D)
1 c3
2 c5
3 c7
4 c8

AFter deletion
1 a3 b3 c3 d3 e3 f3
2 a5 b5 c5 d5 e5 f5
3 a7 b7 c7 d7 e7 f7
4 a8 b8 c8 d8 e8 f8

Thank you


I'm trying to create four new workbooks from a master workbook by copying rows from the master that meet criteria. I'm using the autofilter on column 1. My attempt at a macro (below) copies the first set of rows (based on criteria "H"), but stops with an error when attempting to use the autofilter for the second criteria.

Here's my code:

     ' SortByC1 Macro
    Selection.AutoFilter Field:=1, Criteria1:="H" 
    Selection.AutoFilter Field:=1, Criteria1:="P" 
    Application.CutCopyMode = False 
    Selection.AutoFilter Field:=1, Criteria1:="R" 
    Application.CutCopyMode = False 
    Selection.AutoFilter Field:=1, Criteria1:="X" 
    Application.CutCopyMode = False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am pretty new to vba, and am looking for a code to cut and paste a row of information from sheet1 to sheet2 based on criteria from sheet1 cell showing "complete".
I have attached a sample of the sort of thing I am after, and am hoping for some much needed help.

Many Thanks in advance,



I have a query in excel regarding the extraction of certain specific rows based on a criteria. It goes like this:

I have two sheets in one excel file. The first excel sheet has five columns: Name, number, date, type and reg - with approx 8000 rows.

The second excel sheet has just one column: 'Name' with around 160 rows.

My Requirement: I want to scroll through each value in the second sheet(which is a text), check whether that particular name is present under the Name column in sheet 1, and if it is present, copy the entire row to a new Sheet: Sheet 3.

I need to get this done for all the 160 records from sheet 2 and cross check it with all the 8000 records of sheet 1 and thus get the output.

Hope I have made myself clear.



I am very new to VBA, and i have some trouble with deleting rows: i have data spread over several tabs and each tab exceeds 300,000 rows. I want to delete entire rows based on a criteria (e.g. Tab 1 column A (with headers) not equal "Apple" to be deleted), however, the location of the target variable is mixed up through tabs (in different columns) and it takes way too long to clean up data.

I have tried logic below previously:

wb.Worksheets("JobTitle Raw Data").Select 
z = wb.Worksheets("JobTitle Raw Data").Range("h2:h60").Cells.SpecialCells(xlCellTypeConstants).Count 
n = 0 
k = n 
Do Until k > (z - 1) 
    j = 2 + n 
    If wb.Worksheets("JobTitle Raw Data").Range("H" & j).Value = accountManagerName Then n = n + 1 Else
wb.Worksheets("JobTitle Raw Data").Range("h" & j).EntireRow.Delete 
    k = k + 1 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Is there any better and more efficient way to do this?

Thanks in advance!

Hi All,

I am a SAS programmer, but very new to VBA programming. I would like to run a VBA code to select certain rows based on multiple criteria. Could you please help me out? THANKS IN ADVANCE FOR YOUR HELP!!!!

Please find the attached excel workbook.

Here are the records I want to keep:

If group in ("X1", "R6") and (12/27/05

New user seeks help!

I need some assistance in selecting rows based on cell criteria. Here’s a simplified example of my data:


I’m trying to find a way of automatically selecting (and ultimately deleting) the rows where the data in Row A is duplicated and the adjacent right cell is blank. Rows 2 and 5 would be selected in this example.

Can’t seem to crack it myself. Any help would be greatly appreciated.



Hello. I've tried searching for help on this, but so far I haven't found an exact answer. Hopefully someone here can help.

I have a workbook with 3 sheets. Sheet1 links to the other worksheets, via the following formulas: =Sheet2!, =Sheet3!
These formulas are always placed in Column A, in their own unique ranges (ex: Sheet2 might be A1:A22, while Sheet3 is A23:A57).

If a row gets added or deleted on Sheet2, I want Sheet1 to be "updated" the same way. To avoid getting myself confused, I thought it'd be easier if I first did the following calculations on Sheet1:

*Cell H2 (Total Rows, Sheet1): Counts the number of non-blank rows in the current sheet (Sheet1).
*Cell I2 (Total Rows, Sheet2): Counts the number of non-blank rows in a different sheet (Sheet2).
*Cell J2 (Difference): Subtracts I2 from H2 (=I2-H2)

Using these values, I'd like for the macro to look something like this:

If I2 > H2 (which means there's a positive number):
*Add the number of rows based on J2's value. (ex: If J2 is "4", then add 4 rows)
*Add these rows, starting from the last row with "=Sheet2!" written in Column A (i.e., if Row 20 is the last one filled, then Rows 21, 22, 23, and 24 will be added).

If I2 < H2 (which means there's a negative number):
*Delete the number of rows based on J2's value.
*Delete these rows, starting from the last row with "=Sheet2!" written in Column A. Since it's starting from the last row, it'll basically delete these rows backwards (i.e., if Row 20 is the last one filled, then Rows 20, 19, 18, and 17 will be deleted).

I hope I've explained things clearly. If you're confused, and you need me to make an example workbook, let me know. Thanks in advance!

Hi All,

I'm trying to use VBA code to delete certain rows based on a couple of criteria as follows:

1) If column header is "TERMINAL NAME" and the cell value in that column is "BONDDESK", I need the entire row deleted.

2) Similiarly, if the column header is "PRODUCT TYPE" and the cell value in that column is "CORP", that entire row needs deleted as well.

The information is coming from another source, so the columns may change from day to day, i.e, "TERMINAL NAME" may be in Column L one day but in Column O another day.

Thank you very much for any assistance.
I've attached a sample spreadsheet.

Hello OzGrid Experts!

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?

     ' Sort data
    Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _ 
    , Order2:=xlAscending, Key3:=Range("C2"), Order3:=xlAscending, Header:= _ 
    xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ 
    DataOption1:=xlSortTextAsNumbers, DataOption2:=xlSortTextAsNumbers, _ 
     ' CODE PROVIDED BY "Davc4" (DAVE) via
    Dim Lastcell As Range 
    Dim i As Long 
    Set Lastcell = Cells.Find("*", SearchDirection:=xlPrevious) 
    If Not Lastcell Is Nothing Then 
         'sort the list
        Range(Cells(1, 1), Cells(Lastcell.Row, Lastcell.Column)).Sort _ 
        Cells(1, 1), xlAscending, Header:=xlYes 
         'delete out a previous row if 1st column matches
        For i = Lastcell.Row To 2 Step -1 
            If Cells(i, 1).Value = Cells(i - 1, 1).Value Then 
                Cells(i - 1, 1).EntireRow.Delete 
            End If 
        Next i 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance!!!

I am not familiar with the visual basic, but I need to create a macro that can copy a row of data and paste it into a new worksheet based on criteria in the row.

In the attachment, I need to copy row 3 to the worksheet labeled january if Cell E3 contains a value of 1, copy the same row to the worksheet labeled february if Cell F3 contains a value of 2, copy the same row to the worksheet labeled march if Cell G3 contains a value of 3, etc. This should be done for the other rows as well. Take a look at the sample in the attachment.

Can someone help me out with this application? I would really appreciate it.


I want auto add new rows if column A is equal my request (such as PF)

PF ;AAAAA;AAAAA;ALL;;;;;0.000;0.676;USD;0.000;1.000;;4;0;0;
PF ;AAAAA;AAAAA;1672;1;;;;4.629;23.151;ALL;658.035;3291.030;2006-09-01;57
;2 152;0;
PF ;AAAAA;AAAAA;1673;1;;;;14.131;70.651;ALL;2008.128;10040.072;2006-09-02

will be:

PF ;AAAAA;AAAAA;ALL;;;;;0.000;0.676;USD;0.000;1.000;;4;0;0;

PF ;AAAAA;AAAAA;1672;1;;;;4.629;23.151;ALL;658.035;3291.030;

;2 152;0;
PF ;AAAAA;AAAAA;1673;1;;;;14.131;70.651;ALL;2008.128;10040.072;


Please see attachment.
Need a macro to ask user how many new job to add and add in new rows with same format/formulas and keep sequencing. For example, when user enters 1, 3 row will be added, the sequence will be 6,7,8.
If users enter 3, 9 rows would be added, column shows 6-14.


I am stumped on how to transpose multiple columns to rows based on specific criteria. Here is an example of the data I am working with:

Acct #Rev CodeUnitsCharges10094537034503$0.0010094537034501$605.0010094537037101$0.0010096359034503$0.0010096359034501$355.0010096359037101$0.00

I want it to look like the following:

Acct #Rev CodeUnitsChargesRev CodeUnitsChargesRev CodeUnitsCharges10094537034503$0.004501$605.007101$0.0010096359034503$0.004501$355.007101$0.00

I should note that there is oftentimes more than three rows for the same account number, sometimes it could be as many as 20 rows for the same account.

Please advise!!!! Thanks for any help you can give!

Here is my problem.

I have a spreadsheet with columns A:G populated. Within column A are various
names of dogs. Columns B:G are statistics about the dogs.

What I require is a macro that will delete certain rows based on the
following criteria: In column A there are many duplicate names. I only
require 4 of each name so if there are 5 instances of "Annie" then 1 should
be deleted and if there are only 3 instances of "Bob" then these should all
be deleted as there are not 4.

Thanks in advance

How do I make excel copy a row and then automatically insert new rows that are copies of that row based on a number in one of the cells in the original row.

(ie; if the original row (Row 1),has purchase info about 10 ten pc's, (Quantity in Column B), I need to generate ten rows with the details from Row 1).

I have a large workbook that shares data between two worksheets. I need to copy specific cells from the original worksheet, based on criteria in one of the cells to different cells in the destination worksheet. The in the rows for the two worksheets are not identical, that is why I can't copy the entire row. I need to copy cells from columns B-E and H-I in the origin worksheet, to cells in columns A-F in the destination worksheet all based on the criteria in cell B of the origin worksheet. If possible, after the move I need to delete all duplicates created by the copy operation. I have just started learning VBA and this task is way beyond my current knowledge, any help you can provide is greatly appreciated.

et al,

If this is the wrong group, please point me in the right direction.

What I want to do is choose rows in a spreadsheet based on a criteria
(yes, I know I could filter to get it), but I want to be able to have
them chosen from w/in VBA as I plan to take data in said row and build
and send an e-mail with it.

I'm only looking for the first part, right now. How to choose a row
based on criteria.

Once I have that, I'll probably just write it out to a text file to
make sure it works.

Then, if it shows that it works, I'll go ahead and take out the 'text
file' and replace it w/ building and sending e-mail.

Any help is greatly appreciated.


Preface: I am learning VBA as I go over the last three weeks, so please explain any solutions like i'm a complete idot. Thank you so much in advance for any help.


I want to Delete an entire row based on a criteria in column D (Criteria:= "REMOVAL BLANKET")



I would Like to Delete all Visible Rows from a Flitered Selection of Colum D.

My Current code is:
See ISSUE 1: I need to get the entire row selected to delete. cant seem to scipt it right

' Keyboard Shortcut: Ctrl=g
Dim y As String
Dim X As Integer
Dim ba As String
Dim ba1 As Variant


' Dimension variables.
   y = ActiveCell    ' Change this to True if you want to
   X = 1        ' delete rows 1, 3, 5, and so on.
   I = 1
   xRng = X

   ' Loop once for every row in the selection.
   ba = ActiveCell
   ba1 = ba
   For xCounter = I To 114
y = ActiveCell
       ' If Y is True, then...
              If y <> "REMOVAL BLANKET" Then
I = I + 1
           ' ...delete an entire row of cells.
           GoTo first
       ' Otherwise...
    Range(ActiveCell, ActiveCell).Delete Shift:=xlToLeft  <--- ISSUE 1
           ' ...increment I by one so we can cycle through range.
           If ActiveCell = "Z" Then
           y = ActiveCell
           GoTo first
           If y = "Z" Then
           'Range(ActiveCell, ActiveCell.Offset(columnoffset:=-255)).Select
           GoTo Third

       End If
       End If
       End If

       ' If Y is True, make it False; if Y is False, make it True.


End Sub

No luck finding an answer? You could always try Google.