How do I delete all but columns A-E across multiple sheets in an xls file?

Hello All

I have an xls file containing around 400 sheets, which I will be splitting into individual files.

However, before I do this, I would like to delete everything except columns A-E in every sheet to clean them up a bit.

I have found various ways of deleting the same thing from multiple sheets in a workbook, but is there a way of simply keeping only columns A-E in every sheet, and clearing everything else?

Your help will be greatly appreciated!

(I am using Excel for Mac)

Please Login or Register  to view this content.

Originally Posted by daboho

Please Login or Register  to view this content.

Thanks!! At first I thought this wasn't working but I ran it about 7 times and it got rid of one column each time.

Unfortunately there are "floating" graphs which remain, and was wondering if there is a way of deleting these all in one go, rather than going through all 400 sheets individually. I'll create a small sample document to show the situation and post it shortly.

Originally Posted by FDibbins

click on the 1st sheet you want to include
scroll to the last sheet you want to include
hold SHIFT and click on the last sheet name

You have now GROUPED all the sheets together. Whatever you do on 1, gets "punched" through to all the sheets.
So highlight the columns you (dont) want, then delete them. If you did the grouping right, those columns should now contain nothing.

A word of caution - make sure you UNGROUP the sheets when you are done, either by clicking on a sheet outside the group, or rig-click on a sheet name and select UNGROUP.
If you dont do this, ANYTHING you do on ANY sheet in the group will be dome on all sheets. I have created hours of extra work for myself when I forget this step

Thanks! As they all had different extra bits I was hoping that I could just select the columns I wished to keep and delete everything else, but the code above seems to leave graphs floating around the place. I'll have a go at using this method to see if it gets rid of the extra bits left behind.

Right I think I have attached a sample file to this post now, so when I run the VBA on this sample document it removes everything beyond column E one column per time the code is run.

When I do this with the full 400 sheet file, those floating graphs you see there are left behind on most of the sheets, any quick way to solve this?

I've realised the chart is using data from columns A and B which is why they are not being deleted, how do I get rid of charts from all sheets?