IT Справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд

Excel vba удалить лист

Quickest way to clear all sheet contents VBA

I have a large sheet that I need to delete all the contents of. When I try to simply clear it without VBA it goes into not responding mode. When using a macro such as:

It also doesn’t respond. What’s the quickest way to do this?

Создан 30 апр. 15 2015-04-30 12:09:06 hc91

**Does the sheet contain Conditional Formatting ??** – Gary’ s Student 30 апр. 15 2015-04-30 12:17:11

Not conditional formatting, no. – hc91 30 апр. 15 2015-04-30 12:21:11

Try ‘Application.EnableEvents = False: Sheets(«Zeroes»).Cells.Delete: Application.EnableEvents = True’ – omegastripes 26 мар. 16 2016-03-26 21:12:16

4 ответа

The .Cells range isn’t limited to ones that are being used, so your code is clearing the content of 1,048,576 rows and 16,384 columns — 17,179,869,184 total cells. That’s going to take a while. Just clear the UsedRange instead:

Alternately, you can delete the sheet and re-add it:

Создан 30 апр. 15 2015-04-30 12:28:14 Comintern

I’m not sure that will work either. I’ve tried doing it out of VBA and just highlighting a snippet (200 rows, 5000 columns) and deleting and that froze it. I’ve then put a loop in and just tried to loop through 1000 rows, that didn’t work either. I may just loop it manually and hold F8 down. – hc91 30 апр. 15 2015-04-30 12:34:53

@hc91 — Interesting. Sounds like there’s enough data that Excel is causing the issue, not VBA. You can always delete the worksheet and re-add it. See the edit. – Comintern 30 апр. 15 2015-04-30 12:41:37

Читать еще:  Excel listbox пример

That’s a very good workaround, thanks. – hc91 30 апр. 15 2015-04-30 12:49:52

Heads up for future readers, you cannot delete a sheet if it’s the last/only one in the workbook. In that case, you can add the new blank sheet first, delete the old one, and finally rename that new sheet to the old sheet’s name. – tavnab 28 июн. 16 2016-06-28 03:34:34

Also, note that deleting a sheet removes all associated VBA code if this is an issue. I went with just deleting and recreating sheets in a VBA utility I was developing which painted me into a corner when I started adding events to these sheets. My recommendation is ‘Sheets(«Zeroes»).Cells.Delete xlShiftUp’ which seems instantaneous to me. – user1274820 12 авг. 16 2016-08-12 16:34:08

Also note that eliminating a Sheet will create conflicts with formulas in other sheets that were referencing the recently eliminated one, recreating the sheet may not solve that issue. – Azura 15 дек. 16 2016-12-15 20:58:36

Technically, and from Cominterns accepted workaround, I believe you actually want to Delete all the Cells in the Sheet. (Which removes Formatting etc as well as the Cells Contents) I.e. Sheets(«Zeroes»).Cells.Delete

(clears 10000 lines and 2000 columns in about half a second on my PC)

Combined instead with UsedRange and ScreenUpdating it should be nearly intantaneous.

Создан 04 фев. 16 2016-02-04 22:39:02 Stax

Ссылка на основную публикацию