Insert row above cells containing data

[GARY]

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

 

[Earl Kiosterud]

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.

 

[Tyro]

Now stop

Tyro

 

[GARY]

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

 

[Dave Peterson]

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