Excel help please (very simple q I suspect)

jobbingmusician

TIA for reading.

I have a spreadsheet with a long list of client names and associated data.

I want to add 3 blank rows after each client. I'm sure there must be a REALLY easy way to do this, but can anyone tell me what it is?

Thanks folks!

RichardD1970

Right click on the header bar (where it says 1,2,3/A,B,C etc) where you want the new row and click insert.

jobbingmusician

Thank you. But can I insert 3 rows after every client at once?

EchoLocation

Use the following macro.

Sub InsertRows()
For i = ActiveSheet.UsedRange.Rows.Count To 1 Step -1
For j = 0 To 2
ActiveSheet.Rows(i).Insert
Next
Next
End Sub

jobbingmusician

Thank you so much! Now, I hate to ask, but how do I apply this macro to the ss?

(I will experiment tomorrow, when I'm back at work, but have never used a macro before. I'm not totally stupid, though, and will click around until I find the macro tab - Office 2007, IIRC)

Stompa

In an unused column next to your data, add some sequential numbers (so if you have 10 rows, 1 to 10). Below that in the same column add 1.1,2.1,...,10.1. Below that 1.2,2.2,...,10.2, then below that 1.3,2.3,...,10.3. Autofill will let you do that easily:

http://www.get-digital-help.com/2010/01/24/create-number-sequences-in-excel-2007/

Now just sort according to this extra column, so you'll end up with:

1 your data row 1
1.1
1.2
1.3
2 your data row 2
2.1
2.2
2.3
3 your data row 3
etc...

then delete the column to which you've added these numbers.

EchoLocation

First check to see if you have the Developer Tab at the top of your screen (it's usually after the View Tab). If not, click on the Office button and then 'Excel Options'. Select 'Popular ' on the left, then on the right tick the option for 'Show Developer Tab in the Ribbon'. OK.

Click on the Developer Tab then on 'Visual Basic' amongst the Code options.

In the Left Hand Pane you should see your file with an expand/collapse tree.

Expand the project until you see 'ThisWorkBook'.
Right Click on it and select Insert... Module.
You should then see a Module1 appear underneath

Paste the Macro code into the main blank workspace window then close Visual Basic Editor down as normal (don't worry it won't close your Excel workbook as well).

Go back on the worksheet you want to run the macro.
On the Developer Tab click 'Macros' amongst the Code options.
Select the Macro 'InsertRows' listed and then click 'Run'

See here http://grok.lsu.edu/Article.aspx?articleId=538

---

Depending on the length of your data this might take a while. I also haven't switched the screen update off so you will be able to see it running.

If your data is really long you might be waiting some time. I would also advise making a copy of your file beforehand - not because this should mess anything up, but just incase. Perhaps use it on a small test file of a few records first to ensure it does what you want.

jobbingmusician

Lovely, thank you all (especially Echo, as I think this is going to work). I'll update tomorrow, when I've had a chance to try it on the data

jobbingmusician

Oooo, on re-reading, I think Stompa's solution is a lovely Heath Robinson one. Why didn't I think of that!

But I will learn more from Echo's solution, so will try that first. :A

jobbingmusician

EchoLocation wrote: »

First check to see if you have the Developer Tab at the top of your screen (it's usually after the View Tab). If not, click on the Office button and then 'Excel Options'. Select 'Popular ' on the left, then on the right tick the option for 'Show Developer Tab in the Ribbon'. OK.

Click on the Developer Tab then on 'Visual Basic' amongst the Code options.

In the Left Hand Pane you should see your file with an expand/collapse tree.

Expand the project until you see 'ThisWorkBook'.
Right Click on it and select Insert... Module.
You should then see a Module1 appear underneath

Paste the Macro code into the main blank workspace window then close Visual Basic Editor down as normal (don't worry it won't close your Excel workbook as well).

Go back on the worksheet you want to run the macro.
On the Developer Tab click 'Macros' amongst the Code options.
Select the Macro 'InsertRows' listed and then click 'Run'

See here http://grok.lsu.edu/Article.aspx?articleId=538

---

Depending on the length of your data this might take a while. I also haven't switched the screen update off so you will be able to see it running.

If your data is really long you might be waiting some time. I would also advise making a copy of your file beforehand - not because this should mess anything up, but just incase. Perhaps use it on a small test file of a few records first to ensure it does what you want.

woooooooooooooo! It worked! MANY thanks for showing me more than I have ever seen before about the potential of Excel. :D:D

EchoLocation

jobbingmusician wrote: »

woooooooooooooo! It worked! MANY thanks for showing me more than I have ever seen before about the potential of Excel. :D:D

No problem - pretty much anything is possible.

And don't worry, I must admit I didn't think of Stompa's solution either!