How to delete worksheets without Loop.


After a long time I came up with an interesting trick, it might be rarely useful for anyone but it is tricky. And the trick is “How to delete all worksheets except one” without any loops by VBA.

I always love to use array everywhere (I mean where possibleJ), so here is the trick

Worksheets() collection can accept Index array , and we need an expression that will return that index array. We all knows that  “=Row(1:5)” will return {1;2;3;4;5} , so we just need to create a dynamic index array to supply in worksheets() collection.

How to do that?

I use Evaluate very often. Evaluate() can evaluate any string that is an identical expression. So here is my Expression

“=Row(1:” & Worksheets.count-1 &”)”

If we evaluate this expression by Evaluate(“=Row(1:” & Worksheets.count-1 &”)”) it will return a 2D array, but in this case worksheets() accepts  1D array, we to make it 1D we would need to transpose this array, so the next expression is “Application.Transpose(Evaluate(“=Row(1:” & Worksheets.count-1 &”)”))

And finally, put this array in Worksheets().

Worksheets(Application.Transpose(Evaluate(“=Row(1:” & Worksheets.count-1 &”)”))).Delete

So this is a single line code to delete all worksheets except one. It will ask for confirmation to delete the worksheets , you can use Application.DisplayAlert=False  before and make it true after this line.

Thats all.

Thanks For reading.

Rajan verma

2 Comments Add yours

  1. snb says:

    My suggestions:

    – If you use Evaluate, you can bring ‘transpose’ wtihin the evaluate function.

    – If you use column there’s no need to ‘transpose’

    – if you use a named range you can use the abbreviated writing style of evaluate:

    Instead of ‘Select’ you can use ‘Delete’

    [CODE]Sub M_snb()

    Names.Add “snb_001”, Columns(1).Resize(, sheets.Count – 1)
    Sheets([column(snb_001)]).Select

    Sheets(Evaluate(“Column(offset(A1,,,,” & sheets.Count – 1 & “))”)).Select

    Sheets(Evaluate(“transpose(Row(1:” & sheets.Count – 1 & “))”)).Select
    End Sub[/CODE]

  2. I like your solution. Yes there are many different way to do a single task, thanks for suggestion.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s