Удаление формул из пустых ячеек

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Удаление формул из пустых ячеек (в очередной раз, очередной вопрос)

yuka

Дата: Среда, 09.01.2013, 13:51 | Сообщение № 1

Группа: Пользователи

Ранг: Участник

Сообщений: 94

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Добрый день! Все с прошедшими праздниками!

У меня такой вопрос:
Есть лист в книге, в котором больше 8 тыс. строк. Данные в ячейки заносятся формулами. Там, где результат формулы равен "пустоте", то и в ячейке остается "пустота". Мне надо из этих ячеек, в которых "пустота" удалить эти формулы быстро. У меня есть макрос но это банальный цикл, построчно перебирающий. Он очень долго работает.

Заранее спасибо.

Ответить

Alex_ST

Дата: Среда, 09.01.2013, 14:18 | Сообщение № 2

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3209

Репутация: 609 ±

Замечаний: 0% ±

2003

А Вы не по всем ячейкам цикл делайте, а только по тем, которые содержат формулы:
ActiveSheet.SpecialCells(xlCellTypeFormulas) и перед заменой отключайте обработку событий и пересчёт листа.

С уважением,
Алексей
MS Excel 2003 - the best!!!

Сообщение отредактировал Alex_ST - Среда, 09.01.2013, 14:20

Ответить

Gustav

Дата: Среда, 09.01.2013, 14:20 | Сообщение № 3

Группа: Друзья

Ранг: Участник клуба

Сообщений: 2757

Репутация: 1139 ±

Замечаний: 0% ±

начинал с Excel 4.0, видел 2.1

Если Вам разрешено изменение формул и можно вместо пустоты вернуть ошибку, скажем, функцией НД(), то потом можно выделить ошибки через специальное выделение формул с ошибками (Ctrl+G, Выделить). Макросом это делается через Range.SpecialCells. Далее удаляете (очищаете) нужные выделенные ячейки одной командой (без цикла):

[vba]

Код

Selection.SpecialCells(xlCellTypeFormulas, 16).ClearContents

[/vba]

P.S. Как-то так примерно:
[vba]

Код

Sub Macro1()
     With Range("A1:A20")
         .FormulaR1C1 = "=IF(RAND()>0.5,""Не удаляем"",NA())"
         .SpecialCells(xlCellTypeFormulas, 16).ClearContents
     End With
End Sub

[/vba]

МОИ: Ник, Tip box: 41001663842605

Сообщение отредактировал Gustav - Среда, 09.01.2013, 14:41

Ответить

Саня

Дата: Среда, 09.01.2013, 14:36 | Сообщение № 4

Группа: Друзья

Ранг: Ветеран

Сообщений: 1068

Репутация: 560 ±

Замечаний: 0% ±

XL 2016

могу предложить такой вариант:
[vba]

Код

Dim cell As Range, Rubbish As Range
For Each cell In Range()    ' Range() - сам поставишь, что нужно
      If cell.Value = "" Then
          If Rubbish Is Nothing Then
              Set Rubbish = cell
          Else
              Set Rubbish = Union(Rubbish, cell)
          End If
      End If
Next cell

If Not Rubbish Is Nothing Then
      '   Rubbish.Select
      Rubbish.ClearContents
      Set Rubbish = Nothing
End If

[/vba]

это работает быстрее, чем в цикле "рубать" каждую ячейку

посты 2, 3 - однозначно, да.

Ответить

AlexM

Дата: Среда, 09.01.2013, 14:40 | Сообщение № 5

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4511

Репутация: 1128 ±

Замечаний: 0% ±

Excel 2003

Плохо, что нет файла в задании.
Допустим таблица в столбцах A:D
В столбце С формулы, дающие пустые значения.
Код
[vba]

Код

Sub Macros()
iCol = 3
iRow = Application.CountA(Columns(iCol))
     Range("A1:D" & iRow).AutoFilter Field:=iCol, Criteria1:="=*"
     Rows("2:" & iRow).Delete Shift:=xlUp
     Range("A1:D" & iRow).AutoFilter
End Sub

[/vba]

Номер мобильного модема (без голосовой связи)
9269171249 МегаФон, Московский регион.

Ответить

ikki

Дата: Среда, 09.01.2013, 14:41 | Сообщение № 6

Группа: Друзья

Ранг: Старожил

Сообщений: 1906

Репутация: 504 ±

Замечаний: 0% ±

Excel 2003, 2010

Цитата (yuka)

результат формулы равен "пустоте"

пустой строке?

решение обязательно макросом?
без макроса (специально посчитал) 12 раз щелкнуть мышкой.

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki

Ответить

yuka

Дата: Понедельник, 14.01.2013, 11:04 | Сообщение № 7

Группа: Пользователи

Ранг: Участник

Сообщений: 94

Репутация: 0 ±

Замечаний: 0% ±

Excel 2007

Спасибо большое, все работает!

Цитата (ikki)

пустой строке?

нет, пустой ячейке

Цитата (ikki)

без макроса (специально посчитал) 12 раз щелкнуть мышкой.

Это как? Научите!))

Цитата (Alex_ST)

и перед заменой отключайте обработку событий и пересчёт листа.

Какими командами это делается? Что это дает?

Сообщение отредактировал yuka - Понедельник, 14.01.2013, 11:19

Ответить

ikki

Дата: Понедельник, 14.01.2013, 11:13 | Сообщение № 8

Группа: Друзья

Ранг: Старожил

Сообщений: 1906

Репутация: 504 ±

Замечаний: 0% ±

Excel 2003, 2010

1) выделить диапазон
2) F5 - выделить - формулы - ок
3) Ctrl+F - поле "найти" оставить пустым, область поиска - выбрать "значения" - нажать "найти все" (диалог поиска не закрываем)
4) Ctrl+A, Del

помощь по Excel и VBA
ikki@fxmail.ru, icq 592842413, skype alex.ikki

Сообщение отредактировал ikki - Понедельник, 14.01.2013, 11:39

Ответить

Alex_ST

Дата: Понедельник, 14.01.2013, 12:44 | Сообщение № 9

Группа: Друзья

Ранг: Участник клуба

Сообщений: 3209

Репутация: 609 ±

Замечаний: 0% ±

2003

[vba]

Код

Sub FindAndClearEmptyInFormulas()
     Dim rCell As Range
     With Application: .ScreenUpdating = False: .EnableEvents = False: .Calculation = xlManual: End With
     For Each rCell In ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)
        If rCell.Value = "" Then rCell.ClearContents
     Next rCell
     With Application: .ScreenUpdating = True: .EnableEvents = True: .Calculation = xlAutomatic: End With
End Sub

[/vba]
Отключение обработки событий, обновления экрана и пересчёта ячеек на время работы макроса обычно даёт значительное ускорение его работы

С уважением,
Алексей
MS Excel 2003 - the best!!!

Сообщение отредактировал Alex_ST - Понедельник, 14.01.2013, 12:47

Ответить

Страница 1 из 1
1