Hi,

Is there a way to select non-consecutive rows besides Ctrl + click. If I
want to insert a blank row above about 40 rows, this can become quite tedious.


--
Thanks!

Dee

Hi
only possible with VBA in this case

--
Regards
Frank Kabel
Frankfurt, Germany

"dee" schrieb im Newsbeitrag
...
Hi,

Is there a way to select non-consecutive rows besides Ctrl + click.
If I
want to insert a blank row above about 40 rows, this can become quite
tedious.


--
Thanks!

Dee

Hi,

Thanks - I had a feeling. I guess I will struggle with writing it unless
you know of easy code.

Thanks again!

"Frank Kabel" wrote:

Hi
only possible with VBA in this case

--
Regards
Frank Kabel
Frankfurt, Germany

"dee" schrieb im Newsbeitrag
...
Hi,

Is there a way to select non-consecutive rows besides Ctrl + click.
If I
want to insert a blank row above about 40 rows, this can become quite
tedious.


--
Thanks!

Dee

Dee,

Select your cells, and run the macro below.

HTH,
Bernie
MS Excel MVP

Sub InsertRows2()
Dim myRow As Long
Dim myArea As Range
For Each myArea In Selection.Areas
For myRow = myArea.Rows.Count To 1 Step -1
myArea.Cells(myRow, 1).EntireRow.Insert
Next myRow
Next myArea
End Sub


"dee" wrote in message
...
Hi,

Thanks - I had a feeling. I guess I will struggle with writing it unless
you know of easy code.

Thanks again!

"Frank Kabel" wrote:

Hi
only possible with VBA in this case

--
Regards
Frank Kabel
Frankfurt, Germany

"dee" schrieb im Newsbeitrag
...
Hi,

Is there a way to select non-consecutive rows besides Ctrl + click.
If I
want to insert a blank row above about 40 rows, this can become quite
tedious.


--
Thanks!

Dee

thank you so much! It worked like a charm.

Do you have any suggestions as to a Web site that has free tutorials for VBA?

Thanks!

"Bernie Deitrick" wrote:

Dee,

Select your cells, and run the macro below.

HTH,
Bernie
MS Excel MVP

Sub InsertRows2()
Dim myRow As Long
Dim myArea As Range
For Each myArea In Selection.Areas
For myRow = myArea.Rows.Count To 1 Step -1
myArea.Cells(myRow, 1).EntireRow.Insert
Next myRow
Next myArea
End Sub


"dee" wrote in message
...
Hi,

Thanks - I had a feeling. I guess I will struggle with writing it unless
you know of easy code.

Thanks again!

"Frank Kabel" wrote:

Hi
only possible with VBA in this case

--
Regards
Frank Kabel
Frankfurt, Germany

"dee" schrieb im Newsbeitrag
...
Hi,

Is there a way to select non-consecutive rows besides Ctrl + click.
If I
want to insert a blank row above about 40 rows, this can become quite
tedious.


--
Thanks!

Dee

Bernie:

If it wouldn't be too much trouble, would you mind terribly explaining what
each line of code does?

Thanks!

"dee" wrote:

thank you so much! It worked like a charm.

Do you have any suggestions as to a Web site that has free tutorials for VBA?

Thanks!

"Bernie Deitrick" wrote:

Dee,

Select your cells, and run the macro below.

HTH,
Bernie
MS Excel MVP

Sub InsertRows2()
Dim myRow As Long
Dim myArea As Range
For Each myArea In Selection.Areas
For myRow = myArea.Rows.Count To 1 Step -1
myArea.Cells(myRow, 1).EntireRow.Insert
Next myRow
Next myArea
End Sub


"dee" wrote in message
...
Hi,

Thanks - I had a feeling. I guess I will struggle with writing it unless
you know of easy code.

Thanks again!

"Frank Kabel" wrote:

Hi
only possible with VBA in this case

--
Regards
Frank Kabel
Frankfurt, Germany

"dee" schrieb im Newsbeitrag
...
Hi,

Is there a way to select non-consecutive rows besides Ctrl + click.
If I
want to insert a blank row above about 40 rows, this can become quite
tedious.


--
Thanks!

Dee

Dee,

The best way to learn VBA is to record macros and try to modify them, all
the while posting questions and reading other peoples' questions and
responses, both here and in the archives.

See my comments in-line describing what is being done, then run the fully
interactive version below it.

HTH,
Bernie
MS Excel MVP

If it wouldn't be too much trouble, would you mind terribly explaining
what
each line of code does?

