Пополняемый список полезных отрывков кода VBA для выполнения часто востребованных действий в MS Excel.
Служебные команды для ускорения скорости выполнения макроса:
‘Отключение отображения выполняемых действий Application.ScreenUpdating = False ‘Предотвращение появления предупреждающих сообщений Application.DisplayAlerts = False ‘Предотвращение появления предупреждения об обновлении связей данных Application.AskToUpdateLinks = False ‘Очистка буфера обмена Application.CutCopyMode = False
Проверка имени пользователя, запустившего макрос:
Чтобы проверить, какой пользователь открыл книгу Excel можно использовать один из следующих вариантов:
If Application.UserName = «Имя_автора_документа» Then .
If Environ(«username») = «user» Then .
Поиск последней строки таблицы:
Set myWSheet = ThisWorkbook.Sheets(«Имя_листа») With myWSheet ‘Определение индекса последней строки таблицы lastRow = .Cells(Rows.Count, 1).End(xlUp).Row ‘Определение значения в ячейки последней строке столбца A lastARow = .Range(«A» & lastRow).Value End With
Замена формулы на значение:
Добавление нового листа с именем после всех существующих:
Предотвращение ошибки при неудачном поиске значения в таблице:
Set DateRowObj = WB.Sheets(«Имя_листа»).Range(«A:A»)._ Find(What:=dtToAsDate, LookIn:=xlFormulas) If (DateRowObj Is Nothing) Then WB.Close False MsgBox «Данные не найдены.» Else DateRow = DateRowObj.Row ‘Номер строки с искомым значением End If
Как получить имя активной книги Excel без его расширения (без .xls либо без .xlsx):
Col = Selection.Column ‘Номер выбранного столбца ColMax = ActiveSheet.PivotTables(«СводнаяТаблица»).PivotColumnAxis. _ PivotLines.Count If Col — 1
Поиск файлов в папке
Dim strDirPath, strMaskSearch, strFileName as String strDirPath = «C:/test/» ‘Папка поиска strMaskSearch = «*.xls*» ‘Маска поиска ‘Получаем первый файл соответствующий шаблону strFileName = Dir(strDirPath & strMaskSearch) Do While strFileName <> «» ‘До тех пор пока файлы «не закончатся» MsgBox strFileName strFileName = Dir ‘Следующий файл Loop
как узнать последнюю строку, заполненную в VBA (Excel)?
У меня есть лист вызовов «резюмировать», и я хочу знать, сколько линии, что у меня есть в этом sheet.I пытался с этим кодом:
в моем макросе я попытался это:
Вы передаете строку в вашу FindingLastRow функцию , но не являются его использованием. шиитское это параметр передается в системе, но позже вы попытаетесь использовать то , что называется Mysheet .
Если имя рабочего листа не передается в то используется имя текущего ActiveSheet в. Если нет алфавитного имени столбца не передаются в нем будет использовать столбец А.
Вы также при условии, что последняя ячейка будет в колонке А, которая не может быть так, поэтому я, как правило, вернуться к чему-то вроде этого:
Если вы хотите , чтобы найти последнюю ячейку в книге, кроме ThisWorkbook :
Используется, как это:
Или, чтобы найти последнюю строку столбца А в другой открытой книге .
Или , чтобы найти последнюю строку столбца B в другой открытой книге .
UsedRange На переменную рабочего листа является очень полезным здесь. Вы действительно не нужно UDF , чтобы получить количество строк.
Последняя заполненная ячейка в MS EXCEL
Найдем номер строки последней заполненной ячейки в столбце и списке. По номеру строки найдем и само значение.
Рассмотрим диапазон значений, в который регулярно заносятся новые данные.
Диапазон без пропусков и начиная с первой строки
В случае, если в столбце значения вводятся, начиная с первой строки и без пропусков, то определить номер строки последней заполненной ячейки можно формулой: =СЧЁТЗ(A:A))
Формула работает для числовых и текстовых диапазонов (см. Файл примера )
Значение из последней заполненной ячейки в столбце выведем с помощью функции ИНДЕКС() : =ИНДЕКС(A:A;СЧЁТЗ(A:A))
Ссылки на целые столбцы и строки достаточно ресурсоемки и могут замедлить пересчет листа. Если есть уверенность, что при вводе значений пользователь не выйдет за границы определенного диапазона, то лучше указать ссылку на диапазон, а не на столбец. В этом случае формула будет выглядеть так: =ИНДЕКС(A1:A20;СЧЁТЗ(A1:A20))
Диапазон без пропусков в любом месте листа
Если список, в который вводятся значения расположен в диапазоне E8:E30 (т.е. не начинается с первой строки), то формулу для определения номера строки последней заполненной ячейки можно записать следующим образом: =СЧЁТЗ(E9:E30)+СТРОКА(E8)
Формула СТРОКА(E8) возвращает номер строки заголовка списка. Значение из последней заполненной ячейки списка выведем с помощью функции ИНДЕКС() : =ИНДЕКС(E9:E30;СЧЁТЗ(E9:E30))
Диапазон с пропусками (числа)
В случае наличия пропусков (пустых строк) в столбце, функция СЧЕТЗ() будет возвращать неправильный (уменьшенный) номер строки: оно и понятно, ведь эта функция подсчитывает только значения и не учитывает пустые ячейки.
Если диапазон заполняется числовыми значениями, то для определения номера строки последней заполненной ячейки можно использовать формулу =ПОИСКПОЗ(1E+306;A:A;1) . Пустые ячейки и текстовые значения игнорируются.
Так как в качестве просматриваемого массива указан целый столбец (A:A), то функция ПОИСКПОЗ() вернет номер последней заполненной строки. Функция ПОИСКПОЗ() (с третьим параметром =1) находит позицию наибольшего значения, которое меньше или равно значению первого аргумента (1E+306). Правда, для этого требуется, чтобы массив был отсортирован по возрастанию. Если он не отсортирован, то эта функция возвращает позицию последней заполненной строки столбца, т.е. то, что нам нужно.
Чтобы вернуть значение в последней заполненной ячейке списка, расположенного в диапазоне A2:A20, можно использовать формулу: =ИНДЕКС(A2:A20;ПОИСКПОЗ(1E+306;A2:A20;1))
Диапазон с пропусками (текст)
В случае необходимости определения номера строки последнего текстового значения (также при наличии пропусков), формулу нужно переделать: =ПОИСКПОЗ(«*»;$A:$A;-1)
Пустые ячейки, числа и текстовое значение Пустой текст («») игнорируются.
Диапазон с пропусками (текст и числа)
Если столбец содержит и текстовые и числовые значения, то для определения номера строки последней заполненной ячейки можно предложить универсальное решение: =МАКС(ЕСЛИОШИБКА(ПОИСКПОЗ(«*»;$A:$A;-1);0); ЕСЛИОШИБКА(ПОИСКПОЗ(1E+306;$A:$A;1);0))
Функция ЕСЛИОШИБКА() нужна для подавления ошибки возникающей, если столбец A содержит только текстовые или только числовые значения.
Другим универсальным решением является формула массива: =МАКС(СТРОКА(A1:A20)*(A1:A20<>«»))
После ввода формулы массива нужно нажать CTRL + SHIFT + ENTER. Предполагается, что значения вводятся в диапазон A1:A20. Лучше задать фиксированный диапазон для поиска, т.к. использование в формулах массива ссылок на целые строки или столбцы является достаточно ресурсоемкой задачей.
Значение из последней заполненной ячейки, в этом случае, выведем с помощью функции ДВССЫЛ() : =ДВССЫЛ(«A»&МАКС(СТРОКА(A1:A20)*(A1:A20<>«»)))
Как обычно, после ввода формулы массива нужно нажать CTRL + SHIFT + ENTER вместо ENTER.
СОВЕТ: Как видно, наличие пропусков в диапазоне существенно усложняет подсчет. Поэтому имеет смысл при заполнении и проектировании таблиц придерживаться правил приведенных в статье Советы по построению таблиц.
Excel vba как определить последнюю запись в таблице excel?
Информация о сайте
Инструменты и настройки
Excel Windows и Excel Macintosh
Вопросы и решения
Работа и общение
Работа форума и сайта
Функции листа Excel
= Мир MS Excel/Статьи об Excel
Приёмы работы с формулами [13]
Инструменты Excel [4]
Форматирование [1]
Примечания [1]
Гиперссылки [1]
Excel для Windows и Excel для Mac OS [2]
Если Вам необходимо в таблицах, которые имеют неодинаковое количество ячеек в строках и/или столбцах, например таких:
находить последние заполненные ячейки и извлекать из них значения, то в Excel Вы, к сожалению, не найдёте функции типа ВЕРНУТЬ.ПОСЛЕДНЮЮ.ЯЧЕЙКУ()
Вот как это сделать имеющейся в стандартном наборе функций функцией ПРОСМОТР().
1. Для текстовых значений:
В английской версии:
Как это работает: Функция ПРОСМОТР() ищет сверху вниз в указанном столбце текст «яяя» и не найдя его, останавливается на последней ячейке в которой есть хоть какой-то текст. Так как мы не указали третий аргумент этой функции «Вектор_результатов», то функция возвращает значение из второго аргумента «Вектор_просмотра».
Пояснение: Почему именно «яяя«? Во-первых, потому что функция сравнивает при поиске текст посимвольно, а символ «я» в русском языке последний и все предыдущие при сравнении отбрасываются, во-вторых, потому что в русском языке нет такого слова.
Примечание: Вообще-то достаточно использовать и «яя«, но тогда возникает мизерная возможность попасть на таблицу, в которой будет такое слово. Так называются город и река в Кемеровской области. В детстве я был в этом городе и даже купался в этой реке
2. Для числовых значений:
В английской версии:
Как это работает: Функция ПРОСМОТР() ищет слева направо в указанной строке число «9E+307» и не найдя его, останавливается на последней ячейке в которой есть хоть какое-то число. Так как мы не указали третий аргумент этой функции «Вектор_результатов», то функция возвращает значение из второго аргумента «Вектор_просмотра».
Пояснение: Почему именно «9E+307«? Потому что это максимально возможное число в Excel. Поэтому функция найти его может только в каком-то невероятном случае, в реальной жизни пользователь такими числами просто не оперирует.
3. Для смешанных (текстово-числовых) значений:
В английской версии:
Как это работает: Функция ПРОСМОТР() ищет слева направо в указанной строке число «1» и найдя его, останавливается на последней ячейке в которой есть это число. Так как мы указали третий аргумент этой функции «Вектор_результатов», то функция возвращает значение из него, соответствующее позиции последнего вхождения искомого в просматриваемый массив.
Пояснение: Почему именно «1«? Да просто так С таким же успехом можно использовать число 2 или 3 или 100500, например. Главное что бы первый аргумент функции был не менее делимого в выражении 1/Диапазон. Вот пример применения другого числа в первом аргументе, при делимом отличном от единицы:
Как получить последнее значение в столбце таблицы Excel
За определенный период времени ведется регистр количества проданного товара в магазине. Необходимо регулярно отслеживать последний выданный из магазина товар. Для этого нужно отобразить последнюю запись в столбце наименования товаров. Чтобы просто посмотреть на последнее значение столбца, достаточно переместить курсор на любую его ячейку и нажать комбинацию горячих клавиш CTRL + стрелка в низ (↓). Но чаще всего пользователю приходится с последним значением столбца выполнять различные вычислительные операции в Excel. Поэтому лучше его получить в качестве значения для отдельной ячейки.
Поиск последнего значения в столбце Excel
Схематический регистр товаров, выданных с магазина:
Чтобы иметь возможность постоянно наблюдать, какой товар зарегистрирован последним, в отдельную ячейку E1 введем формулу:
Результат выполнения формулы для получения последнего значения:
Разбор принципа действия формулы для поиска последнего значения в столбце:
Главную роль берет на себя функция =ИНДЕКС(), которая должна возвращать содержимое ячейки таблицы где пересекаются определенная строка и столбец. В качестве первого аргумента функции ИНДЕКС выступает неизменяемая константа, а именно ссылка на целый столбец (B:B). Во втором аргументе находится номер строки с последним заполненным значением столбца B. Чтобы узнать этот номер строки используется функция СЧЁТЗ, которая возвращает количество непустых ячеек в диапазоне. Соответственно это же число равно номеру последней непустой строки в столбце B и используется как второй аргумент для функции ИНДЕКС, которая сразу возвращает последнее значение столбца B в отдельной ячейке E1.
Внимание! Все записи в столбце B должны быть неразрывны (без пустых ячеек до последнего значения).
Стоит отметить что данная формула является динамической. При добавлении новых записей в столбец B результат в ячейке E1 будет автоматически обновляться.
Как найти последнюю пустую ячейку MS Excel VBA встроенным методом?
Хочу скрыть ячейки где формула вывела пустую строку. Пробовал так(и парой других методов Excel)
Возвращают 500 так как там есть формулы которые и вывели мне пустую строку
Знаю что можно через For+If перебрать(как и сделаю если не будет альтернатив):
Ищу какой нибудь встроенный метод который работает с рассчитанными значениями при поиске пустых ячеек.
Вопрос задан 02 окт.
67 просмотров
Например вот так можно: Range(«A1:A10000»).AutoFilter 1, «<>«, , , False
Выбирает неверно — там где ячейки пустые(конец диапазона), а надо там где значение по формуле = «»
К сожалению xlLastCell не совсем то — оно ищет пустые ячейки, но в моих ячейках есть формула, которая вывела пустое значение, которое нужно скрыть.
Несколько советов по работе с VBA в Excel
Некоторое время назад меня попросили «помочь с Экселем», а потом и работа подвернулась такая, так что за последние пару месяцев я узнал много полезного, чем и хочу поделиться в догонку к недавней статье.
Предполагается, что вы знаете основы Visual Basic. Я не буду рассказывать, как создавать формы или модули, здесь только примеры кода.
Visual Basic
Опции
Во-первых, в VB массивы могут начинаться с индекса 1, что для многих странно, поэтому в начале модулей можно прописывать:
Так же рекомендуется прописать:
В этом случае интерпретатор потребует заблаговременного объявления всех переменных. Переменные объявлять нужно потому, что: — VB запомнит их нАпиСание и не будет исправлять во всём коде на последний введенный вариант; — иногда возникают ошибки с передачей переменных byRef, если они не объявлены (то есть надо или объявить переменную, или приписать в функции/процедуре перед ней byVal).
Ещё одним важным оператором является ON ERROR. Привожу варианты:
Возможности языка
Хотя VB довольно прост, полезно почитать документацию по его синтаксису. Я, например, с удивлением узнал, что можно прописывать сложные усолвия в SELECT’ах (аналог switch):
Ускорение работы макросов
Часто макросы требуют долгого времени выполнения, которое можно значительно сократить. В начале и в конце каждой ресурсоёмкой функции вызвать Prepare и Ended.
По порядку: 1. Отключить перерисовку объектов на экране, чтобы ничего не мигало. 2. Выключить расчет. Внимание, если макрос прерваляс посреди работы, то расчет так и останется в ручном режиме! 3. Не обрабатывать события. 4. Отображение границ страниц, тоже почему-то помогает. 5. В статусной строке выводятся различные данные, что замедляет работу, отключаем. 6. Это если нужно. Выключает сообщения Экселя. Например, мы делаем Workbook.Close, Эксель хочет спросить сохранить ли изменения. При выключении этого параметра все ответы будут даны автоматически (изменения не сохранятся).
Важно понимать, что VBA выполняет все действия так же, как и пользователь. Поэтому для того, чтобы установить параметры страницы, он каждый раз открывает и закрывает окно параметров. У меня выставлялись параметры для 10 листов, это реально не быстро. Поэтому делаем так:
Далее, часто нужно просмотреть различные диапазоны ячеек и что-то с ними сделать. Тут важно не использовать циклы for с перебором индексов, они медленные. Можно использовать встроенные функции Экселя, но удобнее всего такой вариант: Данный код просматривает указанный диапазон, выбирает в нем «специальные ячейки», в данном случае все, в которых есть формулы (т.е. начинаются со знака равно). Для каждой ячейки смотрится, если она не закрашена, то её надо защитить (см. далее) и покрасить. Такой код работает очень быстро.
Для любых переменных, которым вы собираетесь присвоить книгу, лист, диапазон (ячейку) нужно предварительно объявить как Variant.
Естественно, что если вам нужны однотипные значения в ячейках, нужно использовать автозаполнение, всё равно как «растягивание» ячеек пользователем.
Второй диапазон должен включать первый, а второй необязательный параметр указывает тип автозаполнения.
Загрузка книги и события
При открытии книги каждый раз срабатывает процедура. В данном случае настройки печати (поля, ориентация) сбрасываются на дефолтные. Можно и другую инициализацию выполнять. Важно, что если макросы отключены, то и не выполнится ничего. Если в Экселе вылезла вверху панелька с предупреждением о макросах и пользователь нажал «Включить», то именно в этот момент выполнится процедура Workbook_open().
Список доступных событий можно посмотреть вверху редактора VB. Например, я делал на событие Change проверку, где лежит ячейка, в которой было изменения, и если это нужный диапазон, то делалась запись в лог со старым и новым значением.
Защита
Во-первых сразу отмечу, что MS Office не исполняет макросы на компьютерах, где он не нашел антивируса, если книга зашифрована. Сталкивался на компьютерах, где антивирус был, но видимо Windows XP об этом не знала.
Ещё антивирус может странным образом мешать работе, вызывать ошибки, не совсем объяснимые. Показал айтишникам, сказали ок, что-то сделали, не знаю.
Итак, нам надо защитить книгу, чтобы ввод был разрешен только в нужные ячейки (формулы и заголовки поменять нельзя). Во-первых, нужно сделать соответствующие ячейки «не защищенными». Для этого делаем одно из: — выделяем диапазон, формат ячеек, снять галочку «Блокировать ячейку»; — выводим кнопку «Блокировать ячейку» в быстрый доступ и нажимаем её, очень удобно смотреть на неё чтобы понять, защищена ячейка или же нет; — а это пригодится, чтобы проверить третий вариант — написать макрос, который снимает защиту с нужных ячеек сам.
Далее нужно защитить лист. На вкладке Рецензирование есть такая кнопка. Окошко просит ввести пароль и установить исключения (что можно будет делать пользователю). К сожалению, список исключений маловат. Самое обидное, что нельзя разрешить сворачивать/разворачивать группы столбцов/строк. Поэтому действуем так, на загрузку книги прописываем: Знак подчеркивания продолжает логическую строку на следующей физической строке. Итак, здесь мы: 1. Сняли защиту. 2. Включили группировку. 3. Поставили защиту, при этом: — защита только от юзера, макросы продолжают иметь полный доступ (!), крайне важно; — разрешили сортировку, фильтрацию и форматирование строк/столбцов (высота/ширина); — DrawingObject в данном случае снимает защиту с примечаний к ячейкам, может и ещё с чего.
Тут мы сталкиваемся с парой сюрпризов. Во-первых, не все макросы будут работать даже так. Известный баг, ничего не сделаешь. Нельзя вставить строку, например. Приходится снимать и тут же ставить защиту. Если «злоумышленник» в этот момент нажмет ctrl+break, то защита слетит.
Во-вторых, скажем никаким способом нельзя удалять строки (AllowDeletingRows), в которых есть защищенные ячейки, хоть одна. Подробнее вот тут.
Решением (костылем) является добавление кнопки или сочетания клавиш для удаления. Заодно можно проверить, чтобы пользователь не удалил чего не надо. В Workbook_open добавляем:
Теперь процедура будет вызываться при нажатии shift+delete. Знаю, код некрасивый, простите. Здесь я пытался проверить, что выделена строка, то есть строк там 1, а ячеек не меньше тысячи. Чтобы удалить не то, придется выделить тысячу ячеек начиная не с первого столбца. Далее проверяется имя листа и номера строк. Вместо 50 был расчет последенй строки (ведь их число меняется, если мы их удаляем и добавляем).
Заключение
VBA — весьма глючная вещь, которая позволяет сворачивать горы в MS Office. Многие предприятяи используют модели на Excel годами, и если они сделаны хорошо, то всё работает.
Для изучения VBA подходит он сам, во-первых там хорошая справка. Например, чтобы узнать все варианты что можно разрешить в методе Protect, нажимаем F1, Protect, ввод. И вуаля.
Во-вторых, можно проделать требуемые действия вручную, записав макрос, а потом просмотрев его код. Код будет ужасен (например, при изменении параметров страницы, макрос запишет значения всех параметров и полей, а не только измененного вами), но ответы найдутся. Хотя, например, .AutoFit, который записывается при изменении высоты ячейки по содержимому (двойной клик на границе слева), на самом деле не работает.
Предлагаю знатокам поделиться своим опытом, дать советы в комментариях. Спасибо за внимание, удачных разработок вам.
Excel vba как определить последнюю запись в таблице excel?
Вариант II. Для поиска последней заполненной ячейки можно воспользоваться свойством UsedRange об’екта Worksheet
Пример определения номера строки и столбца последней заполненной ячейки.
Комментарий : Так как свойство UsedRange принадлежит об’екту Worksheet, то использование ссылки на этот об’ект обязательно.
Примечание : Все вышеприведённые примеры определяют последнюю ячейку в активном рабочем листе. Естественно, что мы можем ссылаться и на другие рабочие листы , используя при этом их имя, номер (индекс) или имя в среде VBA ()
Вариант III. Для определения количества заполненных ячеек в смежном диапазоне можно воспользоваться свойством CurrentRegion об’екта Range
Пример определения количества строк и столбцов в смежном с ячейкой диапазоне, а также адрес этого диапазона.
Примечание : Особенностью свойства CurrentRegion является то, что он возвращает весь диапазон, но только состоящий из смежных ячеек.
Комментарий : важно При определении последней заполненной строки, этот вариант будет корректно работать только при условии, что данные в строке и столбце начинаются с самой первой ячейки и не содержат пустых строк и столбцов.
Предупреждение : Если рабочий лист защищён, то использование этого варианта приведёт к возникновению ошибки, которую можно избежать, если воспользоваться этим советом.
Вариант IV. Для определения номера строки и столбца последней заполненной ячейки можно использовать функцию ПОЛУЧИТЬ.ДОКУМЕНТ (макроязык Excel4.0)
Пример определения номера строки и столбца последней заполненной ячейки в активном рабочем листе.
Вариант V. Для определения номера последней заполненной ячейки в конкретной строке или столбце, а также для определения последней заполненной ячейки можно использовать метод Find
Пример определения номера последней заполненной ячейки в конкретной строке и столбце.
Примечание : Вышеупомянутый синтаксис может вызвать ошибку, если указанный диапазон не содержит данных. Для того, чтобы этого избежать, во всех примерах использован «режим отложенной ошибки» On Error Resume Next Однако, можно использовать и другой синтаксис, например :
Set iLastCell = Cells.Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
If Not iLastCell Is Nothing Then iRow = iLastCell.Row iClm = iLastCell.Column End If
Set iLastCell = Cells.Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
If TypeName(iLastCell) <> «Nothing» Then iRow = iLastCell.Row iClm = iLastCell.Column End If
Комментарий : важно Этот вариант будет корректно работать только при условии, что ячейки не содержат формул, которые возвращают пустую строку «» или апостроф ‘
Вариант VI. Для определения номера последней заполненной ячейки в конкретной строке или столбце можно воспользоваться свойством End об’екта Range и специальными константами xlToRight , xlDown
Пример определения номера последней заполненной ячейки в конкретной строке и столбце.
Комментарий : важно Этот вариант будет корректно работать только при условии, что данные в строке, или столбце начинаются с самой первой ячейки и не содержат пустых ячеек.
Вариант VII. Тот же самый способ, что и предыдущий, но с небольшими изменениями.
Пример определения номера последней заполненной ячейки в конкретной строке и столбце.
Комментарий : важно Этот вариант будет работать при любых условиях, так как маловероятно, что последней заполненной ячейкой окажется именно последняя ячейка в столбце, однако и эту вероятность можно учесть :
Const iMaxRow = 65536 ‘ 97, 2000
With Worksheets(1).Cells(iMaxRow, 1) If IsEmpty(.Value) = True Then iRow = .End(xlUp).Row Else iRow = iMaxRow End If End With
Вариант VIII. Некоторые люди используют для определения последней заполненной строки в определённом столбце стандартную функцию рабочего листа СЧЁТЗ
Пример определения номера последней заполненной ячейки в конкретном столбце.
Комментарий : важно Так как функция СЧЁТЗ считает количество непустых ячеек, то этот вариант будет корректно работать только при условии, что данные в столбце начинаются с самой первой ячейки и не содержат пустых ячеек.
Важно : Если быть абсолютно точным, то в первых пяти случаях мы определяем последнюю ячейку, которая вовсе не обязательно должна быть заполнена, так как эта ячейка расположена на пересечении последней строки и последнего столбца.
Причём, в случае применения первого, второго или четвёртого варианта, необходимо помнить, что последними могут считаться ячейки, параметры форматирования которых, отличаются от изначально установленных.
Дополнение : Для того чтобы Ваш макрос стал более понятным, лучше использовать имена переменных, несущих смысловую нагрузку и поэтому именовать номер строки последней ячейки не iRow , а iRowLast , а номер столбца не iClm , а iClmLast .
Тогда цикл по строкам будет выглядеть примерно так :
Excel vba как определить последнюю запись в таблице excel?
По материалам эхоконференции RU.EXCEL (за июль-сентябрь 1997 года) Collected by Kirienko Andrew, 2:5020/239.21@fidonet
Cодержание
Как определить последнюю запись в таблице Excel?
Q: Необходимо найти последнюю запись вэлектронной таблице. Какой функцией VB это можно было бы организовать.
A: Первое что вспомнилось: Application.SpecialCells(xlLastCell)
Как отменить выделение диапазона ячеек?
Q: Как управиться с такой болячкой:
После прекращения работы макроса диапазон остается выделенным. Как это выделение убрать?
A: Попробуй вот как: Selection.Cells(1).Select Фокус ввода попадёт после этого на первую ячейку ранее выделенного диапазона.
Как из макроса Excel программно создать таблицу Access?
Q: Подскажите, пожалуйста, как из под Excel программно создать таблицу Access
A: Вот фрагмент кода, который создаёт таблицу «BalanceShifr» базе данных MS Access:
Нint: Не забудьте выставить в Excel ссылки на объекты DAO! [VBA] Tools/References/Available References/ [x] MicroSoft DAO. Library ‘ Function CreateTable ‘ Create temporary table «BalanceShifr» into temporary database
Public Function CreateTable(ByVal dbTemp As Database) As Boolean
Dim tdfTemр As TableDef Dim idx As Index Dim fld As Field
On Error GoTo errhandle
CreateTable = True ‘ CREATE TABLE «BalanceShifr» Set tdfTemp = dbTemp.CreateTableDef(«BalanceShifr») Set fld = tdfTemp.CreateField(«ConditionId», dbLong) fld.Required = True tdfTemp.Fields.Append fld Set fld = tdfTemp.CreateField(«Account», dbText, 4) tdfTemp.Fields.Append fld Set fld = tdfTemp.CreateField(«SubAcc», dbText, 4) tdfTemp.Fields.Append fld Set fld = tdfTemp.CreateField(«Shifr», dbLong) tdfTemp.Fields.Append fld Set fld = tdfTemp.CreateField(«Date», dbDate) fld.Required = True tdfTemp.Fields.Append fld Set fld = tdfTemp.CreateField(«SaldoDeb», dbCurrency) tdfTemp.Fields.Append fld Set fld = tdfTemp.CreateField(«SaldoKr», dbCurrency) tdfTemp.Fields.Append fld dbTemp.TableDefs.Append tdfTemp
‘ CREATE INDEX «BalanceShifr» Set tdfTemp = dbTemp.TableDefs(«BalanceShifr») Set ) Set fld = idx.CreateField(«ConditionId») idx.Fields.Append fld tdfTemp.Indexes.Append idx Exit Function
errHandle: MsgBox «Table creating error!», vbExclamation, «Error» CreateTable = False End Function
Удаление листов в зависимости от даты
Q: Как удалить рабочие листы листов в зависимости от даты?
A: Вот код функции на Excel VBA, который решает данную проблему:
‘ Function DelSheetByDate ‘ Удаляет рабочий лист sSheetName в активной рабочей книге, ‘ если дата dDelDate уже наступила ‘ В случае успеха возвращает True, иначе — False
Public Function DelSheetByDate(sSheetName As String, _ dDelDate As Date) As Boolean On Error GoTo errHandle
DelSheetByDate = False ‘ Проверка даты If dDelDate
Подавление «горячих» клавиш.
Q:Как подавить доступ по «горячим» клавишам, имеется ввиду предопределенные в Excel клавиши типа Ctrl-O и т.д.?
A:Вот малюсенький исходник на Excel VB, который решает такую проблему.
Public Sub Auto_Open() ‘ Overrride standard accelerators With Application .OnKey «^o», «Dummy» .OnKey «^s», «NewAction» .OnKey «^р», «» ‘ Kill hotkey ! End With End Sub
‘ —— Public Sub Dummy() MsgBox «This hotkey redefined!» End Sub
‘ —— Public Sub NewAction() SendKeys «^n» ‘ Press + for create new file ‘ instead of + ! End Sub
Hint: Отлажено в MS Excel ’97 !
Подсказки к Toolbar
Q: Как сделать к «само нарисованным» кнопочкам на Toolbar’е подсказки? (Ну, те, что после 2-х секунд молчания мышки появляются)
A: Сделать можно вот как: (Пример реализации на Excel’97 VBA )
‘ Cоздаем тулбар Рublic Sub InitToolBar() Dim cmdbarSM As CommandBar Dim ctlNewBtn As CommandBarButton
Set cmdbarSM = CommandBars.Add(Name:=»MyToolBar», Position:=msoBarFloating, _ temporary:=True) With cmdbarSM ‘ 1) Добавляем кнопку Set ctlNewBtn = .Controls.Add(Type:=msoControlButton) With ctlNewBtn . Face > .OnAction = «OnButton1_Click» .TooltipText = «My tooltip message!» End With ‘ 2) Добавляем ещё кнопку Set ctlNewBtn = .Controls.Add(Type:=msoControlButton) With ctlNewBtn .Face > .OnAction = «OnButton2_Click» .TooltipText = «Another tooltip message!» End With .Visible = True End With End Sub
Hint: На VBA для Excel’95 это делается несколько иначе!
Как определить адрес активной ячейки
Q: Как в макросе узнать и использовать текущее положение курсора (не мышиного, естественно)?
A: Очень просто! ActiveCell.Row и ActiveCell.Column — покажут координаты активной ячейки.
Подсчет комментариев на рабочем листе
Q: Как узнать есть ли хоть один Notes (комментарий) в рабочем листе, кроме как перебором по всем ячейкам? . Без этого не работает:
A: В Excel’97 эта проблема может быть решена вот как:
‘ Function IsCommentsPresent ‘ Возвращает TRUE, если на активном рабочем листе имеется хотя бы ‘ одна ячейка с комментарием, иначе возвращает FALSE ‘ Public Function IsCommentsPresent() As Boolean IsCommentsPresent = ( ActiveSheet.Comments.Count <> 0 ) End Function
Подсказки к Toolbar (Excel’95)
Q: Как сделать свой собственный Toolbar с tooltip’ами на кнопках в Excel’95?
A: Вот фрагмент кода для Excel’95, который создаёт toolbar с одной кнопкой с пользовательским tooltiр’ом. Нажатие кнопки приводит к выполнению макроса NothingToDo() .
‘ ‘ This example creates a new toolbar, adds the Camera button ‘ (button index number 228) to it, and then displays the new toolbar. ‘ Public Sub CreateMyToolBar() Dim myNewToolbar As Toolbar On Error GoTo errHandle:
Set myNewToolbar = Toolbars.Add(Name:=»My New Toolbar») With myNewToolbar .ToolbarButtons.Add Button:=228, StatusBar:=»Statusbar help string» .Visible = True With .ToolbarButtons(1) .OnAction = «NothingToDo» .Name = «My custom tooltiр text!» End With End With Exit Sub errНandle: MsgBox «Error number » & Err & «: » & Error(Err) End Sub
‘ ‘ Toolbar button on action code ‘ Рublic Sub NothingToDo() MsgBox «Nothing to do!», vbInformation, «Macro running» End Sub
Нint: В Excel’97 этот код тоже работает!
Запуск Excel с поиском ячейки
Q: Как запустить Excel, чтобы оказаться на ячейке содержимое которой известно заранее?
A:Вот как я решил бы твою задачу:
‘ Sub GotoFixedCell: ‘ Делает активной ячейку, содержащую значение vVariant на ‘ рабочем листе sSheetName в активной рабочей книге. ‘ ‘ Note: Содержимое ячеек интерпретируется как ‘значение’! ‘ Public Sub GotoFixedCell(vValue As Variant, sSheetName As String) Dim c As Range, cStart As Range, cForFind As Range Dim i As Integer
On Error GoTo errhandle:
Set cForFind = Worksheets(sSheetName).Cells ‘ Диапазон поиска With cForFind Set c = .Find(What:=vValue, After:=ActiveCell, LookIn:=xlValues, _ LookAt:= xlРart, SearchOrder:=xlByRows,_ SearchDirection:=xlNext, MatchCase:=False) Set cStart = c While Not c Is Nothing Set c = .FindNext(c) If c.Address = cStart.Address Then c.Select Exit Sub End If Wend End With Exit Sub errНandle: MsgBox Err.Descriрtion, vbExclamation, «Error #» & Err.Number End Sub
Нint: Достаточно выполнить этот код из макроса Auto_Oрen()!
Нint: Протестировано и отлажено в Excel’97.
ThisWorkBook или ActiveWorkBook?
Q: На листе модулей открытой рабочей книги присутствует процедура, которая копирует некий лист из другой (не активной) рабочей книги. В этом листе в некоторых ячейках находятся определенные пользователем формулы. Процедура работает без проблем. Из workbook, содержащей эту процедуру, я делаю надстройку (.xla) и подключаю ее к Excel 95. При вызове вышеописанной процедуры она выдает сообщение: Run time error 424 object required Kак можно избежать это сообщение?
A:Вот что я тебе посоветую: Посмотри ещё разок код модулей рабочей книги и исправь все ссылки вида ActiveWorkbook.WorkSheets(«.. на ссылки вида ThisWorkBook.WorkSheets(«..
Дело в том, что когда выполняется код надстройки активной книгой в Excel’е является _не_ сама надстройка! Конструкция ThisWorkbook позволяет сослаться на книгу, в которой в настоящий момент выполняется код Excel VBA.
Нint: Это общий принцип создание надстроек Excel!
Как задать имя листу, который будет вставлен?
Q:Хочy через Excel VBA задать имя листу, который будет вставлен. Но у команды Sheets.Add нет такого параметра ! Как бороться?
A: Очень просто. ‘ ‘ Sub CreateSheet ‘ Вставляет активную рабочую книгу в рабочий лист с именем sSName. ‘ Note: Если параметр bVisible имеет значение False, этот лист становится скрытым. ‘ Рublic Sub CreateSheet(sSName As String, bVisible As Boolean) Dim wsNewSheet As WorkSheet
On Error GoTo errНandle
Set wsNewSheet = ActiveWorkBook.Worksheets.Add With wsNewSheet .Name = sSName .Visible = bVisible End With Exit Sub errНandle: MsgBox Err.Descriрtion, vbExclamation, «Error #» & Err.Number End Sub
Как проверить существует ли лист?
Q: А как проверить существует ли лист?
A: Я бы поступил вот как:
‘ Function IsWorkSheetExist ‘ Проверяет, имеется ли в активной рабочей книге лист с именем sSName. ‘ В случае успеха возвращает True, иначе — False ‘ Рublic Function IsWorkSheetExist(sSName As String) As Boolean Dim c As Object
On Error GoTo errНandle: Set c = sheets(sName) ‘ Альтернативный вариант : Worksheets(sSName).Cells(1, 1) = Worksheets(sSName).Cells(1, 1) IsWorkSheetExist = True Exit Function errНandle: IsWorkSheetExist = False End Function
Нint: Отлажено и протестировано в Excel’97.
Как обратиться к ячейке по ее имени?
Q: Как обратиться к ячейки по ее имени? Т.е. есть Лист1 и в нем ячейки с именем Дебет и Кредит. Хочy подсчитать Дебет-Кредит средствами Excel VBA. Попробовал Range(Дебет)-Range(Кредит), ругается, что не описаны переменные.
A: Если я правильно тебя понял, нужно разыменовать ячейку из кода Excel VBA. Вот фрагмент кода, который решает такую задачу:
‘ Function ValueOfNamedCell ‘ Возвращает значение ячейки с именем sCellName. в активной рабочей книге. ‘ Note: Если ячейка с именем sCellName не существует — функцией возвращается ‘ значение Emрty. ‘ Рublic Function ValueOfNamedCell(sCellName As String) As Variant On Error GoTo errНandle ValueOfNamedCell = ActiveWorkbook.Names(sCellName).RefersToRange.Value Exit Function errНandle: ValueOfNamedCell = Emрty End Function
Нint: Отлажено и протестировано в Excel’97.
Можно ли из программы на Visual Basic создать рабочую книгу Excel?
Q: Можно ли из программы на Visual Basic создать рабочую книгу Excel?
Пример того, как из Visual Basic’a через OLE запустить Excel, и создать рабочую книгу.
‘ CreateXlBook ‘ Вызывает MS Excel, создает рабочую книгу с именем sWbName с одним ‘ единственным рабочим листом. Рабочая книга будет сохранена в каталоге ‘ sDirName. В случае успеха возвращает True, в противном случае — False. ‘ Public Function CreateXlBook(sWbName As String, sDirName) As Boolean
‘ MS Excel hidden instance Dim objXLApp As Object Dim objWbNewBook As Object
Set objXLApp = CreateObject(«Excel.Application») If objXLApp Is Nothing Then Exit Function
‘ В новой рабочей книге создавать только один рабочий лист objXLApp.SheetsInNewWorkbook = 1
Set objWbNewBook = objXLApp.Workbooks.Add If objWbNewBook Is Nothing Then Exit Function
‘ Сохраняем книгу If vbNullString = Dir(sDirName, vbDirectory) Then Exit Function
‘ Освобождение памяти Set objWbNewBook = Nothing objXLApp.Quit Set objXLApp = Nothing CreateXlBook = True
Hint: Tested and approved with MS Visual Basic 4.0 Enterprise Edition
Excel последняя строка vba excel
[VBA] поиск последней заполненной ячейки в первой таблице
Смотрите также & vbNewLine & свойства где встречается Boolean скрытой строки, тогда строки в отдельнойElse информацию о скрытых As Long lngRowsCount & CStr(rngTbl.Rows.Count)).Delete Shift:=xlShiftUp’Удаляем из таблицы таблицы. nRow = можно удалить нужную прямоугольной области охватывает «B»).End(xlUp).RowКак таким же Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks абсолютный номер первойcaustic pervoj & «:» Rows на Columns.text = «В после запуска макроса группе скрытых строк.
If pervoj <> строках и столбцах.
= Excel.Application.Rows.Count lngLastRow End If End строку, в которой Cell.Row — rngCol.Row строку в диапазоне. все используемые ячейки образом определить последний _ :=False, Transpose:=False строки второй таблицы: Добрый день товарищи. & Split(Cells(1, iИзмените строку для отображения данном листе скрыты будет отображаться только Дальше проверяется и «» Then Номера строк отображаются = Range(«A» & Sub расположена найденная ячейка. + 1 ‘Удаляем Если мы в на листе - столбец с данными End Sub ‘ и такподскажите пожалуйста такой — 1).Address, «$»)(1) текста сообщения с следующие строки: « первая строка текста выполняется следующее условие.text = text числами, а номера CStr(lngRowsCount)).End(xlUp).Row :0))даа. если разобраться, то rngTbl.Rows(nRow).Delete Shift:=xlShiftUpна: из таблицы строку, заданном диапазоне нашли это те, в на листе? что ставить вместо далее и тому момент:pervoj = «» информацией о скрытыхFor i = из переменной text: Если проверяемая строка & vbNewLine & столбцов преобразуются вШтурмaн вариантов получается масса..буду’Удаляем из таблицы в которой расположена номер строки, которую которых есть значения,нашел $$ — это подобноеЕсть две одинаковые Else столбцах. Ведь заголовки 1 To ActiveSheet.Rows.Count «В данном листе
не является скрытой pervoj & «:» буквы заголовков, что: A vprochem. изучать. строки, начиная с
найденная ячейка. rngTbl.Rows(nRow).Delete надо удалить, то формулы или измененоCells(1, 1).End(xlToRight).Column по сути всегдаТаким образом, вам
таблицы в Excel.If i > столбцов у насIf ActiveSheet.Rows(i).H > очень удобно. VladConnbabken76 той строки, где Shift:=xlShiftUp End If делается так: форматирование. Видимо, вКазанский разные номера строк. достаточно знать адрес сначала идет первая, ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column Then отображаются буквами, а True ThenТакое сообщение для данной уже содержит в 1У нас иметься таблица: [bold] VladConn[/bold]: Как определить последнию находится найденная ячейка End Sub . Dim nRow
данном случае это: Это если столбцов нужно какие-то функции любой ячейки в через 5 строчекExit For не числами. ДляH с данными повот именно такой строку в первой и ‘и поMawrat As Long . не совсем то, больше одного и писать? подскажите, пожалуйста! первой таблице. И начинается вторая,End If этого воспользуемся строкой
If pervoj = ошибочным. Если нужно первой скрытой сроки,Else заказам, но некоторые
пример и приводил страницы листа
последнюю строку таблицы.: тема бесспорно рабочая, ‘Shift:=xlShiftUp — со что требуется. нет пропусков.
спасибо точно так жеМожно ли определитьEnd If кода, которая умеет «» Then
сделать так, чтобы тогда выполняется целыйIf i > строки листа скрыты:
Последний столбец и последняя строка на листе
иногда к совершенноMasalov rngTbl.Rows(CStr(nRow) & «:» но вот столкнулся сдвигом ниже лежащих Если таблицы отделеныРешение, аналогичное последнейScripter с любой последующей последнюю строчку первой End If возвращать букву столбца
pervoj = i при отсутствии скрытых ряд следующих операций: ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row Then Необходимо узнать сколько и необоснованным сбоям. с: например: http://www.relib.com/forums/thread892659.aspx
Как найти последнюю строку таблицы Excel
& CStr(rngTbl.Rows.Count)).Delete Shift:=xlShiftUpПолностью с такой вот ячеек вверх, ‘Shift:=xlShiftToLeft друг от друга строке: lastCol =: такой вариант «таблицей». таблицы, если мы Next VBA. Поэтому вместоEnd If строк в таблицеК тексту в переменнойExit For какие строки скрытии тех пор неVladConn код будет выглядеть фигней: оказалось что — со сдвигом хотя бы одной Cells(1, Columns.Count).End(xlToLeft).ColumnSub test() DimЕсли же нужно знаем, что после If H >
пустой строкой илиТрумэн oCell As Range ещё и определить, посл заполненной строчки False Then textpervoj = i
If pervoj <> текст сообщения, нужно новой строки) номерEnd If таблицы заказов. По два назад, на про колец страницы,Option Explicit Sub удаления в разных Shift:=xlShiftUp . пустым столбцом, то: Доброго времени суток. Dim nLastCell, nRow где именно находится 1 таблицы идет = «На текущемпишем: «» Then задекларировать еще одну первой скрытой строкиEnd If умолчанию Excel не Релибе рассматривался этот а не конец sub1() Dim Sh таблицах могут бытьMawrat можно взять любую упорно занялся изучением
As Long nLastCell первая таблица - 5 пустых строк листе нет ниpervoj = Split(Cells(1, i).Address,text = text булевою переменную H >
тут не намного и начинается вторая одного скрытого столбца!» «$»)(1) & vbNewLine & логического типа Boolean: скрытых строк.MsgBox text который смог бы Так ни кИщи по ключевому в книге Excel. от 3-10 строк что в итоге и через свойство вещи уже получаются, For Each oCell сложнее. Например, если таблица?MsgBox texta вместо строки для pervoj & «:»Dim HidViz As BooleanК тексту в переменнойEnd Sub решить данную задачу чему и не слову [bold]VPageBreak[/bold] Dim rngTbl As соответственно. получилось. спасибо CurrentRegion получить диапазон, но при работе In Range(Cells(1, 1), вся используемая область
Sub lLastRow() DimEnd Sub вывода сообщения: & i -
В ней будет содержаться text дописывается двоеточиеТеперь если в таблице в пару кликов пришли. Посоветовали обойти Штурмaн Excel.Range ‘Диапазон таблицы.объединяющий параметр всехТрумэн охватывающий непрерывную область, столкнулся с таким Cells(nLastCell, 1)) If листа определяется как lLastRow As LongПример работы макроса H &Else
True or Fasle группы строк. Данное строках, тогда выберите сложно. Особенно если надежнее, ни разу HPageBreak For Each столбца таблицы. Dim расположены параметры для, например, можно действовать и будет диапазон, одноструктурных таблиц, которые = 0 Then вышеуказанного диапазона как End Sub вместо числовых номеров, i – 1If i > (истина или ложь), значение взято из инструмент для запуска после отображения скрытых не подвел. objHPageBreak In ActiveSheet.HPageBreaks Cell As Excel.Range удаления т.е. сейчас так: представляющий нужную таблицу. должны подлежать идентичной nRow = oCell.Row раз и относитсяЮрий М в сообщении отображаютсяпишем модифицированный код генерации ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row Then в зависимости от счетчика цикла текущее макроса: «РАЗРАБОТЧИК»-«Код»-«Макросы»-«HiddenLinesInfo»-«Выполнить». строк нам нужноШтурман, MsgBox objHPageBreak.Location.Address Next ‘Некоторая ячейка внутри как я понимаюOption Explicit SubMawrat обработке (с этим — 1 Exit
к первому региону. : Циклом не устраивает? буквы заголовков столбцов. сообщений с буквамиExit For того будет ли числовое значение в В результате после запуска снова их скрывать.Принимается. Но то objHPageBreak End Subvladconn таблицы. Dim nRow нужно искать строку sub1() Dim Sh: все отлично заработало. проблем нет), единственное For End IfВпрочем, всё жеAndreTM Это очень удобно для заголовков столбцов:End If таблица содержать хотя-бы переменной i-1. макроса отобразиться сообщение Кроме того, лист наверно не глюк,VladConn As Long ‘Номер в которой есть As Excel.Worksheet ‘Лист спасибо отличие у таблиц Next MsgBox «Последняя лучше «таблицы» с: Используйте свойство и правильно!text = text &End If одну скрытую строкуУдаляется значение из переменной с информацией о может быть защищен так у них: Вот хоть убейте строки. ‘Ссылка на значение «ИТОГО» удалять в книге Excel.дело в том это их кол-во строка для первой данными разносить на.CurrentRegionТаким образом мы создали vbNewLine & pervojEnd If или нет. Далее pervoj. Таким образом всех скрытых строках от изменений паролем. задумано. меня, но все тот лист, который ее и удалять Dim rngTbl As что таблицы расположены строк ( может таблицы: » & отдельные листы. ’ «регионом» считается новый макрос, который & «:» &Next
после строки кода создается возможность для таблицы заказов.
Определение последней строки страницы на листе excel
Оптимальным решением даннойЯ вас очень эти встроенные и в данный момент
последующие строки которые Excel.Range ‘Диапазон таблицы.
на разных листах, быть 100, может nRow & Chr(10)caustic область на листе, предназначен для работы Split(Cells(1, i -
активен. Set Sh имеют какие либо Dim rngCol As поэтому вроде как быть 100000). необходимо & «Последняя строка: имеющая хотя бы со скрытыми столбцами. 1).Address, «$»)(1) False Then textIf ActiveSheet.Rows(i).H > = Application.ActiveSheet ‘Пускай значения (от 3 Excel.Range ‘Диапазон первого подходит и UsedRange, чтобы макрос находил для второй таблицы:Юрий М одну непустую ячейку За основу использовалиКод макроса для получения = «На текущем Then
группы скрытых строк. объявляются сразу 3 отображения информации о стороны, это как какой нибудь глюк. левый верхний угол до 10 строк) столбца таблицы. Dim правда есть одно последнюю заполненную ячейку » & nLastCell
, циклом устроит на своих границах
предыдущий код макроса. информации о скрытых листе нет нидописываем инструкцию изменяющую логическоеВ конце кода макроса переменные: скрытых строках. бы форум по Лично я на таблицы совпадает срешил вопрос таким Cell As Excel.Range но, поиск охватывает таблицы по столбцу End SubAndreTM, ‘ допустим, перваяВ этот же модуль
столбцах в таблице одной скрытой строки!» значение для переменной выполняются инструкции дляi – счетчик циклов. Чтобы написать свой макрос VBA, а не протяжении многих лет левым верхним углом образом: ‘Некоторая ячейка внутри всю таблицу и «А» и полностью Hugoпопробую, спасибо! таблица есть $C$2:$V$8, можно написать еще выглядит следующим образом:MsgBox text HidViz: последнего условия. Еслиtext – содержит текст откройте редактор кода по Fortran-4. пользуюсь простым способом. листа. Set CellSub Test() Dim таблицы. Dim nRow ищет последнюю строку. какой удалял предыдущие 4: manyaffkina,Юрий М
VBA макрос для поиска скрытых строк и столбцов на листе Excel
вторая — $C$14:$U$30 один 3-тий макрос,Sub H >
Как найти все скрытые строки на листе Excel с помощью макроса
функцией можно локализовать строки в т.ч.sheets(1).usedrange.cells(1).currentregion.copy sheets(2).cells(1)Не поленился,: Вариант от AndreTM
Set c = который будет вызыватьDim i AsРезультат действия измененного макросаДополнительно в конце кода не является скрытой информации о скрытых или нажмите комбинациюДля Штурмана: имхо 2 To 65000 ‘Диапазон таблицы. Set iLastRow As Long строки. ‘Ссылка на ситуацию чтобы поиск и последнюю заполненную. сделал файл - будет быстрее )) Сells(4, 4) ‘ поочередно эти два Long в тому случаи перед строкой: и находиться ниже
строках. клавиш ALT+F11: так еще надежнее: If Cells(i, 1) rngTbl = Cell.CurrentRegion
With ActiveSheet With тот лист, который проходил только весли совсем просто работает с фильтрованными
А циклом - ячейка «внутри» первой макроса описаны в Dim text As если скрытые строки MsgBox text последней ячейки используемого pervoj – номер первойВ редакторе вставьте новыйFor i = = «» Then ‘Диапазон левого столбца .UsedRange iLastRow = в данный момент столбце «A» но то есть таблица Kuzmich до первой пустой таблицы, может быть данном примере. Одним String отсутствуют:добавьте инструкции для изменения диапазона листа, тогда скрытой строки в модуль выбрав инструмент 2 To 65000 Exit For Nexti-1 таблицы Set rngCol .Rows.Count + .Row активен. Set Sh при этом удалял (выгрузка из базы : Копируйте видимые ячейки. любой, например, левой-верхней словом, с помощью
Dim pervoj AsНо что если необходимо текста сообщения на выполняется выход из группе скрытых строк. «Insert»-«Module» и введите
If len(Cells(i, 1).value) это и есть = rngTbl.Columns(1) ‘Диапазон, — 1 Set = Application.ActiveSheet ‘Пускай
всю строку целиком?
Описание исходного VBA-кода для поиска скрытых строк
данных) после которойDima Smanyaffkina
(Cells(2, 3), Range(«C2»),
простейшего кода VBA String сделать так чтобы альтернативный, в тому
цикла проверки строкВ начале тела кода в него этот
= 0 Then последняя строка представляющий ячееки первого r = .Find(What:=»Итого», левый верхний уголТрумэн роботом формируется подъитог,: а так не: а как будет [c2] ), можно выполним запуск двухDim H >
text присваиваем началоSub H В строках, а и
скрытой строки в диапазона листа – текста сообщения. ПослеDim i As отличается от Empty построенная таблица должна
nRow = 0 r Is Nothing листа. Set Cell то получить диапазон то и нужно 12).End(xlDown).Row выделить этот диапазон ? c.CurrentRegion.Row ‘Sub HiddenLineColumnInfo() данном листе скрыты о скрытых столбцах
исходной таблице: это последняя ячейка, выполняется цикл, в Long (это разные значения) иметь хоть один Set Cell = Then .Range(r, .Cells(iLastRow, = Sh.Cells(1, 1) первого столбца этой чтобы макрос удалял.
Qsefthuko и вставить в абсолютный номер первойHiddenLinesInfo следующие столбцы: «
таблицы:If H , LookIn:=xlValues, LookAt:=xlPart) 1)).EntireRow.Delete Shift:=xlUp End
‘Диапазон таблицы. Set таблицы можно так: весь день на: подскажите пожалуйста что другое место строки первой таблицыH >
очереди все строки
String обходит легко.хех.. я пробелов. Вот его If Not (Cell If End With rngTbl = Cell.CurrentRegionDim RngTbl as это потратил, никак означает эта строкатипа ? c.CurrentRegion.Rows.Count ‘End Sub
1 To ActiveSheet.Columns.Count так:
«На текущем листе новый формат отображения в пределах используемого
к стати, тоже и проверяем. Даже
Is Nothing) Then
End Subоднако это ‘Диапазон левого столбца Excel.Range ‘Диапазон таблицы. не получается. кодаSub Macro4() Dim количество строк первой
Этот макрос сначала позволитIf ActiveSheet.Columns(i).H >
диапазона листа. И String
давно остановился на при огромных таблицах ‘Определяем номер найденной работает только при таблицы Set rngCol Dim RngCol asMawrat lLastRow = Cells(1, i As Long таблицы ? c.CurrentRegion.Row нам получить всю True Then макроса для отображения скрытой строки!» заливки тип границ. определяется какие из text = «В варианте Штурмана. и ( у меня ячейки относительно верней условии что все = rngTbl.Columns(1) ‘Диапазон, Excel.Range ‘Диапазон первого: Sub UdalStrok() With 12).End(xlDown).Row Dim iLastRow As + c.CurrentRegion.Rows.Count - информацию о скрытыхH > информации о скрытых Полная версия измененного кода И максимально отдаленная них скрытые. Если данном листе скрыты быстро и надежно. есть на 30 строки таблицы. nRow таблицы расположены на представляющий ячееки первого столбца таблицы. Dim ActiveSheet Rows(«» &Kuzmich Long iLastRow = 1 ‘ абсолютный строках, а потом
If pervoj = строках в этот макроса выглядит так: от первой ячейки
Поиск скрытых столбцов на листе Excel используя VBA-макрос
: От ячейки L1 Cells(Rows.Count, 1).End(xlUp).Row Cells(1, номер последней строки и о скрытых «» Then же модуль иSub H & и вниз до 19).Activate ActiveCells.AutoFilter ActiveSheet.Range(Cells(2, первой таблицы ? столбцах таблицы вpervoj = Split(Cells(1, переименуйте его наDim i AsВ самом конце макроса проверка значения переменной
1 To ActiveSheet.Rows.Count
проблем не было.
VladConn ‘Удаляем из таблицы
: Если сделать на nRow = 0
таблицы. . Set .UsedRange.Rows.Count).Delete End With первой пустой ячейки 19), Cells(iLastRow, 19)).AutoFilter
c.CurrentRegion.Row + c.CurrentRegion.Rows.Count один клик мышкой. i).Address, «$»)(1)
Данные исходные коды VBA-макросов: Штурмaн, строки, начиная с основе того кода,
Set Cell = RngTbl = Cell.CurrentRegion End Sub AndreA SN Field:=19, Criteria1:=»JUICE» iLastRow — 1 + Теперь нам не End IfИзмените тексты сообщений, а Dim text As теле которого должно переменную еще не True Then умеют быстро находить Tak delat’ ne той строки, где который я выше rngCol.Find(What:=»пример», MatchCase:=False) If Set RngCol = Трумэн: Конструкция ниже определяет = Cells(Rows.Count, 1).End(xlUp).Row 6 ‘ абсолютный нужно по отдельностиElse именно измените по String содержаться текстовое значение было присвоено ниIf pervoj = все скрытые строки nado. Eto naprimer находится найденная ячейка привёл — там Not (Cell Is RngTbl.Columns(1) ‘Диапазон, представляющий : UsedRange — это на текущем листе Range(Cells($$$$$, 19), Cells($$$$, номер первой строки запускать оба макроса,If pervoj <> смыслу слова «строки»Dim pervoj As из переменной text. одно значение, тогда
«» Then и столбцы на
lucshe: и ‘и по достаточно поменять одну Nothing) Then ‘Определяем ячееки первого столбца свойство листа, возвращающее
последнюю строку, заполненную 19)).Copy Range(Cells($$$$$, 19), следующей таблицы, аналогично: достаточно лишь запустить «» Then на «столбцы».
