В excel 2019 SpecialCells(xlCellTypeLastCell) не корректно работает после Sort

[angel2s2]

Коллеги, добрый день.

Помогите, пожалуйста, разобраться.
Есть код, который сортирует таблицу по полю "Дата", потом фильтрует ее по полю "Оператор", а далее считывает дату из последней видимой строки.
В Excel 2016 все работает замечательно. Но вот в 2019 странное поведение...

Код (vb) Выделить Развернуть

lo.DataBodyRange.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeLastCell).EntireRow
возвращает значение "последняя_видимая_строка + 1", т.е., например, если последняя видимая строка 4, то возвращает 5.
При этом, если убрать "SpecialCells(xlCellTypeLastCell)", то все правильно (в смысле, возвращает только видимые строки).
Если удалить из кода сортировку, то тоже работает правильно.

Сталкивался кто-нибудь с таким поведением? Как это можно исправить, чтобы и в 2016 и в 2019 работало корректно?

Код (vb) Выделить Развернуть

Dim lo As ListObject: Set lo = ThisWorkbook.Worksheets("Лист1").ListObjects("Таблица1")
Dim OperatorNumber As Long: OperatorNumber = 7
Dim LastDateRecord As String

On Error Resume Next
lo.AutoFilter.ShowAllData
lo.Sort.SortFields.Clear
On Error GoTo 0

lo.Sort.SortFields.Add lo.ListColumns("Дата").DataBodyRange, xlSortOnValues, xlAscending, , xlSortTextAsNumbers
lo.Sort.Header = xlYes
lo.Sort.MatchCase = False
lo.Sort.Apply

lo.Range.AutoFilter Field:=16, Criteria1:="=" & OperatorNumber & "", Operator:=xlAnd

LastDateRecord = Intersect(lo.DataBodyRange.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeLastCell).EntireRow, _
                                                        lo.ListColumns("Дата").DataBodyRange).Value


[Дмитрий Щербаков(The_Prist)]

Без файла примера сказать что-то конкретное сложно. Может есть объединенные ячейки или еще что.

[angel2s2]

Нет, объединенных ячеек нету. На листе только классическая умная таблица, которая начинается со второй строки, а в первой строке пара кнопок для вызова макросов.

Нагуглил вот эту статью - https://www.excel-vba.ru/chto-umeet-excel/kak-opredelit-poslednyuyu-yachejku-na-liste-cherez-vba/

Из нее взял "СПОСОБ 3: Определение последней строки через UsedRange" и переписал свой код вот так:

Код (vb) Выделить Развернуть

Dim lo As ListObject: Set lo = ThisWorkbook.Worksheets("Лист1").ListObjects("Таблица1") 
Dim OperatorNumber As Long: OperatorNumber = 7 
Dim LastDateRecord As String 
 
On Error Resume Next 
lo.AutoFilter.ShowAllData 
lo.Sort.SortFields.Clear 
On Error GoTo 0 
 
lo.Sort.SortFields.Add lo.ListColumns("Дата").DataBodyRange, xlSortOnValues, xlAscending, , xlSortTextAsNumbers 
lo.Sort.Header = xlYes 
lo.Sort.MatchCase = False 
lo.Sort.Apply 
 
lo.Range.AutoFilter Field:=16, Criteria1:="=" & OperatorNumber & "", Operator:=xlAnd 
 
LastDateRecord = Intersect(ThisWorkbook.Worksheets(DataSheet).UsedRange.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeLastCell).EntireRow, _ 
                                                        lo.ListColumns("Дата").DataBodyRange).Value 


Фактически, изменил только одну строку, т.е. заменил

Код (vb) Выделить Развернуть

lo.DataBodyRange.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeLastCell).EntireRow
на

Код (vb) Выделить Развернуть

ThisWorkbook.Worksheets(DataSheet).UsedRange.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeLastCell).EntireRow
и все заработало.

Так и не смог понять, в чем было дело.


Сейчас в дороге. Вечером дома сделаю файл-пример с кодом и пришлю.

[Дмитрий Щербаков(The_Prist)]

Для начала лучше определиться что и откуда именно надо получить - с листа в целом или только из умной таблицы. Т.к. UsedRange это весь используемый диапазон листа, который может быть значительно больше одной умной таблицы.

[angel2s2]

Да, я это понимаю.
У меня на листе данные только в умной таблице. Сам лист защищен паролем, пользователь данные вводит через форму, а форма пишет только в умную таблицу.
Думаю в таком варианте UsedRange тоже подходит.

Но в целом, хочется понять, почему один и тот же код, в одной и той же таблице, в одном и том же файле, ведет себя по разному в 2016 и 2019.

[Дмитрий Щербаков(The_Prist)]

Думаю в таком варианте UsedRange тоже подходит

В общем да, но разницы между применять UsedRange или только ячейки таблицы в данном случае нет.
Плюс, надо было в статье чуть внимательнее почитать про SpecialCells:
1. Этот метод не работает при установленной защите листа
2. Если нужна последняя именно видимая ячейка Вам более чем подходит метод определения последней ячейки СПОСОБОМ 1(из той же статьи). Т.е. через (xlUp).Row

хочется понять, почему один и тот же код, в одной и той же таблице, в одном и том же файле, ведет себя по разному в 2016 и 2019

про это уже говорил:

Без файла примера сказать что-то конкретное сложно

гадать нет желания. А в целом даже гадать особо не стоит - просто надо использовать решение, которое будет корректно работать во всех версиях.

[angel2s2]

Приложил файл.
sub test1 повесил на кнопку слева, в test2 - справа.
В 2016 оба варианта работают как надо, в 2019 test1 выдает ошибку "Object variable or With block variable not set". Оно и понятно... Строка и столбец в intersect не пересекают, он возвращает nothing.

