Excel макрос умной таблице - Word и Excel - помощь в работе с программами

Работа с умной таблицей из кода VBA Excel. Обращение к ячейкам, строкам и столбцам умной таблицы. Добавление и удаление строк и столбцов.

Обращение к умной таблице

Все примеры кода в этой статье привязаны к таблице с именем «Таблица1», расположенной на активном листе:

Обращение к умной таблице:

ActiveSheet.ListObjects(«Таблица1»)

Обращение к диапазону умной таблицы на рабочем листе:

ActiveSheet.ListObjects(«Таблица1»).Range

Проверяем:

Debug.Print ActiveSheet.ListObjects(«Таблица1»).Range.Address ‘Результат: $B$3:$G$9

Далее все примеры кода VBA Excel, чтобы их не дублировать, будут представлены как аргументы метода Debug.Print.

Обращение к строкам

Работа с умной таблицей — обращение к строке заголовков:

Debug.Print ActiveSheet.ListObjects(«Таблица1»).Range.Rows(1).Address ‘Результат: $B$3:$G$3

Таким же образом можно обращаться и к остальным строкам таблицы (Строка1-Строка6), указывая индекс нужной строки от 2 до 7.

К записям таблицы (Строка1-Строка6) обращаются через коллекцию ListRows, указывая индекс записи от 1 до 6:

With ActiveSheet.ListObjects(«Таблица1»)

Debug.Print .ListRows.Count ‘Результат: 6

Debug.Print .ListRows(1).Range.Address ‘Результат: $B$4:$G$4

Debug.Print .ListRows(2).Range.Address ‘Результат: $B$5:$G$5

End With

Обращение к столбцам

Обращение к третьему столбцу умной таблицы из кода VBA Excel:

With ActiveSheet.ListObjects(«Таблица1»)

‘Обращение через диапазон умной таблицы

Debug.Print .Range.Columns(3).Address ‘Результат: $D$3:$D$9

Debug.Print .Range.Columns.Count ‘Результат: 6

‘Обращение через коллекцию ListColumns

Debug.Print .ListColumns(3).Range.Address ‘Результат: $D$3:$D$9

Debug.Print .ListColumns.Count ‘Результат: 6

End With

Обращение к ячейкам

Работа с умной таблицей — обращение к ячейке «E7» с отображением ее значения:

With ActiveSheet.ListObjects(«Таблица1»)

Debug.Print .Range.Cells(5, 4) ‘Результат: 91

Debug.Print .ListColumns(4).Range(5) ‘Результат: 91

Debug.Print .ListRows(4).Range(4) ‘Результат: 91

End With

Вставка и удаление строк

Вставка новой строки в конец умной таблицы:

ActiveSheet.ListObjects(«Таблица1»).ListRows.Add

Удаление последней строки:

With ActiveSheet.ListObjects(«Таблица1»)

.ListRows(.ListRows.Count).Delete

End With

Вставка новой строки на место пятой записи (Строка5 в таблице) со сдвигом пятой и нижерасположенных записей вниз:

ActiveSheet.ListObjects(«Таблица1»).ListRows.Add 5

Удаление пятой строки:

ActiveSheet.ListObjects(«Таблица1»).ListRows(5).Delete

Вставка и удаление столбцов

Вставка нового столбца в конец умной таблицы из кода VBA Excel:

ActiveSheet.ListObjects(«Таблица1»).ListColumns.Add

Удаление последнего столбца:

With ActiveSheet.ListObjects(«Таблица1»)

.ListColumns(.ListColumns.Count).Delete

End With

Вставка нового столбца на место четвертой графы таблицы со сдвигом четвертой и последующих граф вправо:

ActiveSheet.ListObjects(«Таблица1»).ListColumns.Add 4

Удаление четвертого столбца таблицы:

ActiveSheet.ListObjects(«Таблица1»).ListColumns(4).Delete

Создание и удаление умной таблицы описано в статье VBA Excel. Создание таблицы (умной, обычной)

Источник

Доброго времени суток форумчане. Я являюсь новичком в написании макросов в vba поэтому прошу отнестись с пониманием:)

Для своей работы я пишу достаточную объёмную программу со внесением в таблицу данных через пользовательскую форму с последующей их обработкой. Мне необходимо, чтобы эти данные «перешли» в умные таблицы на других листах того же самого документа Excel. Но для начала эти умные таблицы необходимо создать.
Немного погуглив я нашел решение:

