Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to insert cells with data only If they meet certain requiremen | Excel Worksheet Functions | |||
is it only appropriate to insert *different* data into *individual* cells... | Excel Discussion (Misc queries) | |||
MULTIPLE DATA - How to insert new data into existing data.... | Excel Discussion (Misc queries) | |||
INSERT A ROW AFTER EVERY 5 CELLS IN A SHEET THAT HAS DATA OF 120 | Excel Discussion (Misc queries) | |||
insert zeros in front of cells already filled with data. | Excel Discussion (Misc queries) |