Delete column issue

I can't seem to be able to delete columns with any effect. I only use columns A:Q on a sheet, but excel thinks I'm using A:XFD

I have tried.

A) Selecting all columns from S:XFD and deleting them. This just brings in new columns (which should be blank) but contain some border and background formatting matching for the first 5 rows.

B) Selecting all columns from S:XFD and clearing all. Then deleting them.

C) Clearing contents, going to VBA immediate window and typing Activesheet.usedrange - to try and reset the used range

In all three cases if I use the immediate VBA window, it reports the used range to still be 6:186, i.e. all columns are still part of the used range (even if deleted and cleared).


Any ideas please?

thanks, Dom

Do you have any conditional formatting that uses whole row references?

Good idea but no: Did have one rule but just deleted it. Then tried re deleting and same thing happens.

I do think it's something to do with formatting as all the columns deleted reappear with certain cells bordered and coloured. But I'm not sure why that would survive clear contents all and delete

Any worksheet or workbook event code, that adds formatting?

Another check, if you do Ctrl End where does that take you?

Lots of code but not really to do with that sheet.

This is getting more bizarre. After I cleared contents. I saved - and the file had gone from 5mb to 25mb.

I went back and deleted columns again - and it goes back down to 5mb, but on the screen it looks like.

Now when I type usedrange it gives $A$1:$R$696 - even though it looks like there is this blue formatting in all the columns to the right.

Screenshot 2018-12-15 at 19.03.44.jpg

If you copy that sheet to a new workbook, do you still get the same thing?
If so can you upload that file?

Thanks for help.
Yes if I copy sheet to a new file - it still has same behaviour

The uplink icon doesn't seem to be working.
Will keep trying

The paper clip icon doesn't work at the moment.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Yep I've managed to upload with go advanced / manage - but how do I insert into message?

In the upload manager window
Click "Browse" & select your file
Then click "Upload"
Then click "Close this window"
and finally click "Submit Reply"

ok hopefully this adds the attachment

Thanks for the file.
It looks as though there is a style in place on those cells.
If you select col S:XFD & set style to normal then set borders to no borders.
All should be well.

Great thx - that's sorted it. I was convinced it was corrupted so glad it's not. Must be a bit of an anomaly that delete and clear contents don't remove the styles.

Anyway great help. many thx

Glad to help & thanks for the feedback