Selection.Delete Shift:=xlUp

terry87

New Member
Joined
Sep 22, 2015
Messages
9
I want to delete contents of some cells and then shift the cells up without modifying formulae in the same rows of the deleted cells.

Currently when I delete the cells and move the cells up to replace the deleted cells, any formulae in the rows of the deleted cells are modifed and reference cells incorrectly. I know I can delete the rows to get the desired result but I am doing many similar deletions which would mean I would have to copy the formulae again many many times.

Any ideas
terry87
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Rather than delete the rows, why not clear the contents & then sort.
 
Last edited:
Upvote 0
Sorting would not help as I need the remaining cells in the same order as they were before.

:(
 
Upvote 0
Sorting would not help as I need the remaining cells in the same order as they were before.

:(
Not a problem, you can add a helper column with the values 1,2,3 etc & then sort on that. That way your data remains in the same order but without the blank rows.
 
Upvote 0
Can you please expand on your suggestion....

I guess another solution would be to create a macro which selects the columns with the incorrect reference "#Ref!" and replace with correct reference "A1"
 
Upvote 0
If you had data like this, with the helper col H


Excel 2013 32 bit
ABGH
1PostcodeIn Use?GridRefHelper
2AL1 5EGYesTL1590701
3AL10 9WXYesTL2190722
4AL2 1UXYesTL1660393
5AL2 2EJYesTL1570174
6AL2 2EXYesTL1540475
7AL2 3XZYesTL1290216
8AL2 3YTYesTL1260167
9AL3 6ADYesTL1320698
England postcodes


After clearing some rows you get


Excel 2013 32 bit
ABGH
1PostcodeIn Use?GridRefHelper
2AL1 5EGYesTL1590701
3AL10 9WXYesTL2190722
4
5AL2 2EJYesTL1570174
6AL2 2EXYesTL1540475
7
8
9AL3 6ADYesTL1320698
England postcodes


And then sort


Excel 2013 32 bit
ABGH
1PostcodeIn Use?GridRefHelper
2AL1 5EGYesTL1590701
3AL10 9WXYesTL2190722
4AL2 2EJYesTL1570174
5AL2 2EXYesTL1540475
6AL3 6ADYesTL1320698
7
8
9
England postcodes
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,522
Messages
6,114,112
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top