Как узнать, какие строки выделил пользователь в отфильтрованной умной таблице?

[angel2s2]

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

Прошу вашей помощи.

На листе есть умная таблица (ListObject). Пользователь с помощью фильтра умной таблицы (ListObjects("Table1").Range.AutoFilter) фильтрует таблицу, потом мышкой выделяет какой-то диапазон ячеек.
Как определить, какие строки выделены? Нужны только те строки, которые в данный момент отображаются на листе, т.е. только отфильтрованные строки.
Имею ввиду в контексте
ListObjects("Table1").ListRows(X)

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

Не зная что делать с этими строками подсказывать решение сложно. Все нужное делается элементарно при помощи specialCells:

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

Dim rr as range
Set rr = Selection 'только среди выделенных. Но можно и во всей умной таблице: ActiveSheet.ListObjects("Table1").Range
MsgBox rr.SpecialCells(xlCellTypeVisible).EntireRow.Address

[vikttur]

angel2s2,  своих темах, которые разместили на других форумах, дайте ссылки на решения. которые получили здесь.

[angel2s2]

Со specialCells еще не был знаком. Спасибо, изучу. Вижу она мне много где еще пригодится.

Код возвращает номер строки в контексте Worksheet, а не в контексте ListObjects("Table1").ListRows(XXX).
Есть ли возможность преобразовать номер строки из контекста Worksheet в контекст ListObjects("Table1").ListRows(XXX) ?

Не знаю, может я не верно выражаюсь... Имею ввиду, что ваш код возвращает номер строки, например, 6 и это номер строки на самом листе. А в ListObject это уже будет 4 строка.

В моем случае приходится работать через Intersect, т.к. заранее не известно, в каком порядке будут идти столбцы (пользователи иногда изменяют их порядок). Поэтому я не могу напрямую прочитать или записать в ячейку, скажем, B6, ибо не знаю, что там будет в тот или иной момент времени.

[angel2s2]

angel2s2,  своих темах, которые разместили на других форумах, дайте ссылки на решения. которые получили здесь.

Так и планирую сделать. Но пока решения еще не нашел.

[vikttur]

Нужно показывать ссылки, если есть хоть один ответ, иначе помогающие могут тратить свое время, предлагая то, что уже есть.

[angel2s2]

Об этом как-то не подумал... Сделал.
Раньше всегда старался размещать ссылку на ответ-решение, полученный на другом форуме.

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

мышкой выделяет какой-то диапазон ячеек.
Как определить, какие строки выделены?

решение именно для выделенного диапазона я и дал. Вы бы задачу для начала описали более конкретно. А там может и верное решение будет.

Код возвращает номер строки в контексте Worksheet, а не в контексте ListObjects("Table1").ListRows(XXX)

да. А что должен возвращать, если просили определить строки, выделенные пользователем? И что значит "в контексте таблицы"? А если пользователь выделит строки, которые захватывают и строки умной таблицы и строки вне этой таблицы? Каким должен быть результат?

код возвращает номер строки, например, 6 и это номер строки на самом листе. А в ListObject это уже будет 4 строка

что мешает определить разницу между выделенной строкой и первой строкой умной таблицы? И почему Вы всегда оперируете только свойством ListRows? У умных таблиц есть и другие свойства

[angel2s2]

Дмитрий, спасибо за ответы. Попробую развернуто ответить на ваши вопросы. Сразу оговорюсь, опыта в экселе у меня не много, фактически первый месяц идет как начал на VBA писать.

Вы бы задачу для начала описали более конкретно. А там может и верное решение будет.

У меня большая умная таблица (ListObject) на листе, с большим количеством столбцов. Таблицу наполняют разные пользователя. Один пользователь отвечает за предпечатную подготовку. Что для этого нужно? Пользователю нужно по столбцу "Номер" отобрать строки, в которых есть номера из списка (список номеров пользователю поступает по другому каналу - почта, звонок, устно, мессенджер). Далее нужно скопировать в другую (новую) книгу эти отобранные строки, но не целиком, а только определенные столбцы (номер, фамилия, имя, отчество, срок действия, файл фотографии) и сохранить ее в какую-либо папку. Потом нужно найти фотографии, имена которых есть в отобранных строках (столбец "файл фотографии") и скопировать их в эту же папку. Потом эту папку передать на печать.

