Macro to insert a row after 6 rows and 45 rows

Closed
gamedanny Posts 4 Registration date Sunday June 23, 2013 Status Member Last seen June 28, 2013 - Jun 23, 2013 at 06:37 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jun 29, 2013 at 09:22 AM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jun 24, 2013 at 01:38 AM
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
gamedanny Posts 4 Registration date Sunday June 23, 2013 Status Member Last seen June 28, 2013
Jun 24, 2013 at 04:45 AM
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
gamedanny Posts 4 Registration date Sunday June 23, 2013 Status Member Last seen June 28, 2013
Jun 25, 2013 at 06:42 AM
I have tried modifying the code but not luck!
0
gamedanny Posts 4 Registration date Sunday June 23, 2013 Status Member Last seen June 28, 2013
Jun 28, 2013 at 11:49 AM
bump
0

Didn't find the answer you are looking for?

Ask a question
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jun 29, 2013 at 09:22 AM
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