Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel (архив)
Страницы: 1
RSS
Можно ли удалить все "скрытые" ячейки?
 
#1
13.08.2010 22:33:38
Всем доброго времени.  
 
Имеются большие xls файлы по 10Мб.    
После сортировки и фильтра остается несколько необходимых ячеек, все остальное скрывается.  
Естественно при сохранении размер файла не уменьшается. А надо бы.  
 
Вопрос: можно ли как-то удалить все скрытые (зажатые) ячейки, чтобы потом сохранить оставшиеся ячейки и тем самым добиться существенного уменьшения размера фала?    
 
Вариант "скопировать все в другой лист" не подходит.  
 
Спасибо.
 
 
 
#2
13.08.2010 22:37:25
1)Возможно, Вы имеете ввиду удалить скрытые строки?  
2)Чем не устраивает  "скопировать все в другой лист" и удалить исходный?
Я сам - дурнее всякого примера! ...
 
 
 
#3
13.08.2010 23:24:49
Проснетесь, глянете:  
 
Public Sub DelHiddenRows()  
   Dim lR As Long, i As Long  
   lR = ActiveSheet.UsedRange.Rows.Count  
   For i = lR To 1 Step -1  
       If Rows(i).Hidden Then Rows(i).Delete  
   Next  
End Sub
Я сам - дурнее всякого примера! ...
 
 
 
#4
13.08.2010 23:37:21
{quote}{login=KuklP}{date=13.08.2010 10:37}{thema=}{post}1)Возможно, Вы имеете ввиду удалить скрытые строки?  
2)Чем не устраивает  "скопировать все в другой лист" и удалить исходный?{/post}{/quote}  
 
1. Кстати, а в excel есть разница между отфильтроваными и скрытыми?  
Например у меня в файле первоначально зажаты несколько столбцов.  
Затем я ставлю автофильтр и зажимаю еще несколько строк.  
И то и ругое попадает под понятие "скрытые"?  
 
2. Слишком много потом нужно ровнять : )
 
 
 
#5
13.08.2010 23:38:12
{quote}{login=KuklP}{date=13.08.2010 11:24}{thema=}{post}Проснетесь, глянете:  
 
Public Sub DelHiddenRows()  
   Dim lR As Long, i As Long  
   lR = ActiveSheet.UsedRange.Rows.Count  
   For i = lR To 1 Step -1  
       If Rows(i).Hidden Then Rows(i).Delete  
   Next  
End Sub{/post}{/quote}  
 
Я не "программист", поэтому не знаю, что делать с этим кодом : (
 
 
 
#6
14.08.2010 00:19:41
> 1. Кстати, а в excel есть разница между отфильтроваными и скрытыми?  
Нет. Хотя "отфильтрованными" обычно называют видимые данные.  
 
Сергей,  
небольшое уточнение к твоему коду. Если Usedrange начинается не с первой, а с n-ной строки, то последние (n-1) строк не будут просмотрены. См. ниже.  
 
Если надо также удалять скрытые столбцы, код такой:  
 
Public Sub DelHiddenRowsColumns()  
Dim lR As Long, i As Long  
lR = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1  
For i = lR To 1 Step -1  
If Rows(i).Hidden Then Rows(i).Delete  
Next  
 
ActiveSheet.ShowAllData 'в режиме фильтра нельзя удалять столбцы!  
 
lR = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1  
For i = lR To 1 Step -1  
If Columns(i).Hidden Then Columns(i).Delete  
Next  
End Sub  
 
=87666=
 
 
 
#7
14.08.2010 07:06:05
{quote}{login=Казанский}{date=14.08.2010 12:19}{thema=}{post}>  
Сергей,  
небольшое уточнение к твоему коду. Если Usedrange начинается не с первой, а с n-ной строки, то последние (n-1) строк не будут просмотрены. {/post}{/quote}  
Согласен:-)
Я сам - дурнее всякого примера! ...
 
 
 
#8
14.08.2010 21:50:15
{quote}{login=Казанский}{date=14.08.2010 12:19}{thema=}{post}> 1. Кстати, а в excel есть разница между отфильтроваными и скрытыми?  
Нет. Хотя "отфильтрованными" обычно называют видимые данные.  
 