Код
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$L$15"), , xlNo).Name = "МояТаблица1"

Всё прекрасно работает, таблица создается с диапазоном ячеек указанных в параметрах Range. Можно даже вместо ActiveSheet написать

Код
Workbooks("Mybook").Worksheets("MySheet")

И данный способ будет работать, но только в одном случае — если данный MySheet является активным в момент исполнения макроса. Если переключить активный лист на другой и попытаться запустить макрос, то будет ошибка:
Run-time error ‘1004’:

Данные листа таблица должны находиться на том же листе, что и таблица.

Есть предположение, что сначала перед созданием умной таблицы в коде vba можно написать, чтобы нужный нам лист где должна располагаться будущая таблица стал активным. Но как и это сделать я пока тоже не знаю.

Решения своего вопроса на просторах в интернете я не нашел, поэтому решил спросить здесь. Буду благодарен за ваши ответы.

Изменено: Максим Белич — 22.07.2022 10:30:23

Источник

Добавление удаление строк в умную таблицу по условию

anisimovaleksandr32

Дата: Среда, 06.04.2022, 16:01 |
Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 38

Добрый день всем!!!
Помогите реализовать задумку (сам смог лишь к этому прийти — не без помощи интеренета)

Имеется таблица (в некоторых столбцах имеются формулы) 5 строк в данной таблице никогда не удаляются
Задача:
в ячейке Е1 значение (условие) которое говорит о количестве строк в данной таблице (оно не будет ниже 5 — никогда)
Задача дополнить нужное количество строк чтоб реализовать условие

Заранее спасибо огромное всем кто откликнется

К сообщению приложен файл:

4171264.xlsm
(18.3 Kb)

Сообщение отредактировал anisimovaleksandr32 — Среда, 06.04.2022, 16:01

Ответить

_Boroda_

Дата: Среда, 06.04.2022, 16:17 |
Сообщение № 2

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

Положите файл xlsx (без макросов). У меня политика безопасности запрещает скачивать файлы с макросами

Скажи мне, кудесник, любимец ба’гов…
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

anisimovaleksandr32

Дата: Среда, 06.04.2022, 16:53 |
Сообщение № 3

Группа: Пользователи

Ранг: Новичок

Сообщений: 38

_Boroda_, добрый день!!!
Рад очень вас слышать

К сообщению приложен файл:

8937348.xlsx
(11.4 Kb)

Ответить

_Boroda_

Дата: Среда, 06.04.2022, 18:07 |
Сообщение № 4

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

[vba]

Код

Sub Макрос1()
    Dim tb As Object
    n_ = Cells(1, 5).Value
    If n_ <= 5 Then Exit Sub
    Set tb = ActiveSheet.ListObjects(«Таблица1»)
    nr_ = tb.ListRows.Count
    ndob_ = n_ — nr_
    If ndob_ < 1 Then
        MsgBox «В таблице уже есть » & n_ & » строк»
        Exit Sub
    End If
    Application.ScreenUpdating = 0
    Application.Calculation = 3
    tb.ShowTotals = True
    Cells(Range(tb).Row + nr_, 1).Resize(ndob_).EntireRow.Insert
    tb.ShowTotals = False
    Application.Calculation = 1
    Application.ScreenUpdating = 1
End Sub

[/vba]

Ответить

anisimovaleksandr32

Дата: Среда, 06.04.2022, 18:54 |
Сообщение № 5

Группа: Пользователи

Ранг: Новичок

Сообщений: 38

_Boroda_, спасибо огромное!!!
Я дико извеняюсь!!!
Не могли бы помочь разобраться (просто расписал комментариями):

Но не могли бы дополнить данный код не а удалением строк

Данная таблица ни когда не будет меньше 5 основных строк
— она может дополнятся
— она может уменьшаться /удаляться
до нужных количеств строк в зависимости от ячейки Е1(условие)

А так прям на УРА hands СПАСИБО ОГРОМНЕЙШЕЕ

Ответить

anisimovaleksandr32

Дата: Среда, 06.04.2022, 19:14 |
Сообщение № 6

Группа: Пользователи

Ранг: Новичок

Сообщений: 38

пытаюсь убрать MsgBox
И врезаться так вот :'(

