Solved: Macro to Insert Entire Rows

[Champers]

Hi all,

I am trying to put a Macro together that first of all goes striaght to "A9" which will allways hold a value and goes down from "A9" untill there it finds an empty cell.

I then want the macro to insert 5 new "entire" rows, however the macro I am using seems to only enter new rows in column A and not accross the sheet. therefore the data below where the lines are being inserted fr column "A2" moves down but the data in the below cells for columns c onwards does not.

I am not sure exactly why this is happening and what I can do to fix it.

The code I have put together is:


Sub Insertlines()
'
' Insertlines Macro
'
'
Range("A9").Select
Do While ActiveCell > 0
lineno = lineno + 1
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(1, 0).Rows("1:5").Insert Shift:=xlDown
Loop
End Sub

If anybody can help, it would be much appreciated.


Thanks

Champers

[Bob Phillips]

The code doesn't do what you describe, but to overcome your problem



Sub Insertlines()
Dim lineno As Long

Range("A9").Select
Do While ActiveCell > 0
lineno = lineno + 1
ActiveCell.Offset(1, 0).Select
Rows(ActiveCell.Row).Resize(5).Insert Shift:=xlDown
Loop
End Sub

[Champers]

I have tried this bit of coding and it does insert the 5 new rows, however it enters them in between "A9" and the next bit of data rather than finding the next empty cell in column "A" and inserting 5 new rows from that point.

[GTO]

Greetings to all,

Maybe:

Range("A9").End(xlDown).Offset(1).Resize(5).EntireRow.Insert xlDown


Ya'll have a great day,

Mark

[Bob Phillips]

I have tried this bit of coding and it does insert the 5 new rows, however it enters them in between "A9" and the next bit of data rather than finding the next empty cell in column "A" and inserting 5 new rows from that point.

This is mainly your code, I just changed it to insert 5 rows not 5 cells. As I said your code didn't do what you described, so you should clarify what is wanted first.

[Champers]

Sorry it was confusing. I am not great with getting anything to work in VBA.

The first bit of code I entered worked and it found the next available empty cell in column "A".

The next part when I wanted it to insert the five rows from that Activecell didn't work.

I should probably have written this in my original message.

Again, thanks for your help.

[GTO]

Sorry Bob, hit the rack for me time; thought I had it, "thinker" must be "thunked" for the day.

A great one to you and yours,

Mark

[Bob Phillips]

Sorry it was confusing. I am not great with getting anything to work in VBA.

The first bit of code I entered worked and it found the next available empty cell in column "A".

The next part when I wanted it to insert the five rows from that Activecell didn't work.

I should probably have written this in my original message.

Again, thanks for your help.

You say it finds the first empty cell, but I am sorry, it doesn't. The opposite it fact, it finds the first non-empty cell, walks down one row, then tries to insert one row below that.

If you want to find the first non-empty cell, and insert 5 rows there, Mark's code does exactly that.

[Zack Barresse]

I'm going to pose a question here, only due to my personal beliefs. Why do you want to do this? IMHO if you have built a spreadsheet that you need to Insert/Delete from, you haven't done a well enough job building your spreadsheet. There are exceptions to every rule of course.

[mdmackillop]

Hi Zack
I have a few sheets where I keep Summary formulae below a table, and insert rows as required for updating. I suppose I could start with the Total row in row 8000; I'll give it due consideration next time!
Regards
Malcolm

PS I'm happy to see Ted's APB unearthed you.

[Zack Barresse]

Yes, Ted has that affect. ;)

I guess I should have emphasized in my opinion that there are most definitely circumstances where it's not bad. It's just something I seriously look at when designing a spreadsheet for use. :)

[Champers]

It is due to the formulae totals being below a table. The information has to be entered above and the reason I am trying to build the Macro to do this is due to the fact I am trying to create a sheet somebody can use without any excel knowledge.

Therefore instead of them running out of lines in the table and then inserting themselves, they can click the insert rows button, and it does it for them.

It's not ideal and I am sure there are better ways to solve these isuues, however I am learning myself as I go along.

[Bob Phillips]

So have you tried Mark's code. If so, what is wrong with that?

[Champers]

Marks code works perfectly to insert 5 entire rows after "A9".

However what I am trying to get it to do is scroll through column A untill it finds the next empty cell and insert 5 lines there.

A B C
Row 1 1 2 3
Row 2 1 2 3
Row 3 1 2 3
Row 4 1 2 3
Row 5
Row 6 4 8 12 (totals column)

Therefore it should see in column A that there are values in Rows 1, 2, 3 and 4 and not in 5. the Active cell would then be A5 and I wanted it then to insert 5 entire rows between Rows 5 and 6. Therefore the totals column would become Row 11.

[Bob Phillips]

That is because you started by telling us that you wanted to go from A9 down, so Mrak supplied code foir that. Now you are saying A1 down, so just adjust the code



Range("A1").End(xlDown).Offset(1).Resize(5).EntireRow.Insert xlDown

[Aussiebear]

I might be reading this wrong but I believe the OP is asking for 5 new rows to be inserted after the first blank cell so should the code read;



Range("A9").End(xlDown).Offset(2).Resize(5).EntireRow.Insert xlDown

[Phelony]

So is the issue that your data begins in cell A9, then you need to find the first empty cell below that position and insert 5 rows there? :dunno

[Champers]

Aussiebear that works perfectly, however is it possible to limit the number of rows being inserted.

It seems to insert 91 rows.

[GTO]

Could you check or post your code (or wb example)? Took me a couple of spins (my fault, laziness not filling the rows), but Ted's (Aussiebear's) code only inserts (the correct) 5 rows (nicely spotted BTW!) for me.

Mark

[Champers]

I had left a bit of the previous code in there , however I have taken that out now and it works perfectly.

Thanks for all your help, and apologies for the confusion.

[Aussiebear]

That's good to hear, as I was staggered to read that it inserted 91 lines. This is a good learning example for everyone wanting to post questions here in this forum. When having trouble to explain what it is that we are after, try to attach a sample workbook with a before and after display of the data. Takes nearly all the confusion out of the request.