Excel это не сложно
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
21.05.2024, 11:06:35

Войти
Название темы должно отражать её содержание.
Темы типа "ПОМОГИТЕ!!!", "Срочно!", "Не получается сделать", "Нужна помощь" и т.п. будут удаляться без объяснения причин
33 266 Сообщений в 5 461 Тем от 6 769 Пользователей
Последний пользователь: Fortuna
*
Перейти на сайт Хитрости Надстройка MulTEx Обучающие тренинги Наша группа ВКонтакте
Правила форума Начало Помощь Поиск Календарь Войти Регистрация Выйти
+  Excel это не сложно
|-+  Основные форумы
| |-+  Вопросы по Excel и VBA
| | |-+  В excel 2019 SpecialCells(xlCellTypeLastCell) не корректно работает после Sort
Страниц: [1]   Вниз
Печать
Автор Тема: В excel 2019 SpecialCells(xlCellTypeLastCell) не корректно работает после Sort  (Прочитано 4421 раз)
0 Пользователей и 1 Гость смотрят эту тему.
angel2s2
Новичок
*

Репутация: +0/-0
Офлайн Офлайн

Сообщений: 21


Просмотр профиля
« : 05.08.2021, 16:11:49 »

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

Помогите, пожалуйста, разобраться.
Есть код, который сортирует таблицу по полю "Дата", потом фильтрует ее по полю "Оператор", а далее считывает дату из последней видимой строки.
В 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)
Администратор
Ветеран
*****

Репутация: +485/-0
Офлайн Офлайн

Сообщений: 5 838



Просмотр профиля WWW
« Ответ #1 : 05.08.2021, 17:13:47 »

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

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
angel2s2
Новичок
*

Репутация: +0/-0
Офлайн Офлайн

Сообщений: 21


Просмотр профиля
« Ответ #2 : 05.08.2021, 17:33:53 »

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

Нагуглил вот эту статью - 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)
Администратор
Ветеран
*****

Репутация: +485/-0
Офлайн Офлайн

Сообщений: 5 838



Просмотр профиля WWW
« Ответ #3 : 05.08.2021, 17:39:06 »

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

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
angel2s2
Новичок
*

Репутация: +0/-0
Офлайн Офлайн

Сообщений: 21


Просмотр профиля
« Ответ #4 : 05.08.2021, 17:42:28 »

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

Но в целом, хочется понять, почему один и тот же код, в одной и той же таблице, в одном и том же файле, ведет себя по разному в 2016 и 2019.
Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +485/-0
Офлайн Офлайн

Сообщений: 5 838



Просмотр профиля WWW
« Ответ #5 : 05.08.2021, 19:07:38 »

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

Без файла примера сказать что-то конкретное сложно
гадать нет желания. А в целом даже гадать особо не стоит - просто надо использовать решение, которое будет корректно работать во всех версиях.
« Последнее редактирование: 05.08.2021, 19:10:10 от Дмитрий Щербаков(The_Prist) » Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
angel2s2
Новичок
*

Репутация: +0/-0
Офлайн Офлайн

Сообщений: 21


Просмотр профиля
« Ответ #6 : 05.08.2021, 21:03:55 »

Приложил файл.
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
Новичок
*

Репутация: +0/-0
Офлайн Офлайн

Сообщений: 21


Просмотр профиля
« Ответ #7 : 05.08.2021, 21:24:20 »

PPS: Что я хочу получить этим кодом. Самую последнюю дату (в реальном файле дата и время) по заданному оператору.
Блин... Чего я фигней страдаю... Отсортировать по дате от старых к новым и прочитать первую строку...
Код: (vb)
FirstVisibleRow = lo.DataBodyRange.SpecialCells(xlCellTypeVisible).Rows(1).EntireRow.Row
LastDateRecord = Intersect(lo.ListRows(FirstVisibleRow).Range, lo.ListColumns("Дата").DataBodyRange).Value

Вот жеж я тормоз, высыпаться надо Смеющийся
Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +485/-0
Офлайн Офлайн

Сообщений: 5 838



Просмотр профиля WWW
« Ответ #8 : 05.08.2021, 21:47:11 »

Словно 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
« Последнее редактирование: 05.08.2021, 21:54:09 от Дмитрий Щербаков(The_Prist) » Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
angel2s2
Новичок
*

Репутация: +0/-0
Офлайн Офлайн

Сообщений: 21


Просмотр профиля
« Ответ #9 : 05.08.2021, 22:09:38 »

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


За перебор Areas отдельный низкий поклон! Как-то бодался с ним в первые дни экселинга, но он меня забодал. Точнее в ходе бодания открыл для себя SpecialCells  Смеющийся
Записан
Дмитрий Щербаков(The_Prist)
Администратор
Ветеран
*****

Репутация: +485/-0
Офлайн Офлайн

Сообщений: 5 838



Просмотр профиля WWW
« Ответ #10 : 06.08.2021, 10:14:17 »

Или так делать не стоит?
не знаю, все от задачи зависит. Если речь только об этом:
Что я хочу получить этим кодом. Самую последнюю дату (в реальном файле дата и время) по заданному оператору
то даже сортировать ничего не надо Улыбка
Код: (vb)
Set rr = lo.ListColumns("Дата").DataBodyRange.SpecialCells(xlCellTypeVisible)
LastDateRecord = CDate(Application.Max(rr))
Записан

Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
Пункты приёма Спасибов:    -41001332272872  -R298726502453
angel2s2
Новичок
*

Репутация: +0/-0
Офлайн Офлайн

Сообщений: 21


Просмотр профиля
« Ответ #11 : 06.08.2021, 18:22:34 »

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

Powered by MySQL Powered by PHP Powered by SMF 1.1.21 | SMF © 2006-2011, Simple Machines Valid XHTML 1.0! Valid CSS!
Яндекс.Метрика Рейтинг@Mail.ru