Excel vba как определить последнюю запись в таблице excel


Содержание

Open Notes

Обо всём, что мне интересно

Полезные команды VBA

Пополняемый список полезных отрывков кода 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

Замена формулы на значение:

Добавление нового листа с именем после всех существующих:

Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = «Имя_листа»

Как узнать последний день предыдущего месяца:

LastMonthDay = DateAdd(«d», -1, DateSerial(Year(dtDate), Month(dtDate), 1))

Определение оставшихся дней месяца:

dToEndOfMonth = DateDiff(«d», dFrom, DateAdd(«d», -1, _ DateSerial(Year(dFrom), Month(dFrom) + 1, 1)))

Номер текущего дня в неделе (воскресенье — первый день):

DayOfWeek = DatePart(«w», dToday)

Создание нового файла из текущего:

pathNewBook = «C:\Temp» nameNewBook = «Имя_нового_файла.xls» Workbooks.Add ActiveWorkbook.SaveAs Filename:=pathNewBook & nameNewBook ActiveWorkbook.Close True

Сохранить текущий файл в формате CSV

Чтобы при сохранении файла в формате CSV, вместо запятых в качестве разделителя использовалась точка с запятой, следует использовать подобный код:

ActiveWorkbook.SaveAs FileName:=»Name.csv», FileFormat:=xlCSV, _ CreateBackup:=False, Local:=True ActiveWorkbook.Saved = True ActiveWorkbook.Close True

Копирование данных из одного файла в другой:

wbPath = «C:\Temp\» wbName = «Имя_файла_откуда_копируем.xls» Workbooks.Open (wbPath & wbName) Set WB = Workbooks(wbName) WB.Sheets(«Лист 1»).Range(«A1:С10»).Copy Sheet(«Лист_в_текущем_файле»).Range(«A2»).PasteSpecial xlPasteValues

Чтобы открыть файл только для чтения, следует использовать:

Workbooks.Open (Filename:=wbPath & wbName, ReadOnly:=True)

Предотвращение ошибки при неудачном поиске значения в таблице:

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):

wbName = Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, «.») — 1)

Проверка существования файла:

fPath = «C:\Temp\» fName = «Файл.txt» If Dir(fPath & fName) = «» Then MsgBox «Файл не найден:» & Chr(13) & fPath & fName Exit Sub End If

Кнопка, скрывающая/разворачивающая часть таблицы:

Private Sub tbV ).H >

Обновление сводной таблицы:

currPath = ThisWorkbook.Path currWBName = ThisWorkbook.Name ListName.PivotTables(«СводнаяТаблица1»).ChangePivotCache ActiveWorkbook. _ PivotCaches.Create(SourceType:=xlDatabase, SourceData:=currPath & «[» & _ currWBName & «]Лист1!R1C1:R10C5»)

Обращение к элементам Frame:

Замена #ДЕЛ/0! в диапазоне:

Selection.Replace What:=»#DIV/0!», Replacement:=»», LookAt:=xlPart,_ SearchOrder:=xlByRows, MatchCase:=False,_ SearchFormat:=False, ReplaceFormat:=False

Количество строк в отфильтрованной таблице:

Быстро убрать лишние пробелы в диапазоне:

Программно снять защиту с листа:

Работа с диапазоном

Умножить диапазон на число:

ThisWorkbook.Sheets(1).Range(«A1:A10») = _ ThisWorkbook.Sheets(1).Evaluate(«A1:A10» & «*80»)

Добавить ко всем значениям диапазона строку:

ThisWorkbook.Range(«A1:A10»).Value = _ Evaluate(«=»»» & addTxt & «»» & » & ThisWorkbook.Range(«A1:A10»).Address)

Сортировка выбранного столбца в сводной таблице

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.

Илон Маск рекомендует:  Элемент output

Внимание! Все записи в столбце 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

Пример определения номера строки и столбца последней заполненной ячейки.
iRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count — 1
iClm = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count — 1 With ActiveSheet.UsedRange
iRow = .Row + .Rows.Count — 1
iClm = .Column + .Columns.Count — 1
End With Set iList = ActiveSheet
iRow = iList.UsedRange.Row + iList.UsedRange.Rows.Count — 1
iClm = iList.UsedRange.Column + iList.UsedRange.Columns.Count — 1 Set >iRow = iDiapazon.Row + iDiapazon.Rows.Count — 1
iClm = iDiapazon.Column + iDiapazon.Columns.Count — 1 Комментарий :
Так как свойство UsedRange принадлежит об’екту Worksheet, то использование ссылки на этот об’ект обязательно.

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

