Looking for a macro

drozbravo

New Member
Joined
Mar 8, 2005
Messages
15
Hey guys,

I have no clue on how to run or make a macro. What I have is a roughly 287 page mailing list. Each address is 4 rows long. I am looking for a way to insert an empty row at every fourth row. I assume that macros would be the best way to do this rather than inserting one in sine my last row is 14988. Any help on this would be appreciated. I looked and found ways to insert rows and copy formulas or inserting them at every row with data, but I need something tailored to every fourth row insertion. I tried using one and adapting it to my needs but since I don't know anything about coding, I found myself not where I needed to be and nothing would happen. I need to be able to start it at a specific point also so I dont go over what I have manually done up to this point.


Thanks in advance
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fr33dan

New Member
Joined
Jul 18, 2007
Messages
19
Code:
Sub insertLines()
    Application.ScreenUpdating = False
    i = 4
    While Not Cells(i, 1) = ""
        Rows(i + 1).Insert Shift:=xlDown
        i = i + 5
    Wend
End Sub
(I'm assuming from what you wrote you don't know how to get this into your excel file) Open the vba editor (Tools>Macros>VBA Editor), right click on the document and click add Module. Then paste this code into the created module. Change the 4 in the code to the number of the row just before the last blank row you put in. Close the editor and run the macro (Tools>Macro>Run Macro).
 

KWMSeattle

Board Regular
Joined
Aug 23, 2006
Messages
149
Code:
Sub insertLines()
    Application.ScreenUpdating = False
    i = 4
    While Not Cells(i, 1) = ""
        Rows(i + 1).Insert Shift:=xlDown
        i = i + 5
    Wend
End Sub
(I'm assuming from what you wrote you don't know how to get this into your excel file) Open the vba editor (Tools>Macros>VBA Editor), right click on the document and click add Module. Then paste this code into the created module. Change the 4 in the code to the number of the row just before the last blank row you put in. Close the editor and run the macro (Tools>Macro>Run Macro).

Don't forget to add
Code:
Application.ScreenUpdating = True
at the end of your code. ;)
 

Fr33dan

New Member
Joined
Jul 18, 2007
Messages
19
Don't forget to add
Code:
Application.ScreenUpdating = True
at the end of your code. ;)
You actually don't need it. When the user takes control of the application it automatically sets it back to true(Same for most Application. variables)
 

drozbravo

New Member
Joined
Mar 8, 2005
Messages
15
I know I must be doing something wrong but I did as you said, ran the macro and nothing happens
 

QuietRiot

Well-known Member
Joined
May 18, 2007
Messages
1,079
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. MacOS
this is one i made a long time ago i tweaked it for u

Code:
Sub BlueSkyadd()

Application.ScreenUpdating = False

Dim a As Integer, aa As String, aaa As String


Set C = Cells.Find(What:="*", After:=[A1], SearchDirection:=xlPrevious)
If C Is Nothing Then
    last = 1
Else
    last = C.Row
End If

last = (last * 4)

For a = 1 To last
aa = a
aaa = aa + ":" + aa
Rows(aa).Select
Selection.Insert Shift:=xlDown
a = a + 4
Next
Cells(1, 1).Select

Application.ScreenUpdating = True
End Sub

just change a = 1 to last to a = whatever row to last
 

Fr33dan

New Member
Joined
Jul 18, 2007
Messages
19
try replacing the while loop with this one
Code:
    While Not Cells(i + 5, 1) = ""
        i = i + 5
        Rows(i).Insert Shift:=xlDown
    Wend
 

drozbravo

New Member
Joined
Mar 8, 2005
Messages
15
I got an overflow error and when I went to debug it highlighted this line, bearing in mind I tried with 1 and the row I was at both.
 

Fr33dan

New Member
Joined
Jul 18, 2007
Messages
19
I got an overflow error and when I went to debug it highlighted this line, bearing in mind I tried with 1 and the row I was at both.
Which line is "This" line? and what do you mean the row you were at was both?
 

drozbravo

New Member
Joined
Mar 8, 2005
Messages
15
Oops I thought I had pasted that in, this line"For a = 1 To last "

it was on his. I have tried yours Fr33dan, and can not get it to work. I run the macro and nothing happens. I did replace the part you suggested. I do appreciate your time with this.
 

Forum statistics

Threads
1,181,375
Messages
5,929,585
Members
436,681
Latest member
natalie123vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top