Suppose you select A1:B10, and A21:B30 prior to running the macro

Sub InsertRows2()
'a variable to store the row number
Dim myRow As Long 'this is dimensioned as a Long since there could be
65536 rows

'myArea is a range variable, to allow stepping through the range object's
area property
'When you select multiple cells using ctrl-clicks, Excel's selections has as
many areas
'as you used the ctrl-clicks. Otherwise, stepping through can be
complicated. In
'our example, there are two areas, each with 10 rows (but twenty cells)

Dim myArea As Range

'Just stepping through each area - there is always at least one area
'myArea is a range object, first time through equal toRange("A1:B10")
'Second time through equal toRange("A21:B30")

For Each myArea In Selection.Areas

'Indexing backward through the rows, since inserting a row changes the
indexing after
'processing has started. The Rows. Count returns how many rows are in each
area.
'Each area has 10 rows
For myRow = myArea.Rows.Count To 1 Step -1

'.Cells(myrow,1) is a cell in the first column of the area
'.using entirerow.insert puts a full blank line above it
'The first time through myRow = 10, then 9, then 8...
'But myArea is A1:B10, to myArea.Cells(10,1) is cell A10
'When myArea is A21:B30, myArea.Cells(10,1) is cell A30

myArea.Cells(myRow, 1).EntireRow.Insert

'go to the next row above
Next myRow

'go to the next area
Next myArea
End Sub


Sub InsertRows2()
Dim myRow As Long
Dim myArea As Range
For Each myArea In Selection.Areas
msgbox "The current Areas is " & myArea.Address
For myRow = myArea.Rows.Count To 1 Step -1
msgbox "The current row number is " & myRow & _
" and the current cell is " & myArea.Cells(myRow, 1).Address
myArea.Cells(myRow, 1).EntireRow.Insert
Next myRow
Next myArea
End Sub

Thanks so much. I have been working through it step by step to understand it
and I'm pretty clear on it. It's the -Step that I find baffling. I
understand part of the following line:

For myRow = myArea.Rows.Count To 1 Step -1

means myrow variable is the count of the number of rows in the currently
selected area which is... I'm not clear on "to 1 Step -1"

Does 1 mean column 1 (A) and Step -1 mean last number in count (10)

Sorry for my stupidity, but I'm a complete newbie with VBA. I'd really love
to understand this line clearly.

Thanks so much for your assistance and your patience.
Dee
"Bernie Deitrick" wrote:

Dee,

The best way to learn VBA is to record macros and try to modify them, all
the while posting questions and reading other peoples' questions and
responses, both here and in the archives.

See my comments in-line describing what is being done, then run the fully
interactive version below it.

HTH,
Bernie
MS Excel MVP

If it wouldn't be too much trouble, would you mind terribly explaining
what
each line of code does?

Suppose you select A1:B10, and A21:B30 prior to running the macro

Sub InsertRows2()
'a variable to store the row number
Dim myRow As Long 'this is dimensioned as a Long since there could be
65536 rows

'myArea is a range variable, to allow stepping through the range object's
area property
'When you select multiple cells using ctrl-clicks, Excel's selections has as
many areas
'as you used the ctrl-clicks. Otherwise, stepping through can be
complicated. In
'our example, there are two areas, each with 10 rows (but twenty cells)

Dim myArea As Range

'Just stepping through each area - there is always at least one area
'myArea is a range object, first time through equal toRange("A1:B10")
'Second time through equal toRange("A21:B30")

For Each myArea In Selection.Areas

'Indexing backward through the rows, since inserting a row changes the
indexing after
'processing has started. The Rows. Count returns how many rows are in each
area.
'Each area has 10 rows
For myRow = myArea.Rows.Count To 1 Step -1

'.Cells(myrow,1) is a cell in the first column of the area
'.using entirerow.insert puts a full blank line above it
'The first time through myRow = 10, then 9, then 8...
'But myArea is A1:B10, to myArea.Cells(10,1) is cell A10
'When myArea is A21:B30, myArea.Cells(10,1) is cell A30

myArea.Cells(myRow, 1).EntireRow.Insert

'go to the next row above
Next myRow

'go to the next area
Next myArea
End Sub


Sub InsertRows2()
Dim myRow As Long
Dim myArea As Range
For Each myArea In Selection.Areas
msgbox "The current Areas is " & myArea.Address
For myRow = myArea.Rows.Count To 1 Step -1
msgbox "The current row number is " & myRow & _
" and the current cell is " & myArea.Cells(myRow, 1).Address
myArea.Cells(myRow, 1).EntireRow.Insert
Next myRow
Next myArea
End Sub

