Tooprogram.ru

Компьютерный справочник
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Vba excel пустое значение

Пустая ячейка определяется Excel-ем как не пустая. Что за глюк?

Эта статья будет близка тем, кому приходится часто работать с отчетами, выгруженными в Excel из программ вроде 1С, SAP и им подобных. Хотя, справедливости ради, надо отметить, что и в обычных файлах Excel такая проблема может так же встречаться, особенно, если в файле использовались различные формулы.
Вводные данные: есть отчет который выкачивается из сторонней программы. В нем есть ячейки, с виду пустые: в них нет пробелов, нет переносов на строки, никаких символов, нет объектов, условного форматирования, в настройках не стоит скрывать нули(ФайлПараметрыДополнительноПоказывать нули в ячейках, которые содержат нулевые значения). Даже если перейти в режим редактирования ячейки — там пусто.
Если попробовать найти такие «пустые» ячейки(выделить все ячейки листа — F5 — Выделить — Пустые ячейки ) — они не выделяются. Но фильтр при этом их видит как пустые и фильтрует как пустые.
Любые математические действия(умножение на ячейку, сложение, деление и т.п.) внутри формул с такими ячейками выдают ошибку #ЗНАЧ! (#VALUE!) , а функция ЕПУСТО (ISBLANK) считает ячейку не пустой. Формулы вроде СУММ (SUM) и СЧЁТ (COUNT) игнорируют такие ячейки, а СЧЁТЗ (COUNTA) считает их заполненными.

И самое удивительное — если выделить такую ячейку вручную и нажать Delete (или вкладка Главная -группа РедактированиеОчистить содержимое) — то ячейка становится действительно пустой и с ней начинают работать формулы и другие функции Excel как с реально пустой.
Здесь возникает главный вопрос: что с этой ячейкой не так, если там и до Delete ничего не было?
А не так с ней вот что: практически во всех программах есть такое понятие строковых(текстовых) данных, как строка нулевой длины(еще её часто называют нулевая строка. В Visual Basic for Application такая строка обозначается константой vbNullString , в ячейках её можно встретить как сочетание двух кавычек подряд — «» ). Визуально такой текст себя никак не проявляет, однако это все же текст. А любой текст это уже не пусто, но и не число.
Откуда может появится такой текст в ячейках? Здесь несколько вариантов:

  1. Он есть в ячейках изначально, потому что именно так настроена выгрузка и создание файлов в сторонней программе(вроде 1С). В некоторых случаях такие выгрузки настроены таким образом, что как таковых пустых ячеек нет — они просто заполняются строкой нулевой длины.
  2. в ячейке была записана формула, результатом которой стал текст нулевой длины. Самый простой пример такой формулы:
    =ЕСЛИ( A1 =1;10;»»)
    =IF(A1=1,10,»»)
    в итоге, если в ячейке A1 записано любое значение, отличное от 1 формула вернет строку нулевой длины. И если впоследствии формулу заменять значением(Как удалить в ячейке формулу, оставив значения?), то получим нашу псевдо пустую ячейку.
    Если формулы создаются Вами и в дальнейшем планируются производить с этими ячейками математические действия, то лучше вместо «» ставить 0. Тогда проблем не будет. Нули всегда можно заменить или скрыть: ФайлПараметрыДополнительноПоказывать нули в ячейках, которые содержат нулевые значения
Читать еще:  Вставить содержание в word

А если такой файл делали не Вы — он достался «по наследству» или это выгрузка из другой программы, что делать тогда? Я предлагаю такой вот нехитрый код, который во всех выделенных ячейках заменит строки нулевой длины на нормальные пустые ячейки:

Sub ReplaceNullString() Dim rR As Range, rF As Range, rC As Range Dim avR, lr As Long, lc As Long Set rR = Intersect(ActiveSheet.UsedRange, Selection) On Error Resume Next Set rR = rR.SpecialCells(xlCellTypeConstants) On Error GoTo 0 If rR Is Nothing Then MsgBox «В выделенных ячейках нет значений!», vbInformation, «www.excel-vba.ru» Exit Sub End If Set rF = rR.Find(vbNullString, , xlFormulas, xlWhole) If Not rF Is Nothing Then avR = rR.Value For lr = 1 To UBound(avR, 1) For lc = 1 To UBound(avR, 2) If avR(lr, lc) = «» Then rR.Item(lr, lc).Value = Empty Next lc Next lr MsgBox «Строки нулевой длины заменены», vbInformation, «www.excel-vba.ru» Exit Sub End If MsgBox «Строк нулевой длины на листе нет или лист защищен», vbInformation, «www.excel-vba.ru» End Sub

