How can I make cell data automatically shift up when deleting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

EXAMPLE:

A B C
1) Dog Bone Leash
2) Cat Catnip Litter
3) Bird Seed Newspaper
4) Cow Grass Manuer
5) Duck Fish Filter
6) Mouse Cheese Hanta Virus

If I were to delete the information in row 1, I want everything in row two
to shift up automatically. Or... If I were to delete "dog," I would want
everything in Column A, and only Column A, to shift up, therefore removing
any blank cells in the table. How would I do this?
 
One way ..

Assuming this table is in Sheet1, cols A to C,
data from row1 down
A B C
1) Dog Bone Leash
2) Cat Catnip Litter
3) Bird Seed Newspaper
Click to expand...
etc

Put in D1: =IF(A1="","",ROW())

Copy D1 across to F1, fill down to say, F1000
to cover the max expected data range in cols A to C

In Sheet2
-------------
Put in say, A2:

=IF(ISERROR(SMALL(Sheet1!D:D,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMALL
(Sheet1!D:D,ROWS($A$1:A1)),Sheet1!D:D,0)))

Copy A2 across to C2, fill down to C1001
(cover same range as in Sheet1)

Sheet2 will return the desired behaviour
depending on changes made in Sheet1
 
All the coding is impressive and I'm sure it works like a charm, but I prefer
Edit>Delete>Shift Cells Up, myself |:>)
 
That is very impressive coding, and it does work. However, I was hoping for
something a bit more two diminsional. I want it so when I make corrections on
the first page, the cells shift up on the first page. The "edit, delete,
shift up" works fine, and that may be what I have to resort to. However, I am
trying to create a spreadsheet for a complete computer novice, and that may
be too many steps. If there is a function, code, or setting that could make
that possible without getting too complicated in the dismount, that would be
ideal. Any other ways to do this?
 
Back
Top