как топором работаю (((( прекрасно осознаю

Сообщение отредактировал anisimovaleksandr32 — Среда, 06.04.2022, 19:19

Ответить

anisimovaleksandr32

Дата: Среда, 06.04.2022, 19:58 |
Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 38

По сути срабатывает но не корретно :'(

Сообщение отредактировал anisimovaleksandr32 — Среда, 06.04.2022, 19:58

Ответить

_Boroda_

Дата: Четверг, 07.04.2022, 11:41 |
Сообщение № 8

Группа: Модераторы

Ранг: Местный житель

Сообщений: 16618

Репутация:
6465
±

Замечаний:
0% ±

2003; 2007; 2010; 2013 RUS

[vba]

Код

Sub Макрос1()
    Dim tb As Object
    n_ = Cells(1, 5).Value
    If n_ < 5 Then Exit Sub
    Set tb = ActiveSheet.ListObjects(«Таблица1»)
    nr_ = tb.ListRows.Count
    ndob_ = n_ — nr_
    If ndob_ Then
        Application.ScreenUpdating = 0
        Application.Calculation = 3
        If ndob_ > 0 Then
            For i = 1 To Abs(ndob_)
                tb.ListRows.Add
            Next i
        Else
            For i = 1 To Abs(ndob_)
                tb.ListRows(6).Delete
            Next i
        End If
        Application.Calculation = 1
        Application.ScreenUpdating = 1
    End If
End Sub

[/vba]

Ответить

Источник

Medison1

5 / 2 / 1

Регистрация: 04.03.2012

Сообщений: 28

10.03.2018, 05:32. Показов 37707. Ответов 5

Метки нет (Все метки)

Добрый день.
На листе имеется умная таблица с заголовками.
При работе с ней макросами, я обращаюсь к ней так:

Visual Basic

WTName = "UT"
Set WS = ThisWorkbook.Worksheets("Лист1")
Set WT = WS.ListObjects(WTName)
WT.Range(i1, 3)

Последняя строка, мне не нравится, т.к. при добавлении столбца в начало таблицы. Формула «поедет». Т.к. нужно будет брать уже 4ый столбец и т.д.
Можно ли строку задавать переменной, а столбец задавать так как он назван в самой таблице? Чтобы не быть привязанным к порядку столбцов в таблице.

Что-то вроде:

Visual Basic

1	WT.Range(i1, WTName & "[Столбец3]")

1232 / 670 / 238

Регистрация: 22.12.2015

Сообщений: 2,089

10.03.2018, 08:09

Сообщение было отмечено Medison1 как решение

Решение

Сообщение от Medison1

WT.Range(i1, WTName & «[Столбец3]»)

Полагаю, range возвращает обычный диапазон, для которого доступно свойство cells. Не очень понял, что такое i3 — если это число, равное номеру строки, то код будет (например, что-бы выделить ячейку)

Код

WT.Range(WTName & "[Столбец3]").Cells(i1,1).Select

или

Код

WT.Range(WTName & "[Столбец3]").Rows.Item(i1).Select

ji2n

…

39 / 37 / 13

Регистрация: 08.10.2016

Сообщений: 171

10.03.2018, 12:31

Так формула не «поедет» :

Visual Basic

1
2
3

Dim R As Range
'...
Set R =WT.Range(i1, 3)

Иными словами R -становится указателем на на искомый диапазон.
Дальше просто работаем с ней как заблагорассудится

Medison1

5 / 2 / 1

Регистрация: 04.03.2012

Сообщений: 28

10.03.2018, 12:51

[ТС]

Dinoxromniy,
Хорошие варианты визуально, но по какой-то причине не работают.
Ошибка: #5 invalid procedure call or argument

Пробовал еще так:

Visual Basic

1	WT.Range(WTName & "[Столбец3]",i1)

Ошибка: #13 Type mismatch

Вот этот вариант оказался рабочим. Не нужно было писать перед этим «WT.»

Visual Basic

1	String1 = Range(WTName & "[Столбец3]").Cells(i1,1)

ji2n,
Спасибо. Но немного не то. В данном случае искомый диапазон зависит от константы «3». А мне необходимо уйти от этой константы к названию столбца умной таблицы. Т.к. столбцы могут изменять порядок и в какой-то момент нужный столбец будет к примеру не 3им, а 4ым или 5ым и т.д.

Казанский

15136 / 6410 / 1730

Регистрация: 24.09.2011

Сообщений: 9,999

10.03.2018, 13:31

Medison1, весь столбец (включая заголовок и ячейку из строки итогов)

Visual Basic

1	wt.listcolumns("заголовок_столбца").range

Только столбец с данными например так

Visual Basic

1	wt.ListColumns("заголовок_столбца").DataBodyRange

Хорошая шпаргалка по тому, как обращаться к разным частям умной таблицы: https://www.thespreadsheetguru… cel-tables

1232 / 670 / 238

Регистрация: 22.12.2015

Сообщений: 2,089

10.03.2018, 13:57

Сообщение от Medison1

по какой-то причине не работают.

Да, разумеется в обоих случаях должно быть WS вместо WT. Тут обращение происходит со стороны листа рабочей книги, а не через саму умную таблицу (последний случай смотрите у Казанский).

Источник

содержание видео

Урок 1. Работа с Умной таблицей на VBA
В данном уроке рассматриваем пример, как работать с умной таблицей кодом VBA. Учимся добавлять новые строки, столбцы. Работаем с выделением таблицы, форматированием и учимся включать дополнительные функции умной таблицы
Дата: 2022-01-11

Комментарии и отзывы: 10

Как задать имя умной таблицы не жестко. Дело в том, что лист будет копироваться. И умная таблица на скопированном листе будет иметь другое имя — автоматически добавляется числовой индекс в конце. Если сходная была Таблица, то на скопированном листе будет Таблица1 и т. д. Макрос должен удалять и добавлять строки как в вашем видео как на исходном листе так и на скопированных. Проблема — обращаться при добавлении к умн. таблице на активном листе.

Павел
Не понятно как переименовать умную таблицу!
На листе Платежи создал умную таблицу. Она сразу получила имя Таблица1
При попытке задать ей имя появился псевдоним ТабПлатежи, но Таблица1 тоже осталась.
В диспетчере имён видно. что
имени ТабПлатежи соответствует ДИАПАЗОН Таблица1
Пробую код:
Set ShPayment = ThisWorkbook. Worksheets(Платежи) — работает
Set ListObjPay = ShPayment. ListObjects(Таблица1) — работает
Set ListObjPay = ShPayment. ListObjects(ТабПлатежи) — НЕ РАБОТАЕТ!

Юлия
Спасибо за информативное и понятное видео!
Подскажите, а можно ли обратиться к столбцу не по его номеру, а по названию? Если да, то как это сделать?
Буду Вам очень благодарна, если поможете со следующей задачей: как прописать формулу в умной таблице не по индексам ячейки (А2В2, а по названиям столбцов?

Алексей
а не проще удалить вручную все и ничего не писать. До сих пор не могу понять зачем ВБА. Что в нем такого суперового? По мне так сложно все, писать на английском кучу строк что бы удалить строку. Обьясните мне тупому, зачем ВБА. Сколько не искал ответа нету(

Иван
Отличный урок.
Только 22: 58 маленькая неточность. Думаю строка должна иметь вид:
For i=1 to ListObj. ListColumns. Count
а то как-то не комильфо работать со столбцами по количеству строк)
и переменную і забыли объявить, если установлен Option Explicit VBA будет тоже ругаться.

ivan
Спасибо за Вашу работу, очень полезно, подскажите как сделать чтобы номер столбца который нужно добавить ListObj. ListColumns. Add 7 вставлялся автоматически например у меня лист товар, ячейка А15 = значение 6
ListObj. ListColumns. Add 7 (вместо 7 стало 6. спасибо

Алексей
Спасибо за урок. Очень жду продолжения.
Замечена одна неточность: при форматировании всех колонок считаешь количество строк, а форматируешь колонки. Насколько понимаю, правильным было бы записать ListObj. ListColumns. Count

сергей
В видео допущен ошибка на 22: 55 при установке ширины колонок. Количество столбцов нужно определять ListObj. ListColumns. Count, а не через свойство ListRows

Elnara
Здравствуйте! Почему у меня не получается? Выходит маленькое окошко и там написано: run-time error ‘9’: Subscript out of range. Что это означает не поняла?

Николай
Большое спасибо за урок! Очень ценно. А можно ли удалить сразу несколько смежных строк, указав диапазон? Или только по одной?

Источник

Обращение к умной таблице

Обращение к строкам

Обращение к столбцам

Обращение к ячейкам

Вставка и удаление строк

Вставка и удаление столбцов

Решение

А вот еще интересные статьи: