Макрос поиска видимых строк и заполненных ячеек на листе Excel

Если ваш макрос выдаёт ошибку при использовании метода SpecialCells - возможно, причина в установленной защите листа Excel.

Почему разработчики Microsoft отключили работу этой функции на защищённых листах - не совсем понятно, но мы попробуем обойти это ограничение.

 

Итак, нам надо получить все заполненные ячейки из некого диапазона листа Excel. 

Обычно для этого используется вызов метода SpecialCells - например,

msgbox Range("a2:d8").SpecialCells(xlCellTypeConstants).Address

Но на защищенном листе такой код выдаст ошибку 1004.

Чтобы избавиться от ошибки, мы используем функцию SpecialCells_TypeConstants - замену встроенному методу SpecialCells(xlCellTypeConstants)

 

Function SpecialCells_TypeConstants(ByRef ra As Range) As Range
    ' возвращает диапазон, содержащий все заполненные ячейки диапазона ra
    On Error Resume Next: en& = Err.Number
    If ra.Worksheet.ProtectContents Then    ' если лист защищён
        Dim cell As Range
        ' перебираем все ячейки в диапазоне
        For Each cell In Intersect(ra, ra.Worksheet.UsedRange).Cells
            If Trim(cell.Value) <> "" Then    ' если ячейка непустая
                ' то добавляем её в результат
                If SpecialCells_TypeConstants Is Nothing Then
                    Set SpecialCells_TypeConstants = cell
                Else
                    Set SpecialCells_TypeConstants = Union(SpecialCells_TypeConstants, cell)
                End If
            End If
        Next cell
 
    Else    ' если защита листа не установлена - используем штатные средства Excel
        Set SpecialCells_TypeConstants = ra.SpecialCells(xlCellTypeConstants)
    End If
    If en& = 0 Then Err.Clear
End Function

Теперь наш код (работающий в т.ч. и на защищённых листах) будет выглядеть так:

msgbox SpecialCells_TypeConstants(Range("a2:d8")).Address


Аналогичная функция, если нам надо получить диапазон видимых (нескрытых) строк на листе Excel: 

(замена для SpecialCells(xlCellTypeVisible))

Function SpecialCells_VisibleRows(ByRef ra As Range) As Range
    On Error Resume Next: en& = Err.Number
    If ra.Worksheet.ProtectContents Then
        Dim ro As Range
        For Each ro In Intersect(ra, ra.Worksheet.UsedRange.EntireRow).Rows
            If ro.EntireRow.Hidden = False Then
                If SpecialCells_VisibleRows Is Nothing Then
                    Set SpecialCells_VisibleRows = ro
                Else
                    Set SpecialCells_VisibleRows = Union(SpecialCells_VisibleRows, ro)
                End If
            End If
        Next ro
    Else
        Set SpecialCells_VisibleRows = ra.SpecialCells(xlCellTypeVisible)
    End If
    If en& = 0 Then Err.Clear
End Function

Комментарии

Настройки просмотра комментариев

Выберите нужный метод показа комментариев и нажмите "Сохранить установки".

Игорь, добрый день.
Код безусловно полезный, но перебор ячеек на листе с парой-тройкой сотен тысяч строк и несколько десятков столбцов будет занимать много времени... Возможно стоит ограничить перебор, разбив его хотя бы на 2 части: найти вначале крайний справа столбец и последнюю строчку. После чего обрабатывать выбранный диапазон...
Что касается определения столбца применяя UsedRange - столкнулся с проблемой: если на "свежем" листе выполнить х=Sheets(SheetsName).UsedRange.Column + Sheets(SheetsName).UsedRange.Columns.Count , то количество столбцов отобразится корректно, если удалить несколько столбцов (и не переоткрывать с сохранением книгу), то диапазон берется как до удаления столбцов... Применение SpecialCells у меня не всегда дает корректный результат (ячейки разнородные по наполнению и когда попадается с ошибкой (битая ссылка), Excel воспринимает её буквально :)). По этой причине предлагаю вариант такой - брать через UsedRange и потом "минусовать" пустые столбцы в обратном порядке. Аналогично со строками.

А какое отношение ваш вопрос имеет к теме статьи?
Можно формулу написать, выводящую пропущенное число, можно то же самое сделать макросом, - вариантов много.

у меня вопрос как сделать так чтоб Excel мог автоматический находить на листе ошибки, к примеру если на листе идет последовательность чисел 1,2,3,5,6,7-получается что цифры 4 нет в списке, и как сделать что Excel мог автоматический показывать что такого числа нет тоесть указывать на эту ошибку?

Отправить комментарий

Содержание этого поля является приватным и не предназначено к показу.
CAPTCHA
Подтвердите, пожалуйста, что вы - человек:
  _   _                   __   __          _   
| | | | __ __ ____ \ \ / / __ _ | |_
| |_| | \ \ /\ / / |_ / \ V / / _` | | __|
| _ | \ V V / / / | | | (_| | | |_
|_| |_| \_/\_/ /___| |_| \__, | \__|
|___/
Введите код, изображенный в стиле ASCII-арт.

Не получается применить макрос? Не удаётся изменить код под свои нужды?

Оформите заказ у нас на сайте, не забыв прикрепить примеры файлов, и описать, что и как должно работать.