Как это работает:
если раньше никогда не работали с макросами, то рекомендую ознакомиться со статьями:
Что такое макрос и где его искать?
Что такое модуль? Какие бывают модули?

  1. создаем в книге новый стандартный модуль: Alt+F11InsertModule()
  2. копируем в созданный модуль приведенный выше код
  3. выделяем нужный диапазон(если надо заменить на всем листе — то можно выделить все ячейки листа или целиком нужные столбцы — программа сама определить нужные данные)
  4. нажимаем Alt+F8 и в появившемся окне выбираем ReplaceNullString

Короткая видеоинструкция:

Статья помогла? Поделись ссылкой с друзьями!

Vba excel пустое значение

Проверь длину строки, например.

Dim s As String

If (Len(s) <> 0) Then
MsgBox («Кулллл!»)
End If

If (s <> «») Then
MsgBox («Кулллл!»)
End If

От:Аноним
Дата:15.03.05 12:31
Оценка:

Здравствуйте, Hades, Вы писали:

Строка — имеется в виду row, т.е. ячейки, расположенные на одинаковом горизонтальном уровне

От: Unforgiver
Дата:15.03.05 12:54
Оценка:

Здравствуйте, Аноним, Вы писали:

А>Как с помощью vba в Excel таблице проверить является ли данная строка пустой (без данных, без оформления) или нет. Просто мне необходимо проходить все строки таблицы и выдёргивать нужную информацию. Но среди нужных строк иногда попадаются пустые строки, которые нужно игнорировать.

Включи обработчик ошибок:

Если строка пустая, то с ней ничего не сделается и выполнение пойдет дальше.

От: Elena_
Дата:15.03.05 13:43
Оценка: 4 (1)

Здравствуйте, Аноним, Вы писали:

А>Строка — имеется в виду row, т.е. ячейки, расположенные на одинаковом горизонтальном уровне

Ну если по содержанию ячеек, то перебрать строки можно, например, так

А по оформлению, может быть, нужно и смотреть оформление отдельно, надо подумать

От:Аноним
Дата:16.03.05 12:32
Оценка:

Здравствуйте, Аноним, Вы писали:

А>Как с помощью vba в Excel таблице проверить является ли данная строка пустой (без данных, без оформления) или нет. Просто мне необходимо проходить все строки таблицы и выдёргивать нужную информацию. Но среди нужных строк иногда попадаются пустые строки, которые нужно игнорировать.

Да чего вы изголяетесь, пробежаться по всем строкам и по всем ячейкам в строке и не париться.
Вот прмер отчёта в Intermediate о «путотости»/»непустотости» каждой строки в активном листе:

PS: Задолбаетесь ждать, пока макрос отработает — жмите +. Это я так, на всяк.

От: Elena_
Дата:16.03.05 13:49
Оценка:

Здравствуйте, Аноним, Вы писали:

А>Да чего вы изголяетесь, пробежаться по всем строкам и по всем ячейкам в строке и не париться.
А>Вот прмер отчёта в Intermediate о «путотости»/»непустотости» каждой строки в активном листе:

А>

Но зачем проверять даже те ячейки, которые не попали в UsedRange?
Зачем проверять каждую ячейку, если можно проверить сразу диапазон?

От:Аноним
Дата:18.03.05 13:44
Оценка:

Здравствуйте, Elena_, Вы писали:

E_>Но зачем проверять даже те ячейки, которые не попали в UsedRange?
E_>Зачем проверять каждую ячейку, если можно проверить сразу диапазон?
Согласен, но тогда «отмажусь» нехваткой времени ==> сыростью программы.
+ тут уже есть пример с использованием UserRange.

PS: В 97’м ёкселе (были слухи что и в 2000’м) «SpecialCells(xlCellTypeBlanks)» не всегда корректно работает.

От: Elena_
Дата:18.03.05 20:35
Оценка:

Здравствуйте, Аноним, Вы писали:

А>PS: В 97’м ёкселе (были слухи что и в 2000’м) «SpecialCells(xlCellTypeBlanks)» не всегда корректно работает.

В общем-то, если пустых ячеек совсем нет, то действительно некорректно получается

Я имела в виду, чтобы не перебирать по ячейке, так как при больших объемах это все-таки будет заметно

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