Вариант III.
Для определения количества заполненных ячеек в смежном диапазоне можно воспользоваться свойством CurrentRegion об’екта Range

Пример определения количества строк и столбцов в смежном с ячейкой диапазоне, а также адрес этого диапазона.
iRow = Columns(«A»).CurrentRegion.Rows.Count
iClm = Rows(1).CurrentRegion.Columns.Count
iAddress = Range(«A1»).CurrentRegion.Address Примечание :
Особенностью свойства CurrentRegion является то, что он возвращает весь диапазон, но только состоящий из смежных ячеек.

Комментарий :
важно При определении последней заполненной строки, этот вариант будет корректно работать только при условии, что данные в строке и столбце начинаются с самой первой ячейки и не содержат пустых строк и столбцов.

Предупреждение :
Если рабочий лист защищён, то использование этого варианта приведёт к возникновению ошибки, которую можно избежать, если воспользоваться этим советом.

Вариант IV.
Для определения номера строки и столбца последней заполненной ячейки можно использовать функцию ПОЛУЧИТЬ.ДОКУМЕНТ (макроязык Excel4.0)

Пример определения номера строки и столбца последней заполненной ячейки в активном рабочем листе.
iRow = ExecuteExcel4Macro(«GET.DOCUMENT(10)»)
iClm = ExecuteExcel4Macro(«GET.DOCUMENT(12)»)
Пример определения номера строки и столбца последней заполненной ячейки в активной рабочей книге и конкретном рабочем листе.
iRow = ExecuteExcel4Macro(«GET.DOCUMENT(10,»»Лист1″»)»)
iClm = ExecuteExcel4Macro(«GET.DOCUMENT(12,»»Лист1″»)»)
Пример определения номера строки и столбца последней заполненной ячейки в конкретной рабочей книге и листе.
iRow = ExecuteExcel4Macro(«GET.DOCUMENT(10,»»[ОткрытаяКнига.xls]Лист1″»)»)
iClm = ExecuteExcel4Macro(«GET.DOCUMENT(12,»»[ОткрытаяКнига.xls]Лист1″»)»)
Вариант V.
Для определения номера последней заполненной ячейки в конкретной строке или столбце, а также для определения последней заполненной ячейки можно использовать метод Find

Пример определения номера последней заполненной ячейки в конкретной строке и столбце.
On Error Resume Next

iRow = Columns(«C»).Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
iClm = Rows(10).Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column

Пример определения номера строки и столбца последней заполненной ячейки.
On Error Resume Next

iRow = Cells.Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
iClm = Cells.Find(What:=»*», LookIn:=xlValues, SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column Примечание :
Вышеупомянутый синтаксис может вызвать ошибку, если указанный диапазон не содержит данных. Для того, чтобы этого избежать, во всех примерах использован «режим отложенной ошибки» 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

Пример определения номера последней заполненной ячейки в конкретной строке и столбце.
iRow = Columns(3).End(xlDown).Row
iRow = Columns(«C»).End(xlDown).Row
iClm = Rows(10).End(xlToRight).Column Комментарий :
важно Этот вариант будет корректно работать только при условии, что данные в строке, или столбце начинаются с самой первой ячейки и не содержат пустых ячеек.

Вариант VII.
Тот же самый способ, что и предыдущий, но с небольшими изменениями.

Пример определения номера последней заполненной ячейки в конкретной строке и столбце.
iRow = Cells(65536, 3).End(xlUp).Row
iRow = Cells(65536, «C»).End(xlUp).Row
iClm = Cells(10, 256).End(xlToLeft).Column

iRow = Cells(Rows.Count, 3).End(xlUp).Row
iRow = Cells(Rows.Count, «C»).End(xlUp).Row
iClm = Cells(10, Columns.Count).End(xlToLeft).Column В зависимости от Вашего кода можно применять различные подварианты, например : iRow = Columns(3).Rows(65536).End(xlUp).Row
iRow = Columns(«C»).Rows(65536).End(xlUp).Row Комментарий :
важно Этот вариант будет работать при любых условиях, так как маловероятно, что последней заполненной ячейкой окажется именно последняя ячейка в столбце, однако и эту вероятность можно учесть :
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 = Application.CountA(Columns(3))
iRow = Application.CountA(Columns(«C»))
iRow = WorksheetFunction.CountA(Columns(3))
iRow = Excel.Application.CountA(Columns(«C»))
iRow = Excel.WorksheetFunction.CountA(Columns(3))
iRow = Application.WorksheetFunction.CountA(Columns(«C»)) Комментарий :
важно Так как функция СЧЁТЗ считает количество непустых ячеек, то этот вариант будет корректно работать только при условии, что данные в столбце начинаются с самой первой ячейки и не содержат пустых ячеек.

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

Илон Маск рекомендует:  Что такое код createellipticrgn

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

Дополнение :
Для того чтобы Ваш макрос стал более понятным, лучше использовать имена переменных, несущих смысловую нагрузку и поэтому именовать номер строки последней ячейки не 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

objWbNewBook.SaveAs (sDirName + «\» + sWbName + «.xls»)
CreateXlBook = True

‘ Освобождение памяти
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 >​ очень удобно.​
​VladConn​babken76​ той строки, где​ 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).Column​Sub test() Dim​Если же нужно​ знаем, что после​
​If H >

