Insert 3 rows

[Murph]

Hi team
My spreadsheet has a up to 10 groups of data, each set of data numbered from
1 up to 24.
I want to split the groups of data, inserting 3 blank rows between each set
and past the header row in the third row inserted.
Thanks for any assistance in pointing me to similar codes or the solution.
Cheer!
Murph

 

[Udo]

I'd do it with VBA:
make sure the cursor is located in the first data group. Then first go
to the last column of that group (Selection.End(xlToRight).Select).
Make the cell in the next column the active cell (ActiveCell.Offset(0,
1).Range("A1").Select). Insert three columns (code is like
ActiveSheet.Range("g1:h1").EntireColumn.Select and then in the next
code line Selection.Insert Shift:=xlToRight). After you have checked
out all of the horizontal data groups you move to the vertical groups.
First of all you have to go back to the first data group and go to the
last row ( 2 possibilities: Selection.End(xlDown).Select or
ActiveCell.SpecialCells(xlLastCell).Select) and insert there three
rows according to the previous example.

Good luck

 

[Sandy Mann]

Murph,

My spreadsheet has a up to 10 groups of data, each set of data numbered
from 1 up to 24.

I assume that each group of data can have different numbers of sets of data
, ie dfferent amount of rows

If that is the case how can XL recognise when a new group begins? In my
example I assume that each new group starts with the word "Name" in column
A, is 5 columns wide and the header row is in Row 1

If Udo's solution is not what you are looking for then perhaps something
like:

Sub TripleInsert()
Dim EndRow As Long
Dim x As Long

EndRow = Cells(Rows.Count, 1).End(xlUp).Row

For x = EndRow To 3 Step -1
If Left(Cells(x, 1).Value, 4) = "Name" Then
Cells(x, 1).Resize(3, 1).EntireRow.Insert
Range(Cells(x + 2, 1), Cells(x + 2, 5)).Value = _
Range("A1:E1").Value
End If
Next x
End Sub


If you want soemthing different then post back giving more details.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Murph]

Thanks Udo and Sandy for the response and I am sure you can come up with the simple method as I have seen code that deletes but I am unable to adapt it.

The spreadsheet has data in 80 columns across that is linked across each row.
I have tried to simplify the example and compact it into this reply. However the copy has not shown the numeric value is in column A and the alpha reference is in column B and the data continues across the sheet.
Starting Cell A3 1 A Continuing Cell A14 1 N Continuing Cell A22 1 W
2 B 2 O 2 X
3 C 3 P 3 Y
4 D 4 Q 4 Z
5 E 5 R 5 AA
6 F 6 S 6 AB
Insert 3 rows 1 G 7 T 7 AC
2 H 8 U 8 AD
3 I 9 V 9 AE
4 J Insert 3 rows Insert 3 rows
5 K and so on
6 L
7 M
Insert 3 rows


I hope that makes some sense!

 

[Murph]

I just looked at the post trying to explain the query and the format has not come across so I will try a verbal explanation.
Cell A2 to Cell AK2 contain the header row, Row 1 is blank and left for further entry.
Cell A3 has the first case of a set of data across to AK3.
Cell A3 will always have a "1", A4 will always be "2" and the series of that set of data could be up to "24" which will Cell A26. If there are only 6 rows for that series of data that would be cell A8. In that case CellA9 would start a new series and the number in CellA9 would be the beginning of a new series starting with "1".
In the case of the next series I want to insert 3 rows before the "1", Past the header over the new series and go on down the page until up to 10 series of data are separated and given headings above their specific case.
One way I thought I could achieve the result was looking in each row from A4 and sutracting the previous cell from that cell. If the result is a negative insert the 3 entire rows. Alas I fail to get anything happening.
Hope that fixes the explanation and you are able to ignore previous mail.
Thanks for any help!

 

[Sandy Mann]

Murph,

Try this code on a COPY of your spreadsheet. I assumed that the "1" was a figure 1 not a text representation of a 1. If it is text then enclose the 1 in the If/Then line in quotes .

Sub TripleInsert()
Dim EndRow As Long
Dim x As Long

EndRow = Cells(Rows.Count, 1).End(xlUp).Row

For x = EndRow To 4 Step -1
If Cells(x, 1).Value = 1 Then
Cells(x, 1).Resize(3, 1).EntireRow.Insert
Range(Cells(x + 2, 1), Cells(x + 2, 37)).Value = _
Range("A2:AK2").Value
End If
Next x
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


I just looked at the post trying to explain the query and the format has not come across so I will try a verbal explanation.
Cell A2 to Cell AK2 contain the header row, Row 1 is blank and left for further entry.
Cell A3 has the first case of a set of data across to AK3.
Cell A3 will always have a "1", A4 will always be "2" and the series of that set of data could be up to "24" which will Cell A26. If there are only 6 rows for that series of data that would be cell A8. In that case CellA9 would start a new series and the number in CellA9 would be the beginning of a new series starting with "1".
In the case of the next series I want to insert 3 rows before the "1", Past the header over the new series and go on down the page until up to 10 series of data are separated and given headings above their specific case.
One way I thought I could achieve the result was looking in each row from A4 and sutracting the previous cell from that cell. If the result is a negative insert the 3 entire rows. Alas I fail to get anything happening.
Hope that fixes the explanation and you are able to ignore previous mail.
Thanks for any help!

 

[Sandy Mann]

I also assumed that 1 was the only piece of data in the start of a set, if not replace the 1 in the If/Then line with the full data entry, enclosed in quotes if it is text.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


Murph,

Try this code on a COPY of your spreadsheet. I assumed that the "1" was a figure 1 not a text representation of a 1. If it is text then enclose the 1 in the If/Then line in quotes .

Sub TripleInsert()
Dim EndRow As Long
Dim x As Long

EndRow = Cells(Rows.Count, 1).End(xlUp).Row

For x = EndRow To 4 Step -1
If Cells(x, 1).Value = 1 Then
Cells(x, 1).Resize(3, 1).EntireRow.Insert
Range(Cells(x + 2, 1), Cells(x + 2, 37)).Value = _
Range("A2:AK2").Value
End If
Next x
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


I just looked at the post trying to explain the query and the format has not come across so I will try a verbal explanation.
Cell A2 to Cell AK2 contain the header row, Row 1 is blank and left for further entry.
Cell A3 has the first case of a set of data across to AK3.
Cell A3 will always have a "1", A4 will always be "2" and the series of that set of data could be up to "24" which will Cell A26. If there are only 6 rows for that series of data that would be cell A8. In that case CellA9 would start a new series and the number in CellA9 would be the beginning of a new series starting with "1".
In the case of the next series I want to insert 3 rows before the "1", Past the header over the new series and go on down the page until up to 10 series of data are separated and given headings above their specific case.
One way I thought I could achieve the result was looking in each row from A4 and sutracting the previous cell from that cell. If the result is a negative insert the 3 entire rows. Alas I fail to get anything happening.
Hope that fixes the explanation and you are able to ignore previous mail.
Thanks for any help!

 

[Murph]

Sandy
You have nailed it!
Thank you for the help.

 

[Sandy Mann]

You are very welcome Murph. Thanks you the feedback.

--
Regards

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk


Sandy
You have nailed it!
Thank you for the help.