EXCEL-Insert rows below based on criteria

September 20, 2010 at 13:28:13
Specs: Windows Vista
Hi-
I'm trying to develop a macro that will look at the values in column C (say C4=10), then based upon that value in C4, enter a given number of rows below C4 (say 5 rows), without deleting any information in the succeeding rows. Likewise, the next row (C5=15), will need 8 rows below that. In all, there are a given number of values for column C, each with a given number of rows needed for each column C value. In essence, a value of 10 needs 9 rows, a value of 15 needs 8 row, etc. Currently, I'm using a Command Click button and ideally would like to incorporate this macro within that function. Any help would be great, Thanks

See More: EXCEL-Insert rows below based on criteria

Report •


#1
September 20, 2010 at 14:41:29
Some things don't quite make sense.

First you said: (say C4=10), then based upon that value in C4, enter a given number of rows below C4 (say 5 rows)

Then you said "a value of 10 needs 9 rows"

Which is it? 5 or 9

Then you said "(C5=15), will need 8 rows below that".

However, C5 isn't there anymore, is it? Wouldn't C5 now be down however many rows you inserted after C4?

Are you looping through a range in Column C needing to find the cell that used to be below the one you just inserted after?

In other words, if 10 means 5 rows and 15 means 8 rows then...

If C4 = 10 and C5 = 15:

Insert 5 rows after C4 then insert 8 rows after C10, which now contains the value that was in C5.


Report •

#2
September 20, 2010 at 14:46:04
Hi-
Yes, I guess I was in a rush composing the message, but yes a value of 10 will require 5 rows and true the value of 15 that was in c5, will now be located in c10, Thanks-

Report •

#3
September 21, 2010 at 09:55:41
Maybe this will clarify a little...
The macro that will look at the values in column C (say C4=10), then based upon that value in C4, insert a given number of rows below C4 (say 5 rows) and fill in the newly inserted 5 rows with data, without deleting any information in the succeeding rows. Likewise, the next row (C5=15), will need 8 rows inserted below and fill in the newly inserted 8 rows with data.

In essence, there only exist 5 possible values for column C (10,15,20,25,30) and a value of 10 needs 5 rows, a value of 15 needs 8 rows, a value of 20 needs 9 rows, a value of 25 needs 7 rows and a value of 30 needs 6 rows. Equally, the data that needs to be entered into the newly inserted rows remains constant for each column C value, such as if C4=10, then 5 rows are inserted below and in column C5=apple, D5=sour, C6=peach, D6=yummy, C7=orange, D7=orange, C8=pear, D8=soft, C9=grapes, D9=green.


Report •

Related Solutions

#4
September 21, 2010 at 11:24:54
It would help us decide what to offer if we knew your level of VBA expertise.

Providing code to insert the rows based on the value in certain cells is fairly easy, but providing code to populate the new rows with data is dependent on where the data comes from and where it is going to - is it copied from another sheet, is it hard coded within the macro, is it calculated based on which row it's going in, etc.

Since there are numerous ways to populate cells with data via VBA, simply giving us examples of words for one given value doesn't help us help you.

If we provide the code to insert the rows, can you write the code that will populate the cells with data?

If not, then you have to be very specific about where that data is coming from and going to.

Keep in mind that we can't see your spreadsheet from where we're sitting, nor can we read minds.

VBA is nothing more than list of instructions for Excel to execute. It has to be told what to do on a step by step basis. It can't guess what data goes where, it has to be told. Without specific, detailed information, we can't provide specific, detailed code.


Report •

#5
September 21, 2010 at 11:35:54
Hi DerbyDad-
Yes, I understand. My VB programming experience is basically a beginner, though my Excel experience would probably be more intermediate. In regards to where the new data to populate within the newly inserted rows will come from, I would prefer to just hard code it in, which could possibly be another challenge. In my mind, I was trying to use a series of IF statements to do all teh mentioned above, but with little success. Any assistance would be great. Thanks-James

Report •

#6
September 21, 2010 at 22:26:23
First, set up new sheet with the sour apple, green grapes data in A1:B9. This must be a different sheet than the one you are inserting rows into.

    A     B
1 Apple  Sour
2 Peach  Yummy
3 Etc.   Etc.

Name the sheet Row Data. You can hide the sheet if you want to.

