Как найти крайнюю ячейку в excel - Исправление недочетов и поиск решений вместе с Examum.ru

Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

При этом в Excel сохраняется только часть каждого из них, которая содержит данные или форматирование. Пустые ячейки могут содержать форматирование, из-за котором последняя ячейка в строке или столбце выпадет за пределы диапазона ячеек, содержащих данные. В результате размер файла книги будет больше, чем требуется, и при печати книги на печать может потребоваться больше страниц.

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

Поиск последней ячейки с данными или форматированием на нем

Чтобы найти последнюю ячейку с данными или форматированием, щелкните в любом месте на нем и нажмите CTRL+END.

Примечание: Чтобы выбрать последнюю ячейку в строке или столбце, нажмите клавишу END, а затем клавишу СТРЕЛКА ВПРАВО или СТРЕЛКА ВНИЗ.

Очистка всего форматирования между последней ячейкой и данными

Выполните одно из указанных ниже действий.
- Чтобы выбрать все столбцы справа от последнего столбца с данными, щелкните первый заголовок столбца, нажмите и удерживайте нажатой кнопку CTRL, а затем щелкните заголовки столбцов, которые нужно выбрать.
  
  Совет: Можно также щелкнуть первый заголовок столбца и нажать CTRL+SHIFT+END.
- Чтобы выбрать все строки под последней строкой с данными, щелкните заголовок первой строки, нажмите и удерживайте нажатой кнопку CTRL, а затем щелкните заголовки строк, которые нужно выбрать.
  
  Совет: Можно также щелкнуть заголовок первой строки и нажать CTRL+SHIFT+END.
На вкладке Главная в группе Редактирование щелкните стрелку рядом с кнопкой Очистить и выберите очистить все.
Сохраните лист.
Закроем таблицу.

При повторном открыть книгу последняя ячейка данных должна быть последней ячейкой на этом сайте.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

К началу страницы

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

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

Источник

MrBrown

Пользователь

Сообщений: 114
Регистрация: 30.10.2013

Здравствуйте!
Как найти последнюю заполненную ячейку — есть много постов.
А мне нужно найти адрес правой нижней ячейки диапазона, ограниченного рамкой.
К примеру, рамка охватывает диапазон A1:G18.
Макрос должен найти ячейку G18 или, лучше, первую ячейку вне диапазона — H19.
(независимо, заполнена ячейка или пустая)
Подскажите, пожалуйста, какие команды можно использовать, чтобы макрос опирался на расположение рамки.
Спасибо.
p.s. Если на листе несколько рамок, то ищется самая крайняя ячейка (пересечение — крайний правый столбец, имеющий хотя бы единственную обрамлённую ячейку, и такая же крайняя нижняя строка).
p.p.s Догадываюсь, что нужно использовать слово «Borders», а как именно — ума не приложу.

Изменено: MrBrown — 03.06.2019 16:48:26

Sanja

Пользователь

Сообщений: 14849
Регистрация: 10.01.2013

Муторное это дело — перебирать ячейки и проверять на наличие границ. Лучше ‘привязаться’ к какому нибудь другому признаку. Как правило, какая нибудь ячейка со словом ‘Итого’ или что-то вроде этого

Изменено: Sanja — 03.06.2019 16:48:53

Согласие есть продукт при полном непротивлении сторон.

MrBrown

Пользователь

Сообщений: 114
Регистрация: 30.10.2013

03.06.2019 16:52:52

Цитата
Sanja написал: Муторное это дело

ОК, тогда так (чтобы не перебирать все миллионы ячеек):
организовать цикл справа налево, снизу вверх, начиная с сотой ячейки в строке и в столбце — «CV:100».
Этого будет более, чем достаточно.
Как макрос упирается в первую попавшуюся ячейку с бордюром (даже если ячейка касается бордюра только точечно, уголком) , так — три зелёных свистка и — msgbox «Вот она!!! Поймал!!!»

Изменено: MrBrown — 03.06.2019 16:55:28

Sanja

Пользователь

Сообщений: 14849
Регистрация: 10.01.2013

03.06.2019 17:10:03

Цитата
MrBrown написал: лучше, первую ячейку вне диапазона

Код

Sub FindBorders()
Dim cl As Range
With ActiveSheet
For Each cl In .UsedRange.Cells
    If cl.Borders(xlEdgeRight).LineStyle <> xlNone And cl.Borders(xlEdgeBottom).LineStyle <> xlNone Then
        MsgBox "Ячейка найдена!" & vbCrLf & "Адрес - " & cl.Offset(1, 1).Address(0, 0)
    End If
Next
End With
End Sub

Прикрепленные файлы

FindBorders.xlsm (15.39 КБ)

Согласие есть продукт при полном непротивлении сторон.

MrBrown

Пользователь

Сообщений: 114
Регистрация: 30.10.2013

Sanja, спасибо, но годится только для одинарной рамки («внешние границы»).
А если сделать несколько вложенных рамок или вообще замостить бордюрами («все границы»),
то макрос определяет каждую ячейку с бордюром.
А надо — только одну, самую правую и нижнюю.

Sanja

Пользователь

Сообщений: 14849
Регистрация: 10.01.2013

03.06.2019 17:25:06

Цитата
MrBrown написал: А надо — только одну, самую правую и нижнюю.

Ну поэкспериментируйте сами.

Цитата
MrBrown написал: Догадываюсь, что нужно использовать слово «Borders», а как именно — ума не приложу.

