Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 117
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 611
Default 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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,091
Default 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



  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 117
Default 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


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to insert cells with data only If they meet certain requiremen Chelsey- Really need help! Excel Worksheet Functions 0 August 24th 07 05:00 PM
is it only appropriate to insert *different* data into *individual* cells... showsomeidnow Excel Discussion (Misc queries) 5 April 30th 07 09:33 PM
MULTIPLE DATA - How to insert new data into existing data.... Rodorodo Excel Discussion (Misc queries) 0 December 16th 06 12:50 AM
INSERT A ROW AFTER EVERY 5 CELLS IN A SHEET THAT HAS DATA OF 120 JVR Excel Discussion (Misc queries) 3 March 17th 06 05:10 PM
insert zeros in front of cells already filled with data. Horizon Excel Discussion (Misc queries) 2 January 16th 06 09:47 PM


All times are GMT +1. The time now is 12:33 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"