VBA to insert row / colum

[philhowell]

Hi guy,

can't believe its been a year since i last posted on here....

i have been asked to write 4 seperate VBA modules;

1. insert a row above the cell selected
2. insert a row below the cell selected
3. insert a colum to the left of the cell selected
4. insert a colum to the right of the cell selected


any help is grrr8
Phil

 

[steveb9990]

Actually, I've been running under the assumption that you're using Excel 2003 or lower. However we may have a relatively simple solution if you're running Excel 2007 or better. Which version of Excel are you running?

Hello again Greg,

I'm running 2007

 

[steveb9990]

Hi Keewhan,

You seemed to have described an input spreadheet which is used by entering data manually into a set of cells and there that are formulas based on those input that when completed you then want to automatically copy down the input, and in effect reset the cells.

Is this correct? If so is this row that is being entered into the last row in the range of data?

100% correct.

I presume that you dont want it to automatically add a new row every time a manual entry is made if there are multiple fields to be completed?Maybe there is a cell that is the 'final' input?

100% correct again.

If so can you provide the cells (or columns) on each row that contain the manual input cells, and the cell that you would like to trigger the new row creation.

The manual input cells are C17 to H17 inclusive. The trigger cell could actually be E, F or G17 as all three are required inputs.

We can write code that will only react to the 'final' cell being completed that will take this last line of data (with the formulas), save the values entered in that row using paste special => values, but at the same time copies the row that had all of the formulas into the next row, becoming the new row for input, and the cells required for manual entry are cleared out.

This sounds exactly like what I am looking for, but the plethora of code I've found whilst searching Google, etc, has only served to confuse me!

 

[steveb9990]

Hi again,

Just in case it makes a difference (which I doubt) I thought that I would let you know that E and F17 are both numerical inputs, whilst G17 is a text input.

 

[Greg Truby]

Let's try an experiment -- set up a worksheet like so:

