Как найти последнюю заполненную ячейку в excel vba

Как найти последнюю заполненную ячейку в excel vba

Как определить последнюю ячейку на листе через VBA?

Очень часто при внесении данных на лист Excel возникает вопрос определения последней заполненной или первой пустой ячейки. Чтобы впоследствии с этой первой пустой ячейки начать заносить данные. В этой теме я опишу несколько способов определения последней заполненной ячейки.

В качестве переменной, которой мы будем присваивать номер последней заполненной строки, у нас во всех примерах будет lLastRow . Объявлять мы её будем как Long . Для экономии памяти можно было бы использовать и тип Integer, но т.к. строк на листе может быть больше 32767 (это максимальное допустимое значение переменных типа Integer ) нам понадобиться именно Long , во избежание ошибки. Подробнее про типы переменных можно прочитать в статье Что такое переменная и как правильно её объявить

Одинаковые переменные для всех примеров

D im lLastRow As Long ‘а для lLastCol можно применить тип Integer, ‘т.к. столбцов в Excel пока меньше 32767 Dim lLastCol As Long

Dim lLastRow As Long

‘а для lLastCol можно применить тип Integer,

‘т.к. столбцов в Excel пока меньше 32767

Dim lLastCol As Long

Способ 1:
Определение последней заполненной строки через свойство End

l LastRow = Cells(Rows.Count,1).End(xlUp).Row

определяя таким способом нам надо знать что:
1 — это номер столбца, последнюю заполненную ячейку в котором мы определяем. В данном случае это столбце №1 или А.
Это самый распространенный метод определения последней строки. Используя его мы можем определить последнюю ячейку только в одном конкретном столбце. Но в большинстве случаев этого достаточно.

Правда, следует знать одну вещь: если у вас заполнены все строки в просматриваемом столбце (или будет заполнена самая последняя ячейка столбца) — то результат будет неверный (ну или не совсем такой, какой ожидали увидеть вы)
Определение последнего столбца через свойство End

l LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

lLastCol = Cells(1, Columns.Count).End(xlToLeft).Column

1 — это номер строки, последнюю заполненную ячейку в которой мы определяем.

Данный метод лишен недостатков, присущих второму и третьему способам. Однако есть другой, в определенных ситуациях даже полезный: при таком методе определения игнорируются строки, скрытые фильтром, группировкой или командой Скрыть (Hide) . Т.е. если последняя строка таблицы будет скрыта, то данный метод вернет номер последней видимой заполненной строки, а не последней реально заполненной.

Способ 2:
Определение последней заполненной строки через SpecialCells

l LastRow = Cells.SpecialCells(xlLastCell).Row

Определение последнего столбца через SpecialCells

l LastCol = Cells.SpecialCells(xlLastCell).Column

Данный метод не требует указания номера столбца и возвращает максимальную последнюю ячейку (строку — Row либо столбец — Column ) . Но используя данный метод следует помнить, что не всегда можно получить реальную последнюю заполненную ячейку, т.е. именно ячейку со значением. Если вы где-то ниже занесете данные и сразу удалите их из таблицы, а затем примените такой метод, то lLastRow будет равна значению строки, из которой вы только что удалили значения. Другими словами требует обязательного обновления данных, а этого можно добиться только сохранив и закрыв документ и открыв его снова. Так же, если какая-либо ячейка содержит форматирование (например, заливку) , но не содержит никаких значений, то она тоже будет считаться заполненной.
Плюс данный метод определения последней ячейки не будет работать на защищенном листе(Рецензирование -Защитить лист).

Я этот метод использую только для определения в только что созданном документе, в котором только добавляю строки.

Способ 3:
Определение последней строки через UsedRange

l LastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count — 1

lLastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count — 1

Определение последнего столбца через UsedRange

l LastCol = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count — 1

lLastCol = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count — 1