Что я хочу сделать.
Я хочу иметь возможность выделить мышкой ячейки, в которых есть требуемые номера (при этом таблица может быть отфильтрована), нажать кнопку и получить готовую папку с новой книгой, в которой записаны выделенные строки (только нужные столбцы, о которых выше писал) и сами файлы фотографий.

Как я вижу решение (копирование файлов фотографий не рассматриваем, это уже реализовал).
Определить номера выделенных строк в контексте ListObject. Создать новую книгу, в ней создать умную таблицу и далее в цикле с помощью Intersect записать содержимое ячеек, которые находятся в выделенных строках и нужных столбцах.

А что должен возвращать, если просили определить строки, выделенные пользователем?

Написал как смог... Получается не достаточно понятно. Виноват.
Писал именно о номерах строках умной таблицы, а не самого листа. Умная таблица начинает не в A1.

И что значит "в контексте таблицы"?

Как бы это объяснить иначе...
К ячейке листа мы обращаемся так -

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

ThisWorkbook.Worksheets(1).Cells(3,1)
А к ячейке ListObject можно обращаться так

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

Intersect(ThisWorkbook.Worksheets(1).ListObjects("Table1").ListRows(1).Range, ThisWorkbook.Worksheets(1).ListObjects("Table1").ListColumns("Number").DataBodyRange)
В данном случае это одна и та же ячейка.
Почему Intersect? Потому что я не знаю, в каком порядке будут идти столбцы. А делать перебор столбцов в цикле, чтобы найти где какой, не хочется. Да и как-то не правильным мне это кажется.

А если пользователь выделит строки, которые захватывают и строки умной таблицы и строки вне этой таблицы? Каким должен быть результат?

Нужно проигнорировать строки вне умной таблицы. Интересуют только те строки, которые в пределах умной таблицы.
Я пока смог только такой код сделать, он возвращает номер строки умной таблицы в контексте ListRows, но не правильно работает, если выделение за пределами умной таблицы.

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

    Dim r As Long
    Dim i As Long
    Dim j As Long
    Dim lo As ListObject: Set lo = ThisWorkbook.Worksheets(1).ListObjects("Table1")
    Dim ss As Range: Set ss = Selection
   
    Select Case ss.Cells.Rows.Count
        Case 1
            'выделена одна строка
            Debug.Print ss.Cells.Row - lo.HeaderRowRange.Cells(1).Row
        Case Is > 1
            'выделено несколько строк
             For i = 1 To ss.SpecialCells(xlCellTypeVisible).Areas.Count
                For j = 1 To ss.SpecialCells(xlCellTypeVisible).Areas.Item(i).Rows.Count
                    Debug.Print ss.SpecialCells(xlCellTypeVisible).Areas.Item(i).Rows(j).Row - lo.HeaderRowRange.Cells(1).Row
                Next j
             Next i
    End Select

Понимаю, что можно проверить, входит ли строка в умную таблицу или нет. Но пока не разобрался еще...

что мешает определить разницу между выделенной строкой и первой строкой умной таблицы?

На момент задавания вопроса еще не знал, как это сделать. Подсказали в соседней теме.
Я думал, есть какая-то встроенная функция, которая преобразовывает range в номер строки ListObject. Но похоже такой не существует...

И почему Вы всегда оперируете только свойством ListRows? У умных таблиц есть и другие свойства

Потому-что я читаю и пишу умные таблицы через Intersect (т.к. заранее не знаю, где какой столбец), а ему нужно передавать номер строки в контексте ListObject("Table1").ListRows... Т.е. ему нужно указывать номер строки самой умной таблицы, а не номер строки листа. Я не претендую на истину, но я так понял эту функцию. Если не прав, объясните пожалуйста.
Еще одна причина, по которой использую Intersect. Когда работаю с UserForms, то пишу в свойство Tag контролла название столбца, в который должны записываться значения из этого контролла, что очень облегчает написание кода. Ну и еще причина в том, что в этой книге уже очень много кода, который использует Intersect и переписывать его не хочется.



PS: Надеюсь мне удалось понятно ответить на ваши вопросы.

