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

 

[Greg Truby]

(a) You can tighten up the code quite a bit by making the following type of edits...

Sub Macro1()
 
    ActiveSheet.Unprotect
 
    Columns("G:H").EntireColumn.Hidden = (Range("E2").Value = 0)
    Columns("B").EntireColumn.Hidden = (Range("E1").Value = 0)
    Columns("I").EntireColumn.Hidden = (Range("I4").Value = 0)
 
    If Range("J4").Value = 0 Then
       Columns("J").EntireColumn.Hidden = True
    Else
        Columns("J").EntireColumn.Hidden = False
    End If
    
    If Range("K4").Value = 0 Then
        Columns("K").EntireColumn.Hidden = True
    Else
        Columns("K").EntireColumn.Hidden = False
    End If
'//   ...snip...

(b) I would NOT use the SELECTIONCHANGE event handler for ANY type of operation that was going to be inserting rows and/or copying data. Everytime you selected a different cell your code would execute.

(c) Can you show us your own attempt at coding up the insertion logic?

 

[steveb9990]

Hi Greg,

Many thanks for your response. I'll clean up my existing code as soon as I log off.

With regard to point (b) - With how, what and where the data is inputted, I need the worksheet to execute the code with every click.

With regard to point (c) - They were just my feeble attempts at the assembly of the masses of confusing code snippets found via Google. So that I didn't confuse myself ... anymore than I already am! ... I deleted everything.

I've had a few more thoughts and realisations whilst fumbling around, so I'll try to get them assembled ... logically ... in writing ... and will post them here. Meanwhile, if anyone has any suggestions ...... ?

 

[Greg Truby]

With regard to point (b) - With how, what and where the data is inputted, I need the worksheet to execute the code with every click.

I remain skeptical; but you know your process best so we'll assume you really do. Go ahead and suss out the logic for what you need inserted, etc., then we can take a look at an additional layer of logic that's going to be needed to make sure your code does not continue to insert again and again when it no longer should.

 

[steveb9990]

Hi Greg,

I'm tearing my hair out here! Well, that's just a figure speech really ... I'm bald!

I've Googled some more ... I've purchased e-books ... I've Googled yet again ... BUT ... just when I think I've found an answer ... it fails to work!

HELP! Please? (Insert pleading smiley here)

What I'm looking for is that when cell E17 is populated with a number, row 17 is copied and inserted below, then all data, excluding the formulas, is deleted from the new row. Then, when cell E18 is populated, row 18 is copied and inserted below, etc, etc.

There, how easy was that? If only it was as easy for me to figure out as it is to write!

The alternative to the above is to add 10 rows (with copy/paste/delete as mentioned above) when E17 is populated and repeat this action at E27, then E37, so on and so forth.

Based upon what I have read, it is most certainly possible, but I am now really, REALLY confused by the plethora of information!

Please, please, PLEEEEASE can someone point a VB newbie in the right direction?

 

[Greg Truby]

What I'm looking for is that when cell E17 is populated with a number, row 17 is copied and inserted below, then all data, excluding the formulas, is deleted from the new row. Then, when cell E18 is populated, row 18 is copied and inserted below, etc, etc.

Your logic would create a loop that would fill the worksheet.

E17 is filled so row 17 copied and inserted to row 18.
The copy & insert would result in cell E18 being filled.
This results in row 18 getting copied and inserted to row 19
The copy & insert would result in cell E19 being filled
This results in row 19 getting copied and inserted to row 20
... and so on down through row 1048576.

Also, what does "row" mean? A17:XFD17?

 

[steveb9990]

E17 is filled so row 17 copied and inserted to row 18.
The copy & insert would result in cell E18 being filled.

I would be looking for row 18 to be filled rather than cell E18.

Also, what does "row" mean? A17:XFD17?

The entire row (I have absolutely no idea if column XFD is the last!).

I presumed that it would be easier to copy an entire row rather than selecting a range? If not, the current number of columns being used is 22 (B:W), but this may well grow.

As it stands, I would be looking for a macro to be triggered when E17 is populated. This would result in B17:W17 being copied and inserted below (i.e. into B18:W18), then the manually inputted data in B18:W18 would be deleted whilst retaining all formulas. The macro would then be triggered again when E18 is populated, resulting in B18:W18 being copied, inserted, etc, etc.

The cells being copied could also be a fixed range, i.e., cells B17:W17 could be copied every time rather than copying B17:W17, then B18:B18, then B19:W19, etc, etc, but the copy/insert trigger would always move down by one cell, starting with E17.

I hope this makes sense?

 

[Greg Truby]

Do you understand about trapping events and programming event handlers?

 

[steveb9990]

Do you understand about trapping events and programming event handlers?

 

[keewhan]

hi guys...just thought i'd jump in and ask a quick question...

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?

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?

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.

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.

Its always a bit easier with some more specifics!

Apologies if I have misunderstood your post!!

 

[Greg Truby]

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?