• ActiveSheet.UsedRange.Row — этой строкой мы определяем первую ячейку, с которой начинаются данные на листе. Важно понимать для чего это — если у вас первые строк 5 не заполнены ничем, то данная строка вернет 6 (т.е. номер первой строки с данными) . Если же все строки заполнены — то вернет 1 .
• ActiveSheet.UsedRange.Rows.Count — определяем кол-во строк, входящих в весь диапазон данных на листе.
  Т.е. получается: первая строка данных + кол-во строк с данными — 1. Зачем вычитать единицу? Попробуем посчитать вместе: первая строка: 3 . Всего строк: 3 . 3 + 3 = 6. Вроде все верно, чего тут непонятного? А теперь выделите на листе три ячейки, начиная с 3-ей. Все верно. Ведь у нас в 3-ей строке уже есть данные. Думаю, остальное уже понятно и без моих пояснений.
• То же самое и с ActiveSheet.UsedRange.Column , только уже не для строк, а для столбцов.

Обладает всеми недостатками предыдущего метода. . Однако, можно перед определением последней строки/столбца записать строку: With ActiveSheet.UsedRange: End With
Это должно переопределить границы рабочего диапазона и тогда определение последней строки/столбца сработает как ожидается, даже если до этого в ячейке содержались данные, которые впоследствии были удалены.

Если хотите получить первую пустую ячейку на листе придется вспомнить математику. Т.к. последнюю заполненную мы определили, то первая пустая — следующая за ней. Т.е. к результату необходимо прибавить 1.

Способ 4:
Определение последней строки и столбца, а так же адрес ячейки методом Find

D im rF As Range Dim lLastRow As Long, lLastCol As Long ‘ищем последнюю ячейку на листе, в которой хранится хоть какое-то значение Set rF = ActiveSheet.UsedRange.Find("*", , xlValues, xlWhole, xlPrevious) If Not rF Is Nothing Then lLastRow = rF.Row ‘последняя заполненная строка lLastCol = rF.Column ‘последний заполненный столбец MsgBox rF.Address ‘показываем сообщение с адресом последней ячейки Else ‘если ничего не нашлось — значит лист пустой ‘и можно назначить в качестве последних первую строку и столбец lLastRow = 1 lLastCol = 1 End If

Dim rF As Range

Dim lLastRow As Long, lLastCol As Long

‘ищем последнюю ячейку на листе, в которой хранится хоть какое-то значение

Set rF = ActiveSheet.UsedRange.Find("*", , xlValues, xlWhole, xlPrevious)

If Not rF Is Nothing Then

lLastRow = rF.Row ‘последняя заполненная строка

lLastCol = rF.Column ‘последний заполненный столбец

MsgBox rF.Address ‘показываем сообщение с адресом последней ячейки

‘если ничего не нашлось — значит лист пустой

‘и можно назначить в качестве последних первую строку и столбец

Этот метод, пожалуй, самый оптимальный в случае, если надо определить последнюю строку/столбец на листе без учета форматов и формул — только по отображаемому значению в ячейке. Например, если на листе большая таблица и последние строки заполнены формулами, возвращающими пустую ячейку(=""), предыдущие варианты вернут строку/столбец ячейки с последней формулой, в то время как данный метод вернет адрес ячейки только в случае, если в ячейке реально отображается какое-то значение. Такой подход часто используется для того, чтобы определить границы данных для последующего анализа заполненных данных, чтобы не захватывать пустые ячейки и не тратить время на их проверку.

Однако данный метод не будет учитывать в просмотре скрытые строки и столбцы . Это следует учитывать при его применении.

небольшой практический код, который поможет вам понять, как использовать полученную переменную:

Find last used cell in Excel VBA

I’m getting the wrong output when I put a single element into a cell. But when I put more than one value into the cell, the output is correct. What’s the reason behind this?

14 Answers 14

NOTE: I intend to make this a «one stop post» where you can use the Correct way to find the last row. This will also cover the best practices to follow when finding the last row. And hence I will keep on updating it whenever I come across a new scenario/information.