​ пустой строкой или​​Трумэн​ oCell As Range​ ещё и определить,​ посл заполненной строчки​ False Then text​pervoj = 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 text​a вместо строки для​ pervoj & «:»​Dim HidViz As Boolean​К тексту в переменной​End Sub​ решить данную задачу​ чему и не​
​ слову [bold]VPageBreak[/bold]​ Dim rngTbl As​ соответственно.​ получилось. спасибо​ CurrentRegion получить диапазон,​ но при работе​ In Range(Cells(1, 1),​ вся используемая область​

​Sub lLastRow() Dim​​End 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 – 1​If 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 Sub​Mawrat​ обработке (с этим​ — 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 & pervoj​End 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 text​If ActiveSheet.Rows(i).H >​ = Application.ActiveSheet ‘Пускай​ значения (от 3​ Excel.Range ‘Диапазон первого​ подходит и UsedRange,​ чтобы макрос находил​ для второй таблицы:​Юрий М​ одну непустую ячейку​ За основу использовали​Код макроса для получения​ = «На текущем​ Then​

​ группы скрытых строк.​​ объявляются сразу 3​
​ отображения информации о​ стороны, это как​ какой нибудь глюк.​
​ левый верхний угол​ до 10 строк)​ столбца таблицы. Dim​ правда есть одно​ последнюю заполненную ячейку​ » & nLastCell​

​, циклом устроит​​ на своих границах​

​ предыдущий код макроса.​​ информации о скрытых​
​ листе нет ни​дописываем инструкцию изменяющую логическое​В конце кода макроса​ переменные:​ скрытых строках.​ бы форум по​ Лично я на​ таблицы совпадает с​решил вопрос таким​ Cell As Excel.Range​ но, поиск охватывает​ таблицы по столбцу​ End Sub​AndreTM,​ ‘ допустим, первая​В этот же модуль​

​ столбцах в таблице​
​ одной скрытой строки!»​ значение для переменной​ выполняются инструкции для​i – счетчик циклов.​
​Чтобы написать свой макрос​ VBA, а не​ протяжении многих лет​ левым верхним углом​ образом:​ ‘Некоторая ячейка внутри​ всю таблицу и​
​ «А» и полностью​
​Hugo​попробую, спасибо!​
​ таблица есть $C$2:$V$8,​ можно написать еще​ выглядит следующим образом:​MsgBox text​ HidViz:​ последнего условия. Если​text – содержит текст​ откройте редактор кода​
​ по Fortran-4.​ пользуюсь простым способом.​ листа. Set Cell​Sub 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 S​manyaffkina​

  1. ​ (Cells(2, 3), Range(«C2»),​
  2. ​ простейшего кода VBA​ String​ сделать так чтобы​ альтернативный, в тому​
  3. ​ цикла проверки строк​В начале тела кода​ в него этот​

​ = 0 Then​ последняя строка​ представляющий ячееки первого​ r = .Find(What:=»Итого»,​ левый верхний угол​Трумэн​ роботом формируется подъитог,​: а так не​: а как будет​ [c2] ), можно​ выполним запуск двух​Dim H >

  1. ​ text присваиваем начало​Sub H В​ строках, а и​
  2. ​ скрытой строки в​ диапазона листа –​ текста сообщения. После​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.CurrentRegion​Dim 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 as​Mawrat​
​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-макрос

​ текущая строка является​ следующие строки: «​ Правда проверяю на​ 000 строк) все​ = Cell.Row -​ разных листах​ столбца таблицы (диапазона)​

​ Cell as Excel.Range​ .UsedRange.Rows.Count — 3​

    ​: От ячейки 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​ на «столбцы».​

Илон Маск рекомендует:  Sqlзапросы, использующие exists
Понравилась статья? Поделиться с друзьями:
Кодинг, CSS и SQL