Dee

Just a thought.

Why do you need the blank rows? For easier readability?

You could just select the 40 rows and make them taller so looks like
double-spacing.

Gord Dibben Excel MVP

On Fri, 3 Dec 2004 14:29:02 -0800, dee wrote:

Thanks so much. I have been working through it step by step to understand it
and I'm pretty clear on it. It's the -Step that I find baffling. I
understand part of the following line:

For myRow = myArea.Rows.Count To 1 Step -1

means myrow variable is the count of the number of rows in the currently
selected area which is... I'm not clear on "to 1 Step -1"

Does 1 mean column 1 (A) and Step -1 mean last number in count (10)

Sorry for my stupidity, but I'm a complete newbie with VBA. I'd really love
to understand this line clearly.

Thanks so much for your assistance and your patience.
Dee
"Bernie Deitrick" wrote:

Dee,

The best way to learn VBA is to record macros and try to modify them, all
the while posting questions and reading other peoples' questions and
responses, both here and in the archives.

See my comments in-line describing what is being done, then run the fully
interactive version below it.

HTH,
Bernie
MS Excel MVP

If it wouldn't be too much trouble, would you mind terribly explaining
what
each line of code does?

Suppose you select A1:B10, and A21:B30 prior to running the macro

Sub InsertRows2()
'a variable to store the row number
Dim myRow As Long 'this is dimensioned as a Long since there could be
65536 rows

'myArea is a range variable, to allow stepping through the range object's
area property
'When you select multiple cells using ctrl-clicks, Excel's selections has as
many areas
'as you used the ctrl-clicks. Otherwise, stepping through can be
complicated. In
'our example, there are two areas, each with 10 rows (but twenty cells)

Dim myArea As Range

'Just stepping through each area - there is always at least one area
'myArea is a range object, first time through equal toRange("A1:B10")
'Second time through equal toRange("A21:B30")

For Each myArea In Selection.Areas

'Indexing backward through the rows, since inserting a row changes the
indexing after
'processing has started. The Rows. Count returns how many rows are in each
area.
'Each area has 10 rows
For myRow = myArea.Rows.Count To 1 Step -1

'.Cells(myrow,1) is a cell in the first column of the area
'.using entirerow.insert puts a full blank line above it
'The first time through myRow = 10, then 9, then 8...
'But myArea is A1:B10, to myArea.Cells(10,1) is cell A10
'When myArea is A21:B30, myArea.Cells(10,1) is cell A30

myArea.Cells(myRow, 1).EntireRow.Insert

'go to the next row above
Next myRow

'go to the next area
Next myArea
End Sub


Sub InsertRows2()
Dim myRow As Long
Dim myArea As Range
For Each myArea In Selection.Areas
msgbox "The current Areas is " & myArea.Address
For myRow = myArea.Rows.Count To 1 Step -1
msgbox "The current row number is " & myRow & _
" and the current cell is " & myArea.Cells(myRow, 1).Address
myArea.Cells(myRow, 1).EntireRow.Insert
Next myRow
Next myArea
End Sub

You're definitely correct. However, it's to input additional information.

I'm just starting to get into VBA, so it's a good start. Any suggested
sites for learning it as described in plain English?

Thanks!

"Gord Dibben" wrote:

Dee

Just a thought.

Why do you need the blank rows? For easier readability?

You could just select the 40 rows and make them taller so looks like
double-spacing.

Gord Dibben Excel MVP

On Fri, 3 Dec 2004 14:29:02 -0800, dee wrote:

Thanks so much. I have been working through it step by step to understand it
and I'm pretty clear on it. It's the -Step that I find baffling. I
understand part of the following line:

For myRow = myArea.Rows.Count To 1 Step -1

means myrow variable is the count of the number of rows in the currently
selected area which is... I'm not clear on "to 1 Step -1"

Does 1 mean column 1 (A) and Step -1 mean last number in count (10)

Sorry for my stupidity, but I'm a complete newbie with VBA. I'd really love
to understand this line clearly.

Thanks so much for your assistance and your patience.
Dee
"Bernie Deitrick" wrote:

Dee,

The best way to learn VBA is to record macros and try to modify them, all
the while posting questions and reading other peoples' questions and
responses, both here and in the archives.

See my comments in-line describing what is being done, then run the fully
interactive version below it.

HTH,
Bernie
MS Excel MVP

If it wouldn't be too much trouble, would you mind terribly explaining
what
each line of code does?

Suppose you select A1:B10, and A21:B30 prior to running the macro

Sub InsertRows2()
'a variable to store the row number
Dim myRow As Long 'this is dimensioned as a Long since there could be
65536 rows

'myArea is a range variable, to allow stepping through the range object's
area property
'When you select multiple cells using ctrl-clicks, Excel's selections has as
many areas
'as you used the ctrl-clicks. Otherwise, stepping through can be
complicated. In
'our example, there are two areas, each with 10 rows (but twenty cells)

Dim myArea As Range

'Just stepping through each area - there is always at least one area
'myArea is a range object, first time through equal toRange("A1:B10")
'Second time through equal toRange("A21:B30")

For Each myArea In Selection.Areas

'Indexing backward through the rows, since inserting a row changes the
indexing after
'processing has started. The Rows. Count returns how many rows are in each
area.
'Each area has 10 rows
For myRow = myArea.Rows.Count To 1 Step -1

'.Cells(myrow,1) is a cell in the first column of the area
'.using entirerow.insert puts a full blank line above it
'The first time through myRow = 10, then 9, then 8...
'But myArea is A1:B10, to myArea.Cells(10,1) is cell A10
'When myArea is A21:B30, myArea.Cells(10,1) is cell A30

myArea.Cells(myRow, 1).EntireRow.Insert

'go to the next row above
Next myRow

'go to the next area
Next myArea
End Sub


Sub InsertRows2()
Dim myRow As Long
Dim myArea As Range
For Each myArea In Selection.Areas
msgbox "The current Areas is " & myArea.Address
For myRow = myArea.Rows.Count To 1 Step -1
msgbox "The current row number is " & myRow & _
" and the current cell is " & myArea.Cells(myRow, 1).Address
myArea.Cells(myRow, 1).EntireRow.Insert
Next myRow
Next myArea
End Sub

David McRitchie's "getting started with VBA and macros" is good place to
start<g

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Note the links at page bottom to some other sites including on-line Tutorials.

Gord

On Fri, 3 Dec 2004 15:23:04 -0800, dee wrote:

You're definitely correct. However, it's to input additional information.

I'm just starting to get into VBA, so it's a good start. Any suggested
sites for learning it as described in plain English?

Thanks!

"Gord Dibben" wrote:

Dee

Just a thought.

Why do you need the blank rows? For easier readability?

You could just select the 40 rows and make them taller so looks like
double-spacing.

Gord Dibben Excel MVP

On Fri, 3 Dec 2004 14:29:02 -0800, dee wrote:

Thanks so much. I have been working through it step by step to understand it
and I'm pretty clear on it. It's the -Step that I find baffling. I
understand part of the following line:

For myRow = myArea.Rows.Count To 1 Step -1

means myrow variable is the count of the number of rows in the currently
selected area which is... I'm not clear on "to 1 Step -1"

Does 1 mean column 1 (A) and Step -1 mean last number in count (10)

Sorry for my stupidity, but I'm a complete newbie with VBA. I'd really love
to understand this line clearly.

Thanks so much for your assistance and your patience.
Dee
"Bernie Deitrick" wrote:

Dee,

The best way to learn VBA is to record macros and try to modify them, all
the while posting questions and reading other peoples' questions and
responses, both here and in the archives.

See my comments in-line describing what is being done, then run the fully
interactive version below it.

HTH,
Bernie
MS Excel MVP

If it wouldn't be too much trouble, would you mind terribly explaining
what
each line of code does?

Suppose you select A1:B10, and A21:B30 prior to running the macro

Sub InsertRows2()
'a variable to store the row number
Dim myRow As Long 'this is dimensioned as a Long since there could be
65536 rows

'myArea is a range variable, to allow stepping through the range object's
area property
'When you select multiple cells using ctrl-clicks, Excel's selections has as
many areas
'as you used the ctrl-clicks. Otherwise, stepping through can be
complicated. In
'our example, there are two areas, each with 10 rows (but twenty cells)

Dim myArea As Range

'Just stepping through each area - there is always at least one area
'myArea is a range object, first time through equal toRange("A1:B10")
'Second time through equal toRange("A21:B30")

For Each myArea In Selection.Areas

'Indexing backward through the rows, since inserting a row changes the
indexing after
'processing has started. The Rows. Count returns how many rows are in each
area.
'Each area has 10 rows
For myRow = myArea.Rows.Count To 1 Step -1

'.Cells(myrow,1) is a cell in the first column of the area
'.using entirerow.insert puts a full blank line above it
'The first time through myRow = 10, then 9, then 8...
'But myArea is A1:B10, to myArea.Cells(10,1) is cell A10
'When myArea is A21:B30, myArea.Cells(10,1) is cell A30

myArea.Cells(myRow, 1).EntireRow.Insert

'go to the next row above
Next myRow

'go to the next area
Next myArea
End Sub


Sub InsertRows2()
Dim myRow As Long
Dim myArea As Range
For Each myArea In Selection.Areas
msgbox "The current Areas is " & myArea.Address
For myRow = myArea.Rows.Count To 1 Step -1
msgbox "The current row number is " & myRow & _
" and the current cell is " & myArea.Cells(myRow, 1).Address
myArea.Cells(myRow, 1).EntireRow.Insert
Next myRow
Next myArea
End Sub

dee,

In a For..To loop, the default is that the counter is incremented by one
each time through. If you want to decrease the counter, you need to
explicitly tell the loop to do that.

Try this macro below to see the difference.

HTH,
Bernie
MS Excel MVP


Sub DeeTest()
Dim i As Integer

MsgBox "Standard"
For i = 1 to 4
MsgBox i
Next i
MsgBox "Step 2"
For i = 1 to 4 Step 2
MsgBox i
Next i
MsgBox "Step -1"
For i = 4 to 1 Step -1
MsgBox i
Next i
MsgBox "Step -2"
For i = 4 to 1 Step -2
MsgBox i
Next i
End Sub


"dee" wrote in message
...
Thanks so much. I have been working through it step by step to understand
it
and I'm pretty clear on it. It's the -Step that I find baffling. I
understand part of the following line:

For myRow = myArea.Rows.Count To 1 Step -1

means myrow variable is the count of the number of rows in the currently
selected area which is... I'm not clear on "to 1 Step -1"

Does 1 mean column 1 (A) and Step -1 mean last number in count (10)

Sorry for my stupidity, but I'm a complete newbie with VBA. I'd really
love
to understand this line clearly.

Thanks so much for your assistance and your patience.
Dee
"Bernie Deitrick" wrote:

Dee,

The best way to learn VBA is to record macros and try to modify them, all
the while posting questions and reading other peoples' questions and
responses, both here and in the archives.

See my comments in-line describing what is being done, then run the
fully
interactive version below it.

HTH,
Bernie
MS Excel MVP

If it wouldn't be too much trouble, would you mind terribly explaining
what
each line of code does?

Suppose you select A1:B10, and A21:B30 prior to running the macro

Sub InsertRows2()
'a variable to store the row number
Dim myRow As Long 'this is dimensioned as a Long since there could be
65536 rows

'myArea is a range variable, to allow stepping through the range object's
area property
'When you select multiple cells using ctrl-clicks, Excel's selections has
as
many areas
'as you used the ctrl-clicks. Otherwise, stepping through can be
complicated. In
'our example, there are two areas, each with 10 rows (but twenty cells)

Dim myArea As Range

'Just stepping through each area - there is always at least one area
'myArea is a range object, first time through equal toRange("A1:B10")
'Second time through equal toRange("A21:B30")

For Each myArea In Selection.Areas

'Indexing backward through the rows, since inserting a row changes the
indexing after
'processing has started. The Rows. Count returns how many rows are in
each
area.
'Each area has 10 rows
For myRow = myArea.Rows.Count To 1 Step -1

'.Cells(myrow,1) is a cell in the first column of the area
'.using entirerow.insert puts a full blank line above it
'The first time through myRow = 10, then 9, then 8...
'But myArea is A1:B10, to myArea.Cells(10,1) is cell A10
'When myArea is A21:B30, myArea.Cells(10,1) is cell A30

myArea.Cells(myRow, 1).EntireRow.Insert

'go to the next row above
Next myRow

'go to the next area
Next myArea
End Sub


Sub InsertRows2()
Dim myRow As Long
Dim myArea As Range
For Each myArea In Selection.Areas
msgbox "The current Areas is " & myArea.Address
For myRow = myArea.Rows.Count To 1 Step -1
msgbox "The current row number is " & myRow & _
" and the current cell is " & myArea.Cells(myRow, 1).Address
myArea.Cells(myRow, 1).EntireRow.Insert
Next myRow
Next myArea
End Sub