Unreliable ways of finding the last row

Some of the most common ways of finding last row which are highly unreliable and hence should never be used.

1. UsedRange
2. xlDown
3. CountA

UsedRange should NEVER be used to find the last cell which has data. It is highly unreliable. Try this experiment.

Type something in cell A5 . Now when you calculate the last row with any of the methods given below, it will give you 5. Now color the cell A10 red. If you now use the any of the below code, you will still get 5. If you use Usedrange.Rows.Count what do you get? It won’t be 5.

Here is a scenario to show how UsedRange works.

xlDown is equally unreliable.

Consider this code

What would happen if there was only one cell ( A1 ) which had data? You will end up reaching the last row in the worksheet! It’s like selecting cell A1 and then pressing End key and then pressing Down Arrow key. This will also give you unreliable results if there are blank cells in a range.

CountA is also unreliable because it will give you incorrect result if there are blank cells in between.

And hence one should avoid the use of UsedRange , xlDown and CountA to find the last cell.

Find Last Row in a Column

To find the last Row in Col E use this

If you notice that we have a . before Rows.Count . We often chose to ignore that. See THIS question on the possible error that you may get. I always advise using . before Rows.Count and Columns.Count . That question is a classic scenario where the code will fail because the Rows.Count returns 65536 for Excel 2003 and earlier and 1048576 for Excel 2007 and later. Similarly Columns.Count returns 256 and 16384 , respectively.

The above fact that Excel 2007+ has 1048576 rows also emphasizes on the fact that we should always declare the variable which will hold the row value as Long instead of Integer else you will get an Overflow error.

Note that this approach will skip any hidden rows. Looking back at my screenshot above for column A, if row 8 were hidden, this approach would return 5 instead of 8 .

Find Last Row in a Sheet

To find the Effective last row in the sheet, use this. Notice the use of Application.WorksheetFunction.CountA(.Cells) . This is required because if there are no cells with data in the worksheet then .Find will give you Run Time Error 91: Object Variable or With block variable not set

Find Last Row in a Table (ListObject)

The same principles apply, for example to get the last row in the third column of a table:

Note: this answer was motivated by this comment. The purpose of UsedRange is different from what is mentioned in the answer above.

As to the correct way of finding the last used cell, one has first to decide what is considered used, and then select a suitable method. I conceive at least three meanings:

Used = non-blank, i.e., having data.

Used As per official documentation, this is the criterion used by Excel at the time of saving. See also this official documentation. If one is not aware of this, the criterion may produce unexpected results, but it may also be intentionally exploited (less often, surely), e.g., to highlight or print specific regions, which may eventually have no data. And, of course, it is desirable as a criterion for the range to use when saving a workbook, lest losing part of one’s work.

Used or conditional formatting. Same as 2., but also including cells that are the target for any Conditional Formatting rule.

How to find the last used cell depends on what you want (your criterion).

For criterion 1, I suggest reading this answer. Note that UsedRange is cited as unreliable. I think that is misleading (i.e., «unfair» to UsedRange ), as UsedRange is simply not meant to report the last cell containing data. So it should not be used in this case, as indicated in that answer. See also this comment.