<!-- Please do not remove this header --><!-- Table easily created from Excel with ASAP Utilities (http://www.asap-utilities.com) --><TABLE border=1 cellSpacing=0 borderColor=#c0c0c0 borderColorDark=#ffffff><TBODY><TR><TD style="WHITE-SPACE: nowrap" bgColor=#4f81bd height=26 vAlign=bottom width=64>Input 1</TD><TD style="WHITE-SPACE: nowrap" bgColor=#4f81bd height=26 vAlign=bottom width=64>Input 2</TD><TD style="WHITE-SPACE: nowrap" bgColor=#4f81bd height=26 vAlign=bottom width=63>Calc 1</TD><TD style="WHITE-SPACE: nowrap" bgColor=#4f81bd height=26 vAlign=bottom width=63>Calc 2</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#b8cce4 height=26 vAlign=bottom width=64 align=right>5</TD><TD style="WHITE-SPACE: nowrap" bgColor=#b8cce4 height=26 vAlign=bottom width=64 align=right>50</TD><TD style="WHITE-SPACE: nowrap" bgColor=#b8cce4 height=26 vAlign=bottom width=63 align=right>=A2*B2</TD><TD style="WHITE-SPACE: nowrap" bgColor=#b8cce4 height=26 vAlign=bottom width=63>=C2& " text"</TD></TR><TR><TD style="WHITE-SPACE: nowrap" bgColor=#dbe5f1 height=25 vAlign=bottom width=64 align=right>4</TD><TD style="WHITE-SPACE: nowrap" bgColor=#dbe5f1 height=25 vAlign=bottom width=64 align=right>4</TD><TD style="WHITE-SPACE: nowrap" bgColor=#dbe5f1 height=25 vAlign=bottom width=63 align=right>=A3*B3</TD><TD style="WHITE-SPACE: nowrap" bgColor=#dbe5f1 height=25 vAlign=bottom width=63>=C3& " text"</TD></TR></TBODY></TABLE>

With Column C & D being simple formulas.

Now click inside the data and hit Ctrl+T (to convert to a table). Now try clicking on cell D3 and then hit your TAB key - you'll wrap down to the next line and you'll note that a new row is added to the table and any formulas are carried down. Is this essentially the kind of behavior you would like?

If so, then all we need to address is user flow through the workbook (i.e. we don't really want the user to have to TAB clear across through column W before we get a new line). But programming that is a bit simpler than fussing with all the copying and pasting, etc and we have the added benefit that if someone disables macros Excel will still continue to copy down our formulas and formatting for us.

 

[keewhan]

first off thats a great idea by Greg and to be honest if it could be done that way for all of the reasons that he has also mentioned it could make it much easier for you...



If not see if this helps do what you need...i have tried to comment it as clearly as possible but in essence it works out the trigger columns and then runs 2 checks.

1. Is the column of the activecell one of the trigger columns (E,F,G)?
2. Are all 3 required cells on the row of the activecell complete?

if it can answer yes to both of those arguments it will copy the entire row and paste it one row below

It will then work out what the new row is and clear the contents of C:H of that row

Sub Conditional_Copy_Insert()
' Counts the first of the trigger columns based on starting with Column E
Trig_Col_1 = Range("A:E").Columns.Count
Trig_Col_2 = Trig_Col_1 + 1
Trig_Col_3 = Trig_Col_2 + 1
' Test the cell to see if it is in a trigger column to trigger the change
' Checks to see if the values on the active row in all 3 triggers columns are complete
' Copies the active row and inserts one row below
Act_Col = ActiveCell.Column
    Act_Row = ActiveCell.Row
 
    If Act_Col = Trig_Col_1 _
        Or Act_Col = Trig_Col_2 _
            Or Act_Col = Trig_Col_3 Then
 
        If Cells(Act_Row, Trig_Col_1).Value <> "" _
            And Cells(Act_Row, Trig_Col_2).Value <> "" _
                And Cells(Act_Row, Trig_Col_3).Value <> "" Then
 
        ActiveCell.EntireRow.Copy
            ActiveCell.Offset(1, 0).EntireRow.Select
                Selection.Insert Shift:=xlDown
 
' Set the Row Number of the newly created row to New_Row variable
            New_Row = ActiveCell.Row
' Clears contents of manual input ranges on the new row
            Range("C" & New_Row & ":H" & New_Row).ClearContents
 
        End If
    End If
 
 
End Sub

note that i have not specifically written this to work on the last row (another feature built into the Table approach)

What this means that if you change values in a row that is not the last and it can meet the 2 arguments it will do the copy / insert

If you want to add the arguement that the row that is being changed must be the last row that's fairly simple just let me know...

Hopefully it works out for you...let me know if you need any more help...

 

[steveb9990]

Hi Greg,

Is this essentially the kind of behavior you would like?

It most certainly is! And I've learned something new already!

Hi Keewhan,

note that i have not specifically written this to work on the last row

How do you go about stipulating which row is currently the last row. In my case the first row to be stipulated as the last row is row 17, but it could also be any of the rows from row 9 to row 17. After that it's the new row.

I presume that a few SUM formulas which are resident below the last row will not affect anything? They currently reside in row 19.

If you want to add the arguement that the row that is being changed must be the last row that's fairly simple just let me know...

I guess that this is required?


I have to say that I'm in awe of you guys. I simply don't have a clue where to start and I most certainly wouldn't have come anywhere near close to what you, Keewhan, have already posted, so I would like to thank the both of you right now, so ...

Thank you!

Keewhan,

There are a couple of other things I have to ask (forgive me for being a numpty newbie!). Would the routine be placed in the active worksheet, underneath my existing routine?

 

[keewhan]

Hi Steve

Yes this code would be placed in the active worksheet along with your other code.

On the last row issue...we can get the code to work however you like...the 3 scenarios I can see are below...the first is normal / standard behaviour...2 & 3 you need top decide what you want to happen

1. Change the last row in the data table. On completion a new row is created and becomes the new last row for input - Standard Behaviour
2. Change any row in the data above the 'last row'. On completion a new row is created directly below the line that was changes. (i.e. if you change row 9 and new row will be created below the row being changed (row 10). All other existing rows get moved down one row
3. Change any row in the data above the 'last row'. On completion a new row is created at the end of the data list. All other existing rows stay where they are

For the question on your sums at the bottom of the data...they should stay intact, but wee could add something in to make sure that they always refer to the whole range

It might be easier to mail me an extract of the data if you can?

keewhan[at]gmail.com

Please let me know what option you want to take...if any?!!

 

[Greg Truby]

Steve,

I'm going to bow out and let you and keewhan explore this code-driven solution for the row insertion. For a couple of reasons it would be my second choice with the table-driven solution being my first. But you seem to be leaning to the code-driven so I'll let you chaps have fun exploring that path. (I'm not being sarcastic here, exploring new paths and learning is very fun.)

One point I would mention if you're going to go down that path. Make good use of named ranges. You should name any trigger columns as well as your overall data block. This makes your solution robust against layout changes (the most common kind of change). If someone inserts a column or moves a column from here to there and your solution is based on named ranges, you're fine. If you've coded using "magic numbers", i.e. the trigger columns are 4, 5 & 6, then your code shatters into little pieces at the first puff of a layout change.

Also, I'd probably make use of the .SpecialCells() function to clear only those cells that do not have formulae. Again this makes the solution robust against changes in the positions of cells containing constants versus formulae. Enjoy the discoveries lads.

 

[steveb9990]

Hi Greg,

Apologies for not getting back to you sooner.

Keewhan is proving to be beyond inspirational. My 'problem' hasn't been 100% solved, but we are most certainly getting there. However, the main thing is that Keewhan's input has really given me a push .... a push to learn more!

Anyway, I'd just like to thank you for your prior input and wonder if you will ever come back to add some meat to the bones you threw into the melting pot?

 

[Big_A]

Hi everyone,
I'm newbie in macro excel
I'm currently use excel 2007

I have question :

[FONT=&quot]how VBA automatically insert a row on every 9000 rows start from Range "B2"? For example: if i doing insert row manually, i must go to Range "B9001" (Count data from B2 to B9001 is 9000) and insert new row below Range "B9002"
[/FONT]
[FONT=&quot]I have a very large database that requires a row be inserted on every 9000 rows. Can someone help with the VBA?[/FONT]