Macro to Select and Delete Columns and or Rows

Is there any way to use a macro to select a user defined column e.g. column S, together with all the columns to the right of that column and then have the Macro delete the selected columns. A key to this would be that it would need to cycle through every sheet in the workbook excluding the first say two or three sheets, again it needs to be possible to specify which sheets to exclude.

It would be great if the same could be achieved for rows, though I don't know if that would need to be done via a second macro?

Many thanks

yes that is do-able
to clarify you want to delete all sheets after (user input x), delete all rows after (user input y) on all sheets and delete all columns after (user input z) on all sheets?

or

you want to delete all rows after (user input x) and all columns after (user input y) on all sheets after (user input z)?

Note: are these sheets always going to be in order of creation or will the sheet tabs have been shuffled about?

Hi Leon V (AW),

No sheets need to be deleted, it is only columns and/or rows on the existing sheets. I've attached a really basic sample.

So, on the attached, when the macro is run, it would need to ignore the first two sheets, 'Main' and 'Instructions', so maybe the sheet names need to be specified manually in the macro by the user unless this there is a neater way to do this?

Ideally the macro when run, say for Columns, would show a pop up box saying something like 'from which column do you wish to delete?', allowing the user to enter 'T' or 20 (in this example, as T is column 20), then when the user clicks OK, Column T and all the Columns to the right of it are selected and deleted.

This needs to then cycle through all the sheets in the workbook, in the attached 6AX to 4FG (there will generally be around 200 per workbook), so in the sample attached, the macro would select all the green shaded columns, and delete them for each sheet with the execption the first two sheets, leaving just the yellow shaded columns and blank columns to the right.

It would be he same process for the rows. It may be better to have these as two seperate macro's because the user may need to delete columns but no rows and vice versa.

I hope that makes sense, if not, please let me know and I will try to explaion in more detail.

Many thanks

if there are 200 sheets they are unlikely to be in object order. selecting sheets to run on can be done using tab colour, sheet title or a userform listbox.
the userform would look cleaner however tab colour would most likely be quickest to use. up to you.

Would it not be easier, as there may only be one or two sheets the macro shouldn't be included on, to specify the exclusions, along the lines of:

Please Login or Register  to view this content.

rather than looking at tab colours etc, of which there may be numerous.

Hi Norie,

That works perfectly and is a very elegant solution. I adapted it for Rows as well, many thanks... My only question is, how do I remove the run-time error 424 should the user click the cancel button on the userform instead of selecting the row or column?

Yep, that did the trick...

Many thanks, that will save a lot of time... THanks for your quick and efficient response...