mail mail
Нужен макрос для Excel?
Сделайте заказ прямо сейчас!
Ищете готовое решение?
Выбирайте и покупайте!
У вас есть интернет-магазин?
Настроим парсер под любой сайт!

Макрос поиска видимых строк и заполненных ячеек на листе 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

Комментарии

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

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

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

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

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

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

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

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