Macro to insert a row after 6 rows and 45 rows Closed

Question

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

venkat1926 · Answer

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

gamedanny · Answer

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

gamedanny · Answer

I have tried modifying the code but not luck!

gamedanny · Answer

bump

venkat1926 · Answer

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.

Macro to insert a row after 6 rows and 45 rows

5 responses

Similar discussions

Subscribe To Our Newsletter!