Плюс, надо было в статье чуть внимательнее почитать про SpecialCells:
1. Этот метод не работает при установленной защите листа

Хм... В 2019 НЕ работает и с защитой листа и без (если предварительно была сделана сортировка).
Я перед фильтрами, сортировками и записью на лист снимаю защиту и после - ставлю снова (в реальном рабочем файле перед кодом, приведенным выше защита, снимается). UserInterfaceOnly:=True при установке защиты указываю, но это не помогает. Все равно не дает писать и сортировать.

Код (vb) Выделить Развернуть

Sub WorksheetProtect(pEnable As Boolean)
    If pEnable Then
        ThisWorkbook.Worksheets(DataSheet).EnableOutlining = True
        ThisWorkbook.Worksheets(DataSheet).Protect Password:=SheetProtectPass, UserInterfaceOnly:=True, _
            DrawingObjects:=True, _
            Contents:=True, _
            Scenarios:=True, _
            AllowFormattingCells:=True, _
            AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, _
            AllowInsertingColumns:=True, _
            AllowInsertingRows:=True, _
            AllowInsertingHyperlinks:=True, _
            AllowSorting:=True, _
            AllowFiltering:=True, _
            AllowUsingPivotTables:=True, _
            AllowDeletingColumns:=False, _
            AllowDeletingRows:=False
    Else
        ThisWorkbook.Worksheets(DataSheet).unprotect Password:=SheetProtectPass
    End If
End Sub


PS: Еще заметил вот что... Если выполнить код из test1, будет ошибка. Далее, если убрать сортировку, то снова будет ошибка. Чтобы ошибка исчезла (без сортировки) нужно расширить умную таблицу на 1 или более строк, а потом выделить эти строки и удалить (я удалял целиком строки, т.к. Shift+Space, Shift+Space, Ctrl+Shift+Down, Ctrl+NumMinus ). После этого код без сортировки работает верно. Если снова в код добавить сортировку, то снова ошибка. Словно excel 2019 какую-то невидимую строку добавляет в конце таблицы.

[angel2s2]

PPS: Что я хочу получить этим кодом. Самую последнюю дату (в реальном файле дата и время) по заданному оператору.
Блин... Чего я фигней страдаю... Отсортировать по дате от старых к новым и прочитать первую строку...

Код (vb) Выделить Развернуть

FirstVisibleRow = lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows(1).EntireRow.Row
LastDateRecord = Intersect(lo.ListRows(FirstVisibleRow).Range, lo.ListColumns("Дата").DataBodyRange).Value
Вот жеж я тормоз, высыпаться надо

[Дмитрий Щербаков(The_Prist)]

Словно excel 2019 какую-то невидимую строку добавляет в конце таблицы

никто ничего не добавляет. И по сути Ваш первый вариант более правильный с точки зрения логики, если Вам нужна последняя ячейка именно в умной таблице.
Да и второй код тоже "типа добавляет строку". Если добавить сразу после фильтрации строку:

Код (vb) Выделить Развернуть

MsgBox Selection.SpecialCells(xlCellTypeLastCell).EntireRow.Address
то можно проследить этот момент. Нет-нет да проскочит номер 13 Просто во втором коде Вы не привязываетесь к умной таблице при определении последней ячейки. Это и спасает.
Но в любом случае Ваш код даст сбой, когда на листе вдруг внезапно ниже умной таблицы хотя бы ячейку каким-нибудь цветом закрасят или еще хуже, значение запишут.
Попробуйте лучше использовать такой вариант:

Код (vb) Выделить Развернуть

   Dim lcnt&, rr As Range
   'отфильтрованные ячейки в столбце Дата
   Set rr = lo.ListColumns("Дата").DataBodyRange.SpecialCells(xlCellTypeVisible)
   'кол-во ячеек в последней области
   lcnt = rr.Areas(rr.Areas.Count).Cells.Count
   lastRowNumber = rr.Areas(rr.Areas.Count).Cells(lcnt).Row
   LastDateRecord = lo.Parent.Cells(lastRowNumber, lo.ListColumns("Дата").DataBodyRange.Column).Value

[angel2s2]

На счет кода с "UsedRange", "End(xlUp)" и им подобных полностью согласен. Понимаю, что может быть непредсказуемый результат.
Второй вариант сделал от безысходности на тот момент, как временное решение + успокаивало, что лист защищен. Реально другого решения не видел.
А потом осенило, что можно просто наоборот отсортировать и считать первую видимую строку таблицы (Ответ #7). Или так делать не стоит?


За перебор Areas отдельный низкий поклон! Как-то бодался с ним в первые дни экселинга, но он меня забодал. Точнее в ходе бодания открыл для себя SpecialCells 

[Дмитрий Щербаков(The_Prist)]

Или так делать не стоит?

не знаю, все от задачи зависит. Если речь только об этом:

Что я хочу получить этим кодом. Самую последнюю дату (в реальном файле дата и время) по заданному оператору

то даже сортировать ничего не надо

Код (vb) Выделить Развернуть

Set rr = lo.ListColumns("Дата").DataBodyRange.SpecialCells(xlCellTypeVisible)
LastDateRecord = CDate(Application.Max(rr))

[angel2s2]

Да вы "издеваетесь" 
Даже в голову не приходило, что в VBA есть стандартные экселевские функции (в смысле, что их можно использовать без записи в какую-нибудь ячейку). Чую многому мне еще придется удивляться...
Спасибо большое!
Именно это мне и нужно, узнать самую свежую (последнюю) дату по определенному оператору (чтобы потом из другого файла загрузить только те записи по оператору, которые новее имеющихся в моем файле).