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 **