Here is another way to insert blank cells without VBA
1. Copy data A1:B10 to bottom (A11:B20).
2. Format copied data in a unique way, say red letters.
3. Select both sets of data and sort. Red letters will be interleaved with black.
4. Go to Find and Replace.
5. At FindWhat:/Format/FormatFromCell, click any red letter cell.
6. Click Replace All.

Thanks to all of you who are so helpful and patient and to Bernie for taking
the time to write some code to clarify things.

Again - thanks so much!
Dee

"Bernie Deitrick" wrote:

dee,

In a For..To loop, the default is that the counter is incremented by one
each time through. If you want to decrease the counter, you need to
explicitly tell the loop to do that.

Try this macro below to see the difference.

HTH,
Bernie
MS Excel MVP


Sub DeeTest()
Dim i As Integer

MsgBox "Standard"
For i = 1 to 4
MsgBox i
Next i
MsgBox "Step 2"
For i = 1 to 4 Step 2
MsgBox i
Next i
MsgBox "Step -1"
For i = 4 to 1 Step -1
MsgBox i
Next i
MsgBox "Step -2"
For i = 4 to 1 Step -2
MsgBox i
Next i
End Sub


"dee" wrote in message
...
Thanks so much. I have been working through it step by step to understand
it
and I'm pretty clear on it. It's the -Step that I find baffling. I
understand part of the following line:

For myRow = myArea.Rows.Count To 1 Step -1

means myrow variable is the count of the number of rows in the currently
selected area which is... I'm not clear on "to 1 Step -1"

Does 1 mean column 1 (A) and Step -1 mean last number in count (10)

Sorry for my stupidity, but I'm a complete newbie with VBA. I'd really
love
to understand this line clearly.

Thanks so much for your assistance and your patience.
Dee
"Bernie Deitrick" wrote:

Dee,

The best way to learn VBA is to record macros and try to modify them, all
the while posting questions and reading other peoples' questions and
responses, both here and in the archives.

See my comments in-line describing what is being done, then run the
fully
interactive version below it.

HTH,
Bernie
MS Excel MVP

If it wouldn't be too much trouble, would you mind terribly explaining
what
each line of code does?

Suppose you select A1:B10, and A21:B30 prior to running the macro

Sub InsertRows2()
'a variable to store the row number
Dim myRow As Long 'this is dimensioned as a Long since there could be
65536 rows

'myArea is a range variable, to allow stepping through the range object's
area property
'When you select multiple cells using ctrl-clicks, Excel's selections has
as
many areas
'as you used the ctrl-clicks. Otherwise, stepping through can be
complicated. In
'our example, there are two areas, each with 10 rows (but twenty cells)

Dim myArea As Range

'Just stepping through each area - there is always at least one area
'myArea is a range object, first time through equal toRange("A1:B10")
'Second time through equal toRange("A21:B30")

For Each myArea In Selection.Areas

'Indexing backward through the rows, since inserting a row changes the
indexing after
'processing has started. The Rows. Count returns how many rows are in
each
area.
'Each area has 10 rows
For myRow = myArea.Rows.Count To 1 Step -1

'.Cells(myrow,1) is a cell in the first column of the area
'.using entirerow.insert puts a full blank line above it
'The first time through myRow = 10, then 9, then 8...
'But myArea is A1:B10, to myArea.Cells(10,1) is cell A10
'When myArea is A21:B30, myArea.Cells(10,1) is cell A30

myArea.Cells(myRow, 1).EntireRow.Insert

'go to the next row above
Next myRow

'go to the next area
Next myArea
End Sub


Sub InsertRows2()
Dim myRow As Long
Dim myArea As Range
For Each myArea In Selection.Areas
msgbox "The current Areas is " & myArea.Address
For myRow = myArea.Rows.Count To 1 Step -1
msgbox "The current row number is " & myRow & _
" and the current cell is " & myArea.Cells(myRow, 1).Address
myArea.Cells(myRow, 1).EntireRow.Insert
Next myRow
Next myArea
End Sub

Totally smart and worked well!

Thanks!

"Herbert Seidenberg" wrote:

Here is another way to insert blank cells without VBA
1. Copy data A1:B10 to bottom (A11:B20).
2. Format copied data in a unique way, say red letters.
3. Select both sets of data and sort. Red letters will be interleaved with black.
4. Go to Find and Replace.
5. At FindWhat:/Format/FormatFromCell, click any red letter cell.
6. Click Replace All.