Macro to insert a row after 6 rows and 45 rows

Closed
Report
Posts
4
Registration date
Sunday June 23, 2013
Status
Member
Last seen
June 28, 2013
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hi all,

I'm new here and hope you guys can give me some advice and help to end my problem!

I have a large data set containing "control values" and "data values"

I import my data into excel and the first 6 rows are control values, I need these to be separated form the data values by one entire row (the rows stretch over several columns).

The data values themselves are comprised of several different "experiments", each lasting a total of 45 rows. I need these to all be split up by a rows as well.

These data sets can be thousands of rows long so splitting them all by myself is time consuming! Is anyone able to design a macro to help me split these? You will obviously be thanked and acknowledged in my masters thesis! Even if a full one cant be designed some useful pointers would be really appreciated!

Cheers!

Danny

5 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
sample trivial data sheet1
enter A1 1
a2 2
highlight A1:a2 and drag down upto about 108 rows (more ok)

it will 1,2,3,4,5, etc


copy sheet1 data in sheet 2 also lfrom A1 of that sheet

now run this macro and see and ifnecessary modify

Sub test()
    Dim j As Long, k As Long, r As Range, m As Long
    Worksheets("sheet1").Cells.Clear
    Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")
    Worksheets("sheet1").Activate
    m = 0
    j = Cells(Rows.Count, "A").End(xlUp).Row
    'MsgBox j
    Set r = Range("A1")
    k = Int(j / 51)
    Do
        r.Offset(6, 1).EntireRow.Insert
        Set r = r.Offset(m * 7, 1)
        r.Offset(45, 1).EntireRow.Insert
        Set r = Cells(Rows.Count, "A").End(xlUp).End(xlUp)

        m = m + 1
        If m > k Then Exit Do
    Loop
MsgBox "macro over"
End Sub
1
Posts
4
Registration date
Sunday June 23, 2013
Status
Member
Last seen
June 28, 2013

Thanks venkat1926 this macro is very close to what I want so thank you!

It adds a blank row after 6, which is great but then the next blank row is inserted after 38. It then inserts one after 6 again, I end up getting a pattern like this: 6, 38, 6, 45, 6, 52, 6, 59, etc. The pattern I need is 6, 45, 45, 45, 45, etc.

This macro is on the way to that so thank you once again!

Danny
0
Posts
4
Registration date
Sunday June 23, 2013
Status
Member
Last seen
June 28, 2013

I have tried modifying the code but not luck!
0
Posts
4
Registration date
Sunday June 23, 2013
Status
Member
Last seen
June 28, 2013

bump
0
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
sample trivial data SHEET2
enter A1 1
a2 2
highlight A1:a2 and drag down upto about 161 rows (more ok)


AT THE BEGINNING OF THE MACRO THIS SHEET2 IS COPIED TO SHEET1


revised macro is


Sub test()
    Dim j As Long, r As Range
    Worksheets("sheet1").Cells.Clear
    Worksheets("sheet2").Cells.Copy Worksheets("sheet1").Range("A1")
    Worksheets("sheet1").Activate
    
    j = Cells(Rows.Count, "A").End(xlUp).Row
  '  'MsgBox j
    Set r = Range("A1")
    Do
    'MsgBox r.Address
    Set r = r.Offset(6, 0)
   ' MsgBox r.Address
    r.EntireRow.Insert
    Set r = r.Offset(45, 0)
    'MsgBox r.Address
     r.EntireRow.Insert
        If r.Row > j Then Exit Do
        
    Loop
MsgBox "macro over"
End Sub


I am sending the file in address gisven below. see result in sheet1

http://speedy.sh/nh5eB/gamedanny-130629.xlsm

check and give feedback.
0