Insert row above cells containing data
 

How can I insert a ROW above a cell that contains data?
(Col A contains 985 cells but only 284 cells contain data).

In the following example, I want to insert another row above cells A3,
A5, A11 and A14.

row col A

1 111021001-7
2
3 112411015-2
4
5 116141013-4
6
7
8
9
10
11 116151002-5
12
13
14 118103010-7

Insert row above cells containing data
 

Gary,

Select the column, Edit - Go to (F5) - Special - Constants. That should select the cells
with data. Ctrl-+ (or Edit Insert). Select "Entire Row." Note that this will be different
from shifting all those rows down one row -- the inserts will have a cumulative effect, and
the lower rows will be moved down more.
--
Regards from Virginia Beach,

Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"GARY" wrote in message
...
How can I insert a ROW above a cell that contains data?
(Col A contains 985 cells but only 284 cells contain data).

In the following example, I want to insert another row above cells A3,
A5, A11 and A14.

row col A

1 111021001-7
2
3 112411015-2
4
5 116141013-4
6
7
8
9
10
11 116151002-5
12
13
14 118103010-7

Insert row above cells containing data
 

Now stop

Tyro

"GARY" wrote in message
...
How can I insert a ROW above a cell that contains data?
(Col A contains 985 cells but only 284 cells contain data).

In the following example, I want to insert another row above cells A3,
A5, A11 and A14.

row col A

1 111021001-7
2
3 112411015-2
4
5 116141013-4
6
7
8
9
10
11 116151002-5
12
13
14 118103010-7

Insert row above cells containing data
 

Hi Earl,

That works fine when an empty cell precedes a cell containing data.
But what do I do when a cell containing data precedes a cell
containing data?

Gary


On Apr 27, 8:43 pm, "Earl Kiosterud" wrote:
Gary,

Select the column, Edit - Go to (F5) - Special - Constants. That should select the cells
with data. Ctrl-+ (or Edit Insert). Select "Entire Row." Note that this will be different
from shifting all those rows down one row -- the inserts will have a cumulative effect, and
the lower rows will be moved down more.
--
Regards from Virginia Beach,

Earl Kiosterudwww.smokeylake.com
-----------------------------------------------------------------------"GARY" wrote in message

...

How can I insert a ROW above a cell that contains data?
(Col A contains 985 cells but only 284 cells contain data).

In the following example, I want to insert another row above cells A3,
A5, A11 and A14.

row col A

1 111021001-7
2
3 112411015-2
4
5 116141013-4
6
7
8
9
10
11 116151002-5
12
13
14 118103010-7

Insert row above cells containing data
 

Option Explicit
Sub testme()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim Wks As Worksheet

Set Wks = ActiveSheet

With Wks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
If .Cells(iRow, "A").Value = "" Then
'skip it
Else
.Rows(iRow).Insert
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

GARY wrote:

Hi Earl,

That works fine when an empty cell precedes a cell containing data.
But what do I do when a cell containing data precedes a cell
containing data?

Gary

On Apr 27, 8:43 pm, "Earl Kiosterud" wrote:
Gary,

Select the column, Edit - Go to (F5) - Special - Constants. That should select the cells
with data. Ctrl-+ (or Edit Insert). Select "Entire Row." Note that this will be different
from shifting all those rows down one row -- the inserts will have a cumulative effect, and
the lower rows will be moved down more.
--
Regards from Virginia Beach,

Earl Kiosterudwww.smokeylake.com
-----------------------------------------------------------------------"GARY" wrote in message

...

How can I insert a ROW above a cell that contains data?
(Col A contains 985 cells but only 284 cells contain data).

In the following example, I want to insert another row above cells A3,
A5, A11 and A14.

row col A

1 111021001-7
2
3 112411015-2
4
5 116141013-4
6
7
8
9
10
11 116151002-5
12
13
14 118103010-7

--

Dave Peterson