Сергей,  
небольшое уточнение к твоему коду. Если Usedrange начинается не с первой, а с n-ной строки, то последние (n-1) строк не будут просмотрены. См. ниже.  
 
Если надо также удалять скрытые столбцы, код такой:  
 
Public Sub DelHiddenRowsColumns()  
Dim lR As Long, i As Long  
lR = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1  
For i = lR To 1 Step -1  
If Rows(i).Hidden Then Rows(i).Delete  
Next  
 
ActiveSheet.ShowAllData 'в режиме фильтра нельзя удалять столбцы!  
 
lR = ActiveSheet.UsedRange.Columns.Count + ActiveSheet.UsedRange.Column - 1  
For i = lR To 1 Step -1  
If Columns(i).Hidden Then Columns(i).Delete  
Next  
End Sub  
 
=87666={/post}{/quote}  
 
А что в итоге делать мне?  
Стандартными инструментами данную операцию проделать не получится?
 
 
 
#9
14.08.2010 22:41:17
Грузите свой файл. Жмете alt-F11. Меню-Insert-Module. Туда копируете макрос Казанского. Далее сохраняем книгу. Переходим на нужный лист. Жмем alt-F8. Выбираем макрос DelHiddenRowsColumns - Выполнить. Смотрим что получилось.  
Макросы должны быть включены.
Я сам - дурнее всякого примера! ...
 
 
 
#10
14.08.2010 23:42:55
{quote}{login=Казанский}{date=14.08.2010 12:19}{thema=}{post}> 1. Кстати, а в excel есть разница между отфильтроваными и скрытыми?  
Нет. Хотя "отфильтрованными" обычно называют видимые данные.{/post}{/quote}  
На всякий случай уточню, что скрытые вручную и автофильтором строки или столбцы - не одно и то же с точки зрения Excel и VBA. Например, метод Find не видит того, что скрыто автофильтром, но видит скрытое вручную.
 
 
 
#11
14.08.2010 23:45:10
{quote}{login=KuklP}{date=14.08.2010 10:41}{thema=}{post}Грузите свой файл. Жмете alt-F11. Меню-Insert-Module. Туда копируете макрос Казанского. Далее сохраняем книгу. Переходим на нужный лист. Жмем alt-F8. Выбираем макрос DelHiddenRowsColumns - Выполнить. Смотрим что получилось.  
Макросы должны быть включены.{/post}{/quote}  
 
Большое спасибо. Вставил именно ваш код (а не Казанского), т.к. удаление скрытых столбцов не требуется, они как раз таки нужны.  
 
Скорость обработки оставляет желать лучшего.    
Но в целом очень полезный вариант решения проблемы.  
 
Еще раз спасибо.
 
 
 
#12
15.08.2010 00:03:00
{quote}{login=martensit}{date=13.08.2010 10:33}{thema=Можно ли удалить все "скрытые" ячейки?}{post}Вопрос: можно ли как-то удалить все скрытые ячейки, чтобы потом сохранить оставшиеся ячейки и тем самым добиться существенного уменьшения размера файла?{/post}{/quote}Без VBA - напишите противоположное условие, чтобы отобразилось то, что нужно удалить. Затем удалите видимые, и отобразите все - в фильтре есть такая строчка.
 
 
 
#13
15.08.2010 06:08:18
А с ВБА, чтоб быстрее:  
Public Sub DelHiddenRows()  
   Dim lR As Long, i As Long  
   lR = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1  
   Application.ScreenUpdating = False  
   For i = lR To 1 Step -1  
       If Rows(i).Hidden Then Rows(i).Delete  
   Next  
   Application.ScreenUpdating = True  
End Sub  
Но способ ZVI сработает просто моментально, так что подумайте.
Я сам - дурнее всякого примера! ...
 
 
 
#14
15.08.2010 12:40:10
{quote}{login=KuklP}{date=15.08.2010 06:08}{thema=}{post}А с ВБА, чтоб быстрее:  
Public Sub DelHiddenRows()  
   Dim lR As Long, i As Long  
   lR = ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Row - 1  
   Application.ScreenUpdating = False  
   For i = lR To 1 Step -1  
       If Rows(i).Hidden Then Rows(i).Delete  
   Next  
   Application.ScreenUpdating = True  
End Sub  
Но способ ZVI сработает просто моментально, так что подумайте.{/post}{/quote}  
 