[angel2s2]

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

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


    Dim i As Long
    Dim j As Long
    Dim RowNumber As Integer
    Dim lo As ListObject
        Set lo = ThisWorkbook.Worksheets(1).ListObjects("Table1")
    Dim rr As Range
        Set rr = lo.DataBodyRange
    Dim ss As Range
        Set ss = Selection
   
    Select Case ss.Cells.Rows.Count
        Case 1
            'выделена одна строка
            RowNumber = ss.Cells.Row - lo.HeaderRowRange.Cells(1).Row
            If RowNumber >= 1 And RowNumber <= rr.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeLastCell).Row - lo.HeaderRowRange.Cells(1).Row Then
                Debug.Print RowNumber
            Else
                RowNumber = -1
                Debug.Print RowNumber
            End If
        Case Is > 1
            'выделено несколько строк
             For i = 1 To ss.SpecialCells(xlCellTypeVisible).Areas.Count
                For j = 1 To ss.SpecialCells(xlCellTypeVisible).Areas.Item(i).Rows.Count
                    RowNumber = ss.SpecialCells(xlCellTypeVisible).Areas.Item(i).Rows(j).Row - lo.HeaderRowRange.Cells(1).Row
                    If RowNumber >= 1 And RowNumber <= rr.SpecialCells(xlCellTypeVisible).SpecialCells(xlCellTypeLastCell).Row - lo.HeaderRowRange.Cells(1).Row Then
                        Debug.Print RowNumber
                    End If
                Next j
             Next i
    End Select


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

Еще одна причина, по которой использую Intersect

а я про Intersect вообще не спрашивал Я про ListRows спрашивал.
По факту. Проще сначала определить, есть ли вообще выделение внутри именно умной таблицы. А потом обрабатывать уже именно те строки, что выделены внутри умной таблицы. Например, так:

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

Sub test()
    Dim rr As Range, rrow As Range, rColCell As Range
    Dim lo As ListObject
    Set lo = ThisWorkbook.Worksheets(1).ListObjects("Table1")
    Set rr = Intersect(lo.DataBodyRange, Selection.EntireRow)
    If rr Is Nothing Then 'выделена область за умной таблицей
        Exit Sub
    End If
    For Each rrow In rr.SpecialCells(xlCellTypeVisible).Rows
        'считываем значение выделенных строк столбца Number умной таблицы
        Set rColCell = Intersect(lo.ListColumns("Number").DataBodyRange, rrow)
        MsgBox rColCell.Value
    Next
End Sub
при таком подходе не придется даже проверять не входит ли обрабатываемый диапазон в заголовок. Браться будут всегда именно данные самой таблицы.

[angel2s2]

Вау ! У меня слов нет! Как красиво и изящно! И работает отлично. Гораздо лучше чем у меня было.

Мегаспасибище!!!!! 


Хотел так же ваш код применить к удалению выделенных строк... Да вот незадача возникла...
rr.SpecialCells(xlCellTypeVisible).Delete
Вот так работает (удаляет без вопросов), но только если не включен фильтр. Если таблица отфильтрована, то показывает диалоговое окно "Удалить всю строку листа?"

rr.SpecialCells(xlCellTypeVisible).EntireRow.Delete
Так тоже работает, если нет фильтра. А если фильтр применен, то выдает ошибку 1004 "Application-defined or object-defined error".
Не могу понять, что не так делаю... Как удалить эти строки без вопросов.

Можно ли обойти показ диалогового окна "Удалить всю строку листа?"

а я про Intersect вообще не спрашивал Я про ListRows спрашивал.

Я и не знал, что ее можно по другому использовать. Например, про EntireRow только вчера узнал. Другими словами, думал что она только для работы с ListObject и что ей надо указывать строку и столбец (имею ввиду ListRows и ListColumns), на пересечении которых будет нужная ячейка. Но что можно ей указать обычный range даже в голову не приходило.
Да что там, я и сейчас не сразу понял как работает код, словно магия какая-то... Потом подумал хорошенько, посмотрел какие типы данных на входе, на выходе, какие .address у используемых range и понял.
Не программист я, сисадмин 

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

Можно ли обойти показ диалогового окна "Удалить всю строку листа?"

Попробуйте воспользоваться этим методом: Как запретить сообщения?

[angel2s2]

Помогло!

Спасибо ОГРОМНОЕ!!!!!


PS: Надеюсь ваш пункт приёма Спасибов на яндекс работает )))