Looking for a macro

[drozbravo]

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

 

[Fr33dan]

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]

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

Application.ScreenUpdating = True

at the end of your code.

 

[Fr33dan]

Don't forget to add

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]

I know I must be doing something wrong but I did as you said, ran the macro and nothing happens

 

[QuietRiot]

this is one i made a long time ago i tweaked it for u

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]

try replacing the while loop with this one

    While Not Cells(i + 5, 1) = ""
        i = i + 5
        Rows(i).Insert Shift:=xlDown
    Wend

 

[drozbravo]

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]

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]

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.