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

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

A couple of problems...

you have it select A1, but then you try to access an
offset .offset(-1,0), so it's trying to check a row 0,
which there is none. I think you need to start it by
selecting cell A2.

if some records already have a blank row separating them,
will your selection.end(xldown) select them all? Won't it
stop at the blank row? You may need to use
activesheet.usedrange.rows.count to tell it how far to
select down.

try using c.entirerow.insert to do the insertion... when
I tried it, that worked fine, whereas the other way,
c.insert.row , got the object error.

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

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

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

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.


It doesn't actually insert rows...

After having deleted the original blanks, it

1) Inserts a column for an index of the original data
2) Creates an index of the original data
3) Copies that index to the rows below your data range
4) Resorts the new range, which includes both your data,
and the second copy of the index.

This leaves you with your original row of data, and a new
row blank row, which has been placed based upon the
original index, and the copied index, each having the same
values in them...

sort of like turning

1 data
2 data
3 data
1
2
3

into:

1 data
1
2 data
2
3 data
3

through sorting.

It's a good solution.... can in some cases work more
quickly than progressing through a loop and inserting.

That seems to do the trick!

Thanks again.

Garry

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
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