Auto insert blank new cells

[tra1a]

I am fairly fresh to Macros.

4 columns: B23, C23, D23, E23

Headers: type, vendor, amount, date

data cells

Footer: sums total

Need Macro that inserts a new blank row below most recent row whenever data is recorded in the previously blank row.

Make sense? I know this has been answered before. I am not sure I'm at a point where I know enough to adjust another code.

 

[Peter_SSs]

Welcome to the MrExcel board!

Not sure I have your layout and requirements exactly right but see if this is close.

With a layout like below, notice that my SUM formulas sum to row 28 but that row 28 is hidden. When something is entered in any cell in the range B27:E27, a new row will be added.

I have assumed that there is nothing in column B below the SUM formula.

To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Test.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#00007F">Dim</SPAN> nr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    nr = Range("B" & Rows.Count).End(xlUp).Row - 2<br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Intersect(Target, Rows(nr)) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> WorksheetFunction _<br>            .CountA(Range("B" & nr).Resize(, 4)) > 0 <SPAN style="color:#00007F">Then</SPAN><br>        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>        Rows(nr + 1).Insert<br>        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

Excel Workbook
	B	C	D	E
23	H1	H2	H3	H4
24	2	2	2	2
25	2	2	2	2
26	6	2	2	2
27
29	10	6	6	6
30
Insert Rows

 

[tra1a]

Thanks.

I've copied pasted. Made sure Macros were turned on. Nothing though.

I seriously appreciate the help.

 

[Peter_SSs]

Thanks.

I've copied pasted. Made sure Macros were turned on. Nothing though.

I seriously appreciate the help.

1. Did you ensure the setup was the same as mine with two blank rows between the formula row and the row of data above that?

2. It is possible that your code is not working because 'Events' (eg Worksheet_Change) are disabled. In the VBA window, ensure the Immediate Window is visible (View|Immediate Window) and then on a new line in the Immediate Window, type
Application.EnableEvents=True and press Enter
Now go back to your sheet and try entering something in the next blank row (row 27 it would be for my sheet posted above) again.

 

[tra1a]

Worked. Thanks.

I presume if I'd like to only enter cells and not an entire row I would adjust the range? Or should I set up the a group/family (term is slipping me right now) so I have essentially several worksheets within a worksheet?

It's funny. I thought I was pretty good with Excel and now a whole new world.

Thanks in advance.

--t

 

[Peter_SSs]

I presume if I'd like to only enter cells and not an entire row ..

I did wonder about that. Your thread title indicated that but the following swayed me:

Need Macro that inserts a new blank row ...

Anyway, just replace the first line below with the second one.

Rows(nr + 1).Insert
Range("B" & nr + 1).Resize(, 4).Insert Shift:=xlDown

 

[tra1a]

Thanks again. Actually learning a lot. I'm more of a dissect and learn.

What I've got now:

If I keep a row hidden such as R28 w/ the

Range("B" & nr + 1).Resize(, 4).Insert Shift:=xlDown

The hidden row doesn't shift down. This isn't the end of the world as I could simply block/black out the bottom row.

I'm working on solution too.

 

[Peter_SSs]

The hidden row doesn't shift down.

Yes, sorry, my mistake. However, before making any more suggestions I think I need to know more about your sheet.

If you only want to insert cells, not a complete row, that would indicate that you have other data to the right (or possibly left) of this data that you do not want to interfere with. If that is the case, then isn't a hidden row, particularly if it moves, also going to interfere with that other data?

I was just using the hidden row so that we didn't have to keep adjusting the formulas below to include the new row of cells just added. We could do this without a hidden row.

What are your actual formulas in columns B:E at the bottom of this data?
If they are simple SUM formulas, what is the first row they are summing 23 or 24 or something else?

 

[tra1a]

If you only want to insert cells, not a complete row, that would indicate that you have other data to the right (or possibly left) of this data that you do not want to interfere with. If that is the case, then isn't a hidden row, particularly if it moves, also going to interfere with that other data?

Yes, though I don't believe the data in the columns to the right will get that far.

I actually have several ranges of data.

<table style="width:194px;"><tr><td align="center" style="height:194px;background:url(https://picasaweb.google.com/s/c/transparent_album_background.gif) no-repeat left"><a href="https://picasaweb.google.com/115045190921292221062/August12011?authuser=0&feat=embedwebsite"><img src="https://lh6.googleusercontent.com/-EVYvO7IRLgI/TjdxNMKX__E/AAAAAAAAAKg/QAJK-fJfQJk/s160-c/August12011.jpg" width="160" height="160" style="margin:1px 0 0 4px;"></a></td></tr><tr><td style="text-align:center;font-family:arial,sans-serif;font-size:11px"><a href="https://picasaweb.google.com/115045190921292221062/August12011?authuser=0&feat=embedwebsite" style="color:#4D4D4D;font-weight:bold;text-decoration:none;">August 1, 2011</a></td></tr></table>

I was just using the hidden row so that we didn't have to keep adjusting the formulas below to include the new row of cells just added. We could do this without a hidden row.

I moved the formulas at the bottom. It was redundant.

I'd like to do the same for fixed expenses, income(ing), and debt.

 

[Peter_SSs]

I moved the formulas at the bottom. It was redundant.

I'd like to do the same for fixed expenses, income(ing), and debt.

You apparently removed the formulas at the bottom of one table, but now you want to apply this process to other ranges that do appear to have formulas at the bottom still. And hidden rows in these other areas would impact other data beside them so I think hidden rows are out of the equation.

I don't see this as an easy task. For example, if you need to add a new row of cells to the "Income(ing)" area then on the face of it you would insert a row of 4 cells just above the "total deposits" row. When you insert those 4 cells it will move everything else in those 4 columns down a row and that would result in the "debt" area below becoming disjointed since it would have 4 columns moving down a row and 3 columns not. You have a similar issue on the left where your tables are also not the same width of each other.

Are any of the tables likely to get more columns at any time in the future?

If we get to where we think we might be able to work out how to proceed, it would be very helpful to have your sample data in a form that can be copied to a sheet to test. Unfortunately the Picasa ones can only be looked at and most helpers here are not that keen on manually typing out a heap of data to test on.

My signature block contains some suggested methods for posting copyable screen shots directly in your post. Test them in the Test Here forum. That way, if something goes wrong, you won’t be messing up a main forum.