Selection.Delete Shift:=xlUp

[terry87]

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

 

[Fluff]

Rather than delete the rows, why not clear the contents & then sort.

 

[terry87]

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

 

[terry87]

btw thnx for quick reply

 

[Fluff]

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.

 

[terry87]

Thnx Fluff but I don't understand your solution

 

[terry87]

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"

 

[Fluff]

If you had data like this, with the helper col H

Excel 2013 32 bit
	A	B	G	H
1	Postcode	In Use?	GridRef	Helper
2	AL1 5EG	Yes	TL159070	1
3	AL10 9WX	Yes	TL219072	2
4	AL2 1UX	Yes	TL166039	3
5	AL2 2EJ	Yes	TL157017	4
6	AL2 2EX	Yes	TL154047	5
7	AL2 3XZ	Yes	TL129021	6
8	AL2 3YT	Yes	TL126016	7
9	AL3 6AD	Yes	TL132069	8
England postcodes

After clearing some rows you get

Excel 2013 32 bit
	A	B	G	H
1	Postcode	In Use?	GridRef	Helper
2	AL1 5EG	Yes	TL159070	1
3	AL10 9WX	Yes	TL219072	2
4
5	AL2 2EJ	Yes	TL157017	4
6	AL2 2EX	Yes	TL154047	5
7
8
9	AL3 6AD	Yes	TL132069	8
England postcodes

And then sort

Excel 2013 32 bit
	A	B	G	H
1	Postcode	In Use?	GridRef	Helper
2	AL1 5EG	Yes	TL159070	1
3	AL10 9WX	Yes	TL219072	2
4	AL2 2EJ	Yes	TL157017	4
5	AL2 2EX	Yes	TL154047	5
6	AL3 6AD	Yes	TL132069	8
7
8
9
England postcodes

 

[terry87]

Fluff that is brilliant. I have spent days on this. Thank you so much

Terry97

 

[Fluff]

Glad to help & thanks for the feedback