Я Вам показал как ‘использовать это слово’

Согласие есть продукт при полном непротивлении сторон.

	cм. вложение, какая из ячеек с рамкой тут крайняя? Программисты — это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!

Sanja

Пользователь

Сообщений: 14849
Регистрация: 10.01.2013

03.06.2019 17:32:07

MrBrown, я Вас сразу предупредил

Цитата
Sanja написал: Муторное это дело

Ищите другой признак.

Согласие есть продукт при полном непротивлении сторон.

MrBrown

Пользователь

Сообщений: 114
Регистрация: 30.10.2013

Ігор Гончаренко, здесь — ячейка C3.
пересечение ПРОДОЛЖЕНИЯ крайнего правого и ПРОДОЛЖЕНИЯ нижнего бордюра, и — смещение вправо вниз на ячейку.
(вариант — ячейка В2. Это та, которая остаётся внутри пересечения.)
Мне подошёл бы любой вариант.

Sanja, а можно так? (см. ответ Игорю Гончаренко).

Изменено: MrBrown — 04.06.2019 09:23:16

RAN Пользователь Сообщений: 7135 Регистрация: 21.12.2012	Опираться на границу ячейки — плохая идея. Угадайте с 2 раз, какая ячейка отвечает вашим условиям?

MrBrown

Пользователь

Сообщений: 114
Регистрация: 30.10.2013

RAN, крайняя — правая нижняя ячейка. Это С10.
Согласен, тут приведён прикольный вариант с бордюрами.
В таком случае макросу надо дать возможность самому виртуально построить рамку вокруг всех ячеек, имеющих части бордюра.

Андрей VG

Пользователь

Сообщений: 11878
Регистрация: 22.12.2012

Excel 2016, 365

#12

04.06.2019 10:33:31

Доброе время суток

Цитата
MrBrown написал: здесь — ячейка C3.

Вариант

Скрытый текст

MrBrown

Пользователь

Сообщений: 114
Регистрация: 30.10.2013

#13

05.06.2019 12:09:15

Вот частный случай:
надо найти ячейку АМ56 (закрашена жёлтым).

Андрей VG, благодарю за код. Но, как ни пытался использовать функцию, вставлять её в процедуру «Sub», — не получилось.
Знаний мало. Подскажите, что вписать в скобки после наименования функции:

Код
Sub test() getBottomRightOutsideBorderCell MsgBox result End Sub

Прикрепленные файлы

Частный случай.xlsb (10.75 КБ)

Изменено: MrBrown — 05.06.2019 12:10:55

Андрей VG

Пользователь

Сообщений: 11878
Регистрация: 22.12.2012

Excel 2016, 365

#14

05.06.2019 12:23:41

Цитата
MrBrown написал: Знаний мало

Предупреждать надо, что вы программист Ctrl+C, Ctrl+V

Код

Sub test()
    Dim lastNonBorderCell As Range
    Set lastNonBorderCell = getBottomRightOutsideBorderCell(ActiveSheet)
    If Not lastNonBorderCell Is Nothing Then MsgBox lastNonBorderCell.Address(ReferenceStyle:=xlA1, External:=True)
End Sub

MrBrown

Пользователь

Сообщений: 114
Регистрация: 30.10.2013

#15

05.06.2019 12:47:46

Андрей VG, Работает! Моя благодарность — словами не описать.

Цитата
Андрей VG написал: Предупреждать надо, что вы программист Ctrl+C, Ctrl+V

Класс! Я восхищён таким точным определением дилетанта от программирования.
Возьму на вооружение.

Изменено: MrBrown — 05.06.2019 14:12:41

Источник

Вы не упомянули, как вы успешно получаете самое правильное значение в таблице. Один из способов сделать это, введя следующую формулу в ячейку B9:

=LOOKUP(2,1/(2:2<>0),2:2)

Эта формула работает так, что 2:2<>0 возвращает массив значений True/False, в которых ячейки в строке 2 не являются пустыми. 1/(2:2<>"") возвращает массив, состоящий из 1 для каждой непустой ячейки и # DIV/0! за каждую пустую ячейку. LOOKUP ищет в возвращенном массиве значение 2; поскольку это значение отсутствует в массиве, оно находит последнее вхождение следующего наибольшего значения (в данном случае 1). LOOKUP возвращает значение из последнего параметра (2:2), которое соответствует найденному значению.

Мы можем изменить эту формулу, чтобы она возвращала значение ячейки перед последним непустым значением, введя ее в ячейку C9:

=LOOKUP(2,1/(B2:ZZ2<>0),A2:ZY2)

В этой версии массив, в котором ищется значение, смещается влево на одну ячейку от массива, в котором был произведен поиск, в результате чего ячейка слева от последней возвращаемой ячейки. Обратите внимание, что вы должны быть уверены, что данные не выходят за пределы диапазонов, используемых в LOOKUP (ZZ2 в моем примере).

Источник

При работе в Excel последняя используемая ячейка обычно выделена курсором. Чтобы ее найти, достаточно нажать клавишу Enter на странице, где вы работаете.

Если нужно найти последнюю ячейку в длинном списке — достаточно выделить одну из ячеек списка, затем поставить курсор на одну из границ ячейки (смотря в каком направлении заполнен ваш список) и осуществить двойной клик.

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

Алгоритм поиска всегда одинаков — выделяем ячейку, наводим курсор на ее границу и делаем двойной щелчок мышью.

Источник