Macro for inserting set number of rows between entries

[boblock1]

I am working on a database that has about 500K entries. The data is lumped together right now, and I need to separate it by macro if I can. Each entry provides quarterly data for a particular telephone line, on a particular switch in a telephone network. There are multiple entries per switch, with some having two, and others several hundred. I need to separate each set of data with three blank rows.

Any ideas on how to craft a macro?

Thanks

Bob

 

[Andrew Poulsom]

This seems to work:

Sub Test()
    Dim Rng As Range
    Dim x As Long
'   *** Change starting row and column reference to suit ***
    Set Rng = Range("A1:A" & Range("A65536").End(xlUp).Row)
    For x = Rng.Rows.Count To 2 Step -1
        If Rng.Cells(x, 1).Offset(-1, 0).Value <> Rng.Cells(x, 1).Value Then
            Rng.Cells(x, 1).Resize(3, 1).EntireRow.Insert Shift:=xlDown
        End If
    Next x
End Sub

Make sure you make a copy of your workbook first, just in case!

 

[boblock1]

I get an error message as the macro works. The problem appears to be in this line: Rng.Cells(x, 1).Resize(3, 1).EntireRow.Insert Shift:=xlDown

The formula won't work. Any suggestions?

 

[Andrew Poulsom]

That's possibly because you have so much data that you have run out of rows (max 65536). You did say 500K entries, which must be a typo, but I suppose it could be 50k.

Try inserting just 1 row, like this:

Rng.Cells(x, 1).EntireRow.Insert Shift:=xlDown

 

[boblock1]

Andrew,

My apologies. I have 34,845 rows of data. I had not looked before. Does this change anything? Should I past the formula line above in the original macro that you sent, or just use the line above as a second macro?

Thanks

Bob

 

[Andrew Poulsom]

You should replace:

Rng.Cells(x, 1).Resize(3, 1).EntireRow.Insert Shift:=xlDown

with

Rng.Cells(x, 1).EntireRow.Insert Shift:=xlDown

Or you could try 2 rows:

Rng.Cells(x, 1).Resize(2, 1).EntireRow.Insert Shift:=xlDown

Whether this will work depends on how may different values there are in column A.