The following code will insert new rows based on the values in C4 through the next cell that doesn't contain 10, 15, 20, 25 or 30. If the code doesn't find one of those values, it will Exit.

After it inserts a set of rows, it will then "copy" the data from the Row Data sheet, starting in A1, and copying the same number of pieces of data as rows it inserted.

In other words, for a value of 15, it will insert 8 rows after the cell in which it found 15, then copy A1:B8 to the new rows, Columns C & D.

The main reason for using a table as suggested is that it is easy to change the data in one place and not have to modify the code.

Option Explicit
Sub InsertRowsAndData()
Dim rw, new_rw, num_rows As Integer
'Initialize Row Variable
 rw = 4
Next_Insert:
'Set num_rows Variable Based On Value In C(rw)
    Select Case Range("C" & rw)
     Case 10
      num_rows = 5
     Case 15
      num_rows = 8
     Case 20
      num_rows = 9
     Case 25
      num_rows = 7
     Case 30
      num_rows = 6
     Case Else
      Exit Sub
    End Select
'Insert The Correct Number Of Rows After C(rw)"
         Range("C" & rw + 1 & _
              ":C" & rw + num_rows).Insert shift:=xlDown
'Loop Through New Rows, Getting Data From Row "Data Sheet"
          For new_rw = 1 To num_rows
             Range("C" & rw + new_rw) = _
                Sheets("Row Data").Range("A" & new_rw)
             Range("D" & rw + new_rw) = _
                Sheets("Row Data").Range("B" & new_rw)
          Next
'Increment rw Variable To Find Next value To Test
      rw = rw + num_rows + 1
    GoTo Next_Insert
End Sub


Report •

#7
September 23, 2010 at 14:19:17
In your PM you said that it was only inserting rows into Column C.

To fix that, change this line:

       Range("C" & rw + 1 & _
            ":C" & rw + num_rows).Insert shift:=xlDown

to be:

       Range("C" & rw + 1 & _
            ":C" & rw + num_rows).EntireRow.Insert shift:=xlDown


Report •

#8
September 23, 2010 at 14:55:19
Thanks DerbyDad-That works much better, after I saw the correction, it all made sense. One more follow up if you got time. Say that the "case 10" that inserts 5 rows below needs to have values 1, 2, 3, 4, 5 inserted in that cloumn (C) and A, B, C, D, E in column D, and some more stuff for columns E,F,G (ie-TT1, TT2, TT3, TT4, TT4 in column E, SS1, SS2, SS3, SS4, SS5 in column F, DD1, DD2, DD3, DD4, DD4 in column G) Equally, for "case 15" that inserts 8 rows needs, values 5,6,7,8,9,10,11,12 inserted in that column (C) and F,G,H,I,J,K,L,M in column D, and some more stuff for columns E,F,G (same number of stuff, ie-8, jsut liek teh previous example). The same pattern follows suit for the other cases.

Report •

#9
September 23, 2010 at 16:22:34
To be honest, this is getting a bit tiresome.

You started with a post asking about inserting Blank lines.

Then you added the additional requirement that certain values be inserted in Columns C & D in the new rows

Now you're asking that values be inserted in Columns E, F and G also.

Why did you leave out those requirements in your earlier posts? Why do I feel that if I add these features to the code, you'll just ask for something else?

Try this:

In Response # 4 I offered the suggestion of setting up a table for the data that will populate Columns C & D. I am assuming you tried that suggestion because you said the code worked for you.

I suggest that you expand that table for your additional data and replicate the lines of code that copy the data to C & D to also copy the new data to E, F and G. It should be pretty straight forward.

Open the VBA editor and make the window smaller so you can see your spreadsheet behind it.

Place you cursor anywhere in the code and press F8 over and over again to single step through the code. Watch as it inserts lines and places data from the table into the cells. Once you've done that, you should have no problem modifying the code to fit you new requirements.

This line copies data from Column A of the table to Column C of the sheet with the sheet with the new rows. Replicate it for your other data and change the "source" and "destination" column letters:

             Range("C" & rw + new_rw) = _
                Sheets("Row Data").Range("A" & new_rw)

Sure, I could do it for you, but if you are going to be using this code, you need to understand how it works. I'm not going to be there to maintain it for you.



Report •


Ask Question