Да, с новым кодом гораздо быстрее получилось. А можете объяснить в двух словах как не для программиста: за счет чего скорость увеличились?  
 
И возник еще один вопрос: в книге около 15 листов, надо зайти на каждый и применить обработку. А можно написать код, чтобы одним нажатием сразу всю книгу обработать?    
А то приходится над каждым листом сидеть, а так можно было бы другими полезными делами заняться, пока вся книга обрабатывается.
 
 
 
#15
15.08.2010 12:43:36
{quote}{login=ZVI}{date=15.08.2010 12:03}{thema=}{post}{quote}{login=martensit}{date=13.08.2010 10:33}{thema=Можно ли удалить все "скрытые" ячейки?}{post}Вопрос: можно ли как-то удалить все скрытые ячейки, чтобы потом сохранить оставшиеся ячейки и тем самым добиться существенного уменьшения размера файла?{/post}{/quote}Без VBA - напишите противоположное условие, чтобы отобразилось то, что нужно удалить. Затем удалите видимые, и отобразите все - в фильтре есть такая строчка.{/post}{/quote}  
 
Спасибо, вариант в копилку.    
Но не для моего случая. Т.к. слишком много листов и строк. И каждый раз выделять все пустые строки для удаления не вариант.  
Задача свести к минимуму движения мышкой.
 
 
 
#16
15.08.2010 12:46:34
{quote}{login=}{date=15.08.2010 12:40}{thema=Re: }{post}{quote}{login=KuklP}{date=15.08.2010 06:08}{thema=}{post}{/post}{/quote}И возник еще один вопрос{/post}{/quote}  
Привыкайте СРАЗУ полностью ставить задачу - давно получили бы нужный код и избежали бы ненужной переписки.
 
 
 
#17
15.08.2010 15:02:49
{quote}{login=}{date=15.08.2010 12:40}{thema=Re: }{post}{quote}{login=KuklP}{date=15.08.2010 06:08}{thema=}{post}А с ВБА, чтоб быстрее:  
{/post}{/quote}  
 
А можете объяснить в двух словах как не для программиста: за счет чего скорость увеличились?  
 
А можно написать код, чтобы одним нажатием сразу всю книгу обработать?    
{/post}{/quote}  
1) За счет отключения обновления экрана: Application.ScreenUpdating = False  
2)Для всей книги  
Public Sub DelHiddenRows()  
   Dim lR As Long, i As Long, sh As Worksheet  
   On Error Resume Next  
   Application.ScreenUpdating = False  
   For Each sh In ThisWorkbook.Worksheets  
       sh.Activate  
       lR = sh.UsedRange.Rows.Count + sh.UsedRange.Row - 1  
       For i = lR To 1 Step -1  
           If sh.Rows(i).Hidden Then Rows(i).Delete  
       Next  
   Next  
   Application.ScreenUpdating = True  
End Sub
Я сам - дурнее всякого примера! ...
 
 
 
#18
16.08.2010 18:35:18
{quote}{login=KuklP}{date=15.08.2010 03:02}{thema=Re: Re: }{post}{quote}{login=}{date=15.08.2010 12:40}{thema=Re: }{post}{quote}{login=KuklP}{date=15.08.2010 06:08}{thema=}{post}А с ВБА, чтоб быстрее:  
{/post}{/quote}  
 
А можете объяснить в двух словах как не для программиста: за счет чего скорость увеличились?  
 
А можно написать код, чтобы одним нажатием сразу всю книгу обработать?    
{/post}{/quote}  
1) За счет отключения обновления экрана: Application.ScreenUpdating = False  
2)Для всей книги  
Public Sub DelHiddenRows()  
   Dim lR As Long, i As Long, sh As Worksheet  
   On Error Resume Next  
   Application.ScreenUpdating = False  
   For Each sh In ThisWorkbook.Worksheets  
       sh.Activate  
       lR = sh.UsedRange.Rows.Count + sh.UsedRange.Row - 1  
       For i = lR To 1 Step -1  
           If sh.Rows(i).Hidden Then Rows(i).Delete  
       Next  
   Next  
   Application.ScreenUpdating = True  
End Sub{/post}{/quote}  
 
Еще раз спасибо. Очень выручили.
 
 
 
Страницы: 1
Читают тему
Наверх