For criterion 2, UsedRange is the most reliable option, as compared to other options also designed for this use. It even makes it unnecessary to save a workbook to make sure that the last cell is updated. Ctrl + End will go to a wrong cell prior to saving (“The last cell is not reset until you save the worksheet”, from http://msdn.microsoft.com/en-us/library/aa139976%28v=office.10%29.aspx. It is an old reference, but in this respect valid).

For criterion 3, I do not know any built-in method. Criterion 2 does not account for Conditional Formatting. One may have formatted cells, based on formulas, which are not detected by UsedRange or Ctrl + End . In the figure, the last cell is B3, since formatting was applied explicitly to it. Cells B6:D7 have a format derived from a Conditional Formatting rule, and this is not detected even by UsedRange . Accounting for this would require some VBA programming.

As to your specific question: What’s the reason behind this?

Your code uses the first cell in your range E4:E48 as a trampoline, for jumping down with End(xlDown) .

The «erroneous» output will obtain if there are no non-blank cells in your range other than perhaps the first. Then, you are leaping in the dark, i.e., down the worksheet (you should note the difference between blank and empty string!).

If your range contains non-contiguous non-blank cells, then it will also give a wrong result.

If there is only one non-blank cell, but it is not the first one, your code will still give you the correct result.

VBA Excel. Номер последней заполненной строки

Переменную, которой присваивается номер последней строки, следует объявлять как Long или Variant, например: Dim PosStr As Long . В современных версиях Excel количество строк на рабочем листе превышает максимальное значение типа данных Integer.

Таблица в верхнем левом углу

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

Пример таблицы с набором данных в Excel

Вариант 1
Основная формула для поиска последней строки в такой таблице, не требующая соблюдения каких-либо условий:
PosStr = Cells(1, 1).CurrentRegion.Rows.Count

Вариант 2
Ниже таблицы не должно быть никаких записей, в том числе ранее удаленных:
PosStr = ActiveSheet.UsedRange.Rows.Count

Вариант 3
В первом столбце таблицы не должно быть пропусков, а также в таблице должно быть не менее двух заполненных строк, включая строку заголовков:
PosStr = Cells(1, 1).End(xlDown).Row

Вариант 4
В первой колонке рабочего листа внутри таблицы не должно быть пропусков, а ниже таблицы в первой колонке не должно быть других заполненных ячеек:
PosStr = WorksheetFunction.CountA(Range(«A:A»))

Вариант 5
Ниже таблицы не должно быть никаких записей:
PosStr = Cells.SpecialCells(xlLastCell).Row

Последняя строка любой таблицы

Последнюю заполненную строку для любой таблицы будем искать, отталкиваясь от ее верхней левой ячейки: Cells(a, b) .

Вариант 1
Основная формула для поиска последней строки в любой таблице, не требующая соблюдения каких-либо условий:
PosStr = Cells(a, b).CurrentRegion.Cells(Cells(a, b).CurrentRegion.Cells.Count).Row

Вариант 2
Дополнительная формула с условием, что в первом столбце таблицы нет пустых ячеек:
PosStr = Cells(a, b).End(xlDown).Row

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

VBA Last Row

By Madhuri Thakur

Excel VBA Last Row

Finding the last row in a column is an important aspect in writing macro’s and making those dynamic. As we would not prefer to update the cell ranges every now and then when we are working with Excel cell references. As being a coder/developer, you would always prefer to write a dynamic code which can be used on any data and suffice your requirement. Moreover, it would always be great if you have the last row known of your data so that you can dynamically change the code as per your requirement.

I will just point out one example which iterates the importance of dynamic code.

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

Suppose I have data as given below with employee and their salaries.

And look at the code given below:

Code:

Here, this code prints the sum of salaries for all employees (cell B2:B11) in cell D2. See the image below:

Now, what if I add some cells to this data and run this code again?

Logically speaking, the above code will not sum up all the 14 rows from column B. Reason for the same is the range which we have updated under WorksheetFunction (which is B2:B11). This is the reason a dynamic code which can take the last filled row into consideration makes it more important for us.

In this article, I will introduce some methods which can be useful in finding out the last row for a given data set using VBA code.

How to Find Last used Row in Column Using VBA?

Below are the different examples with different methods to find the last used Row of a Column in Excel using VBA Code.

Example #1 – Using Range.End() Method

Well, this method is as same as using the Ctrl + Down Arrow in Excel to go to the last non-empty row. On similar lines, follow the below steps for creating code in VBA to reach to the last non-empty row of a column in Excel.

Step 1: Define a variable which can take value for the last non-empty row of the excel column.

Code:

Here, the variable Last_Row is defined as LONG just to make sure it can take any number of arguments.

Step 2: Use the defined variable to hold the value of the last non-empty row.

Code:

Step 3: Type the code starting with CELLS (Rows.Count in front of Last_Row =.

Code:

Step 4: Mention 1 after a comma in the above-mentioned code. The value numeric 1 is synonyms to the first column in the excel sheet.

Code:

This code allows VBA to find out the total number of (empty + non-empty) rows present in the first column of the excel worksheet. This means this code allows the system to go to the last cell of Excel.

Now, what if you are at the last cell of the excel and want to go up to the last non-empty row? You’ll use Ctrl + Up Arrow, right?

The same logic we are going to use in the next line of code.

Step 5: Use a combination of End key and xlUp to go to the last non-empty row in excel.

Code:

This will take you to the last non-empty row in the excel. However, you wanted a row number for the same.

Step 6: Use ROW to get the row number of the last non-empty row.

Code:

Step 7: Show the value of Last_Row, which contains the last non-empty row number using MsgBox.

Code:

Step 8: Run the code using the Run button or hitting F5 and see the output.

Output:

Step 9: Now, let’s delete one row and see if the code gives an accurate result or not. It will help us checking the dynamism of our code.

Example #2 – Using Range and SpecialCells

We can also use the Range and SepcialCells property of VBA to get the last non-empty row of the excel sheet.

Follow the below steps to get the last non-empty row in excel using VBA code:

Step 1: Define a variable again as Long.

Code:

Step 2: Start storing the value to the variable Last_Row using the assignment operator.

Code:

Step 3: Start Typing Range(“A:A”).

Code:

Step 4: Use the SpecialCells function to find out the last non-empty cell.

Code:

This function SpecialCells selects the last cell from your excel as it is written in the parentheses (xlCellTypeLastCell allows you to select the last non-empty cell from your excel sheet).

Step 5: Now, use ROW to get the last row from your excel sheet.

Code:

This will return the last non-empty row for you from your excel.

Step 6: Now, assign this value of Last_Row to MsgBox so that we can see the last non-empty row number on the message box.

Code:

Step 7: Run the code by hitting the F5 or Run button placed at the top of the left corner.

Output:

You can see that the last non-empty cell number is popped out through MsgBox with reference to the column A because we have mentioned the column A under the Range function while defining the variable formula.

Step 8: If we delete a row and can run this formula. Let’s see what happens.

You can see the system has still given a row count of 14. Even though I have deleted a row and the actual row count is 13, the system has not captured the row count accurately. For the system to capture the actual row count, you need to save the worksheet and run the code again.

You can see the actual row count is showing in this screenshot now.

Example #3 – Using Range.Find()

Follow the below steps to get the last non-empty row in excel using VBA code:

Step 1: Define a variable as long.

Code:

Step 2: Now, use the following code to see the last non-empty row.

Code:

Here, the FIND function looks for the first non-blank cell. Asterisk (*) is a wildcard operator which helps in finding out the same.

Starting from cell A1, the system goes back to the last cell from the sheet and searches in a backward direction (xlPrevious). It moves from right to left (xlByRows) and loops up in the same sheet through all the rows on similar lines until it finds a non-blank row (see the .ROW at the end of the code).

Step 3: Use MsgBox to store the value of the last non-empty row and see it as a pop-up box.

Code:

Step 4: Run the code and see the output as a pop-up box containing the last non-empty row number.

Output:

Things to Remember

• End (Example1) can be used to find out the first blank cell/row or last non-empty cell/row in a given column using the VBA code.
• The end works on a single column most of the time. If you have data in ranges, it would be difficult to decide which column should be used to find out the last non-empty row.
• Find (Example3) works on an entire range from the point of start and finds out the last non-empty cell/row in a given column using VBA code. It also can be used to find out the last non-empty column.

Recommended Articles

This is a guide to VBA Last Row. Here we discuss how to find the last used row in a given column along with some practical examples and a downloadable excel template. You may also look at the following articles to learn more –