Garry,

Try this version.

HTH,
Bernie
MS Excel MVP

Sub InsertBlanksForGarry2()
Dim myRange As Range
Dim myCnt As Long

On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete
Range("A1").EntireColumn.Insert
Set myRange = Range("A2:A" & Range("B65536").End(xlUp).Row)
Range("A1").Formula = 1
Range("A2").FormulaR1C1 = _
"=IF(LEFT(RC[1], 1) = ""["", R[-1]C+1,R[-1]C)"
Range("A2").AutoFill Destination:=myRange, _
Type:=xlFillDefault
myRange.Copy
myRange.PasteSpecial Paste:=xlPasteValues
Application.Calculate
myCnt = Application.Max(Range("A:A"))
myRange(myRange.Rows.Count + 1).Value = 1
myRange(myRange.Rows.Count + 1).Resize(myCnt, 1).DataSeries _
Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Stop:=myCnt, Trend:=False

Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending
Selection.EntireColumn.Delete
Range("A1").Select
End Sub

"Garry Douglas" wrote in message
...
Just one problem, when I ran the code it inserts a blank line between
every
row (field). I only want to insert a blank row between every record as
denoted by the first field in each record being a text line beginning with
a
square parenthesis i.e. [ Unfortunately the records are variable length
(rows) so I can't select a fixed number of rows and insert a blank line at
each iteration.

I can see how and why your code is doing what it does in step mode and I
can
utilise the part that eliminates the blank rows but I can't for the life
of
me figure out how to insert the blank rows between every record in the way
that you demonstrated.

Thanks

Garry

"Garry Douglas" wrote in message
...
Thanks Bernie.

That's very neat!

I finally came up with a solution that works but I like the idea of
getting
rid of the blank cells.

My crude and inelegant version, which inserts the value two rows EOF
after
the last active cell (located by "boxing the compass") to define the
limits
of the range, was:

Sub test2()
Range("B1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(0, -1).Select
Selection.End(xlUp).Select
ActiveCell.Offset(2, 0).Select
ActiveCell.Formula = "EOF"
Range("A2").Select [starts at line 2 as I had not figured out how to
"handle" the first line!!]
Do Until ActiveCell = "EOF"
If Left(ActiveCell.Value, 1) = "[" And ActiveCell.Offset(-1, 0).Value <
""
Then
Selection.Insert Shift:=xlDown
ActiveCell.Offset(1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Garry

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Garry,

Here's an alternative approach: get rid of all the blanks, insert a
counter
in column A, double the counter by copying it, then sort based on the
counter, and finally delete the counter.

The macro below works on column A. Give it a try.

HTH,
Bernie
MS Excel MVP

Sub InsertBlanksForGarry()
Dim myRange As Range
On Error Resume Next
Columns("A:A").SpecialCells(xlCellTypeBlanks).Enti reRow.Delete
Range("A1").EntireColumn.Insert
Set myRange = Range("A1:A" & Range("B65536").End(xlUp).Row)
Range("A1").FormulaR1C1 = "=ROW()"
Range("A1").AutoFill Destination:=myRange, _
Type:=xlFillDefault
myRange.Copy
myRange.PasteSpecial Paste:=xlPasteValues
myRange(myRange.Rows.Count + 1).PasteSpecial Paste:=xlPasteValues
Range("A1").Sort Key1:=Range("A1"), Order1:=xlAscending
Selection.EntireColumn.Delete
Range("A1").Select
End Sub

"Garry Douglas" wrote in message
...
Hi

I'm having a brain fade and can't figure out how to insert blank
rows
in
a
selection without corrupting the selection and returning a Run Time
error
424: Object Required.

The text data is from a csv file and is all pasted in Col A. I want
to
force
a blank line between each variable length record. Each new record
starts
with an alpha/numeric string contained in square parentheses.

The abstract of the code I have currently is as follows:

Range("A1").Select
Range(ActiveCell, Selection.End(xlDown)).Select
For Each c In Selection
If Left(c.Value, 1) = "[" And c.Offset(-1, 0).Value < "" Then
c.Insert.Row
End If
Next c

The multiple If/And condition on line 4 is needed because some
records
already have a blank row separating them. I just want to insert a
single
blank row between the records that are not already separated.

Any suggestions would be greatly appreciated.

Thanks

Garry Douglas

** Please Remove SPMOFF to Reply **