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

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

Номер последней заполненной строки в таблице Excel обычно используется в коде VBA для определения следующей за ней первой пустой строки для добавления новой записи. А также для задания интервала для поиска и обработки информации с помощью цикла For… Next (указание границ обрабатываемого диапазона).

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

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

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

Пример таблицы с набором данных в 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.

How can I determine the last row in an Excel sheet, including some blank lines in the middle?

With this function:

Function ultimaFilaBlanco(col As String) As Long        
        Dim lastRow As Long
        With ActiveSheet
            lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, col).End(xlUp).row
        End With            
        ultimaFilaBlanco = lastRow          
End Function

And this data:

Row 1 : Value
Row 2 : Value
Row 3 : Value
Row 4 : Value
Row 5 : Value
Row 6 : White
Row 7 : Value
Row 8 : Value
Row 9 : Value
Row 10  : White
Row 11  : White
Row 12  : White
Row 13  : Value
Row 14  : White

The function returns 5, and I need 13. Any idea how to do it?

Paolo Stefan's user avatar

Paolo Stefan

10.1k5 gold badges45 silver badges64 bronze badges

asked Dec 3, 2012 at 15:49

Jonathan Raul Tapia Lopez's user avatar

4

The best way to get number of last row used is:

ActiveSheet.UsedRange.Rows.Count for the current sheet.

Worksheets("Sheet name").UsedRange.Rows.Count for a specific sheet.

To get number of the last column used:

ActiveSheet.UsedRange.Columns.Count for the current sheet.

Worksheets("Sheet name").UsedRange.Columns.Count for a specific sheet.

I hope this helps.

Abdo

answered Jun 12, 2013 at 14:19

Abdelhameed Mahmoud's user avatar

3

ActiveSheet.UsedRange.Rows.Count + ActiveSheet.UsedRange.Rows(1).Row -1

Short. Safe. Fast. Will return the last non-empty row even if there are blank lines on top of the sheet, or anywhere else. Works also for an empty sheet (Excel reports 1 used row on an empty sheet so the expression will be 1). Tested and working on Excel 2002 and Excel 2010.

answered Dec 23, 2013 at 14:30

manuel's user avatar

3

I use the following:

lastrow = ActiveSheet.Columns("A").Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

It’ll find the last row in a specific column. If you want the last used row for any column then:

lastrow = ActiveSheet.Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

answered Dec 3, 2012 at 17:24

Sid Holland's user avatar

Sid HollandSid Holland

2,8713 gold badges27 silver badges43 bronze badges

1

You’re really close. Try using a large row number with End(xlUp)

Function ultimaFilaBlanco(col As String) As Long

        Dim lastRow As Long
        With ActiveSheet
            lastRow = ActiveSheet.Cells(1048576, col).End(xlUp).row
        End With

        ultimaFilaBlanco = lastRow

End Function

answered Dec 3, 2012 at 16:18

fbonetti's user avatar

fbonettifbonetti

6,5823 gold badges33 silver badges32 bronze badges

2

LastRow = ActiveSheet.UsedRange.Rows.Count

CallumDA's user avatar

CallumDA

12k6 gold badges30 silver badges52 bronze badges

answered Dec 3, 2012 at 16:19

jtorreon's user avatar

2

The Problem is the “as string” in your function. Replace it with “as double” or “as long” to make it work. This way it even works if the last row is bigger than the “large number” proposed in the accepted answer.

So this should work

Function ultimaFilaBlanco(col As double) As Long        
    Dim lastRow As Long
    With ActiveSheet
        lastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, col).End(xlUp).row
    End With            
    ultimaFilaBlanco = lastRow          
End Function

answered Jun 14, 2013 at 12:03

user1965813's user avatar

user1965813user1965813

6715 silver badges16 bronze badges

If sheet contains unused area on the top, UsedRange.Rows.Count is not the maximum row.

This is the correct max row number.

maxrow = Sheets("..name..").UsedRange.Rows(Sheets("..name..").UsedRange.Rows.Count).Row

answered Jun 21, 2018 at 5:26

JHK's user avatar

0

Well apart from all mentioned ones, there are several other ways to find the last row or column in a worksheet or specified range.

Function FindingLastRow(col As String) As Long

  'PURPOSE: Various ways to find the last row in a column or a range
  'ASSUMPTION: col is passed as column header name in string data type i.e. "B", "AZ" etc.

   Dim wks As Worksheet
   Dim lstRow As Long

   Set wks = ThisWorkbook.Worksheets("Sheet1") 'Please change the sheet name
   'Set wks = ActiveSheet   'or for your problem uncomment this line

   'Method #1: By Finding Last used cell in the worksheet
   lstRow = wks.Range("A1").SpecialCells(xlCellTypeLastCell).Row

   'Method #2: Using Table Range
   lstRow = wks.ListObjects("Table1").Range.Rows.Count

   'Method #3 : Manual way of selecting last Row : Ctrl + Shift + End
   lstRow = wks.Cells(wks.Rows.Count, col).End(xlUp).Row

   'Method #4: By using UsedRange
   wks.UsedRange 'Refresh UsedRange
   lstRow = wks.UsedRange.Rows(wks.UsedRange.Rows.Count).Row

   'Method #5: Using Named Range
   lstRow = wks.Range("MyNamedRange").Rows.Count

   'Method #6: Ctrl + Shift + Down (Range should be the first cell in data set)
   lstRow = wks.Range("A1").CurrentRegion.Rows.Count

   'Method #7: Using Range.Find method
   lstRow = wks.Column(col).Cells.Find("*", SearchOrder:=xlByRows, LookIn:=xlValues, SearchDirection:=xlPrevious).Row

   FindingLastRow = lstRow

End Function

Note: Please use only one of the above method as it justifies your problem statement.

Please pay attention to the fact that Find method does not see cell formatting but only data, hence look for xlCellTypeLastCell if only data is important and not formatting. Also, merged cells (which must be avoided) might give you unexpected results as it will give you the row number of the first cell and not the last cell in the merged cells.

answered Aug 5, 2017 at 17:23

jainashish's user avatar

jainashishjainashish

4,5225 gold badges36 silver badges48 bronze badges

1

ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.count).row

ActiveSheet can be replaced with WorkSheets(1) or WorkSheets("name here")

answered Jan 29, 2018 at 21:48

18C's user avatar

18C18C

1,96410 silver badges16 bronze badges

here is my code to find the next empty row for example in first row ‘A’.
To use it for any other row just change cells(i,2 or 3 or 4 so on)

Sheets("Sheeet1").Select
   for i=1 to 5000
        if cells(i,1)="" then nextEmpty=i goto 20
   next i
20 'continue your code here 

enter code here

answered Sep 14, 2015 at 8:51

Odiljon Jakhangirov's user avatar

Better:

if cells(i,1)="" then 
nextEmpty=i: 
exit for

Vagish's user avatar

Vagish

2,51018 silver badges32 bronze badges

answered Dec 12, 2017 at 13:43

MarcoP's user avatar

1

Different Ways to Find The Last Row or Last Column Using VBA In Microsoft Excel

Dynamic VBA Code With Last Row/Column

Finding the Last Row or Last Column within your VBA code is key to ensuring your macro doesn’t break in the future.

Early on when I first began writing VBA macro code, I always needed to go back into the code and modify range references.  I had created a bunch of macros to clean up and perform analysis on raw data exported from databases and the data never had the same amount of rows from one data pull to the next.  

My coding skills dramatically changed the day I realized my VBA code could be dynamic and automatically determine the size of my raw data once executed.  I soon came to realize the goal of coding a macro:  to write it once and never touch it again.

Variability is also the greatest challenge for any VBA coder as you have to think of every possible change that could occur in the future.  I have found writing VBA code that can automatically resize itself is one of the greatest things missing from most average macro user’s code.  

In this article, I have compiled a list of the best methods you can use to accomplish finding the last row or column in your data range.

Prep Your Excel Data! 

Keep in mind some of these methods may not give you the desired row or column number if you are not setting your spreadsheet up properly or using a well-formatted block of data.  

What I mean by a “well-formatted block of data”, is a worksheet with data that starts in cell A1 and does not have any blank rows or columns in the middle of the data.  

The below figure illustrates the difference.

An Example of a Poorly-Formatted Data Set

An Example of a Poorly-Formatted Data Set

An Example of a Well-Formatted Data Set

An Example of a Well-Formatted Data Set

In a data set starting in Row 4, you may need to add or subtract a numerical value depending on the method you use.  If you are going to be coding for a data set that has blank rows or columns within it, always be sure to test out your code to make sure it is calculating properly.

Find the Last Cell In Spreadsheet With Data

Finding the last cell with a value in it is key to determining the last row or last column. There are a couple of different ways you can locate the last cell on your spreadsheet. Let’s take a look!

1. The Find Function Method (Best Method)

This line of VBA code will search all the cells on your sheet and return the row of the last cell with any sort of value stored in it. Because the Find function is set to search from the very bottom of the spreadsheet and upwards, this code can accommodate blank rows in your data.

Dim LastCell As Range

Set LastCell = ActiveSheet.Cells.Find(“*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

This method ignores empty cells with formatting still in them, which is ideal if you are truly wanting the find the last cell with data in it, not necessarily the last cell that had any modifications done to it.

2. SpecialCells Method

One of the best manual ways to do this is to utilize the Go To Special dialog box.

The Go To Special dialog box has a variety of actions that can be taken to select certain cells or objections on your spreadsheet. One of those options is to select the Last Cell on the active spreadsheet.

You can get to the Go To Special dialog box by using the keyboard shortcut Ctrl + G which will open the Go To dialog box. From there you can click the Special button and you’ll have arrived at the Go To Special dialog box.

In VBA, the select actions in the Go To Special dialog box are simply called SpecialCells. By calling the SpecialCells function in VBA, you gain the same actions, though they have slightly different names. The particular action you’ll want to call is named xlCellTypeLastCell.

The below VBA code stores the last cell found on the spreadsheet with a value in it to a range variable.

Dim LastCell As Range

Set LastCell = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell)

WARNING! This method could give you unintended results as this finds the last cell with any sort of data or formatting associated with it. This means it can return an empty cell that used to have data in it or simply has any formatting changes (like a yellow cell fill color).

7 Ways To Find The Last Row With VBA

There are actually quite a few ways to determine the last row of a data set in a spreadsheet. We will walk through a number of different ways in this article. I have marked specific methods with a “Best Method” tag as these coding practices are the most bullet-proof ways to determine the last row in your spreadsheet data.

1. The Find Function Method (Best Method)

This line of VBA code will search all the cells on your sheet and return the row of the last cell with any sort of value stored in it. Because the Find function is set to search from the very bottom of the spreadsheet and upwards, this code can accommodate blank rows within your data.

Dim LastRow As Long

LastRow = ActiveSheet.Cells.Find(“*”, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

2. SpecialCells Method

SpecialCells is a function you can call in VBA that will allow you to pinpoint certain cells or a range of cells based on specific criteria. We can use the xlCellTypeLastCell action to find the last cell in the spreadsheet and call for the cell’s row number.

Dim LastRow As Long

LastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

WARNING! This method could give you unintended results as this finds the last cell with any sort of data or formatting associated with it. This means it can return an empty cell that used to have data in it or simply has any formatting changes (like a yellow cell fill color).

3. Ctrl+Shift+End Method

This line of VBA code mimics the keyboard shortcut Ctrl + Shift + End and returns the numerical value of the last row in the range.

Dim LastRow As Long

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, “A”).End(xlUp).Row

4. UsedRange Method

The Used Range is something that Excel stores a reference to behind the scenes. It represents the area of the spreadsheet that has values in it. The Used Range can be referenced in VBA using the UsedRange object.

You must be careful with the Used Range though , as Excel does not always update the reference in real time. Sometimes when you delete cell content the Used Range will not readjust itself right away. For this reason, it is wise to force the UsedRange object to restructure itself with your VBA code. The below VBA code example calls this restructure/refresh prior to utilizing UsedRange to pull the last row.

Dim LastRow As Long

ActiveSheet.UsedRange ‘Refresh UsedRange

LastRow = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row

5. Table Object Method (Best Method)

If you are using a Table Object to store your data, you can use the Table’s name in the below VBA code to return the numerical value of how many rows are in the table.

Dim LastRow As Long

LastRow = ActiveSheet.ListObjects(“Table1”).Range.Rows.Count

6. Named Range Method

If you are using a Named Range to reference your data’s location, you can use the Range name in the below VBA code to return the numerical value of how many rows are in the Named Range.

Dim LastRow As Long

LastRow = ActiveSheet.Range(“MyNamedRange”).Rows.Count

7. Ctrl+Shift+Down Method

Dim LastRow As Long

LastRow = ActiveSheet.Range(“A1”).CurrentRegion.Rows.Count

Expand Your Range To The Last Row

After you have determined the last row, how do you use it? The vast majority of the time you are going to want to store your entire data range to a Range variable. The following code shows you how to incorporate the last row number you calculated into a Range reference.

Dim DataRange As Range

Set DataRange = Range(“A1:M” & LastRow)

7 Ways To Find The Last Column With VBA

There are actually quite a few ways to determine the last column of a data set in a spreadsheet. We will walk through a number of different ways in this article. I have marked specific methods with a “Best Method” tag as these coding practices are the most bullet-proof ways to determine the last column in your spreadsheet data.

1. The Find Function Method (Best Method)

This line of VBA code will search all the cells on your sheet and return the column of the last cell with any sort of value stored in it. Because the Find function is set to search from the very far right of the spreadsheet and then leftward, this code can accommodate blank columns within your data.

Dim LastColumn As Long

LastColumn = ActiveSheet.Cells.Find(“*”, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

2. SpecialCells Method

SpecialCells is a function you can call in VBA that will allow you to pinpoint certain cells or a range of cells based on specific criteria. We can use the xlCellTypeLastCell action to find the last cell in the spreadsheet and call for the cell’s column number.

Dim LastColumn As Long

LastColumn = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Column

WARNING! This method could give you unintended results as this finds the last cell with any sort of data or formatting associated with it. This means it can return an empty cell that used to have data in it or simply has any formatting changes (like a yellow cell fill color).

3. Ctrl+Shift+End Method

Dim LastColumn As Long

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

4. UsedRange Method

Dim LastColumn As Long

ActiveSheet.UsedRange ‘Refresh UsedRange

LastColumn = ActiveSheet.UsedRange.Columns(ActiveSheet.UsedRange.Columns.Count).Column

5. Table Object Method (Best Method)

If you are using a Table Object to store your data, you can use the Table’s name in the below VBA code to return the numerical value of how many columns are in the table.

Dim LastColumn As Long

LastColumn = ActiveSheet.ListObjects(“Table1”).Range.Columns.Count

6. Named Range Method

Dim LastColumn As Long

LastColumn = ActiveSheet.Range(“MyNamedRange”).Columns.Count

7. Ctrl+Shift+Right Method

Dim LastColumn As Long

LastColumn = ActiveSheet.Range(“A1”).CurrentRegion.Columns.Count

How To Expand Your Range To The Last Column

After you have determined the last column, how do you use it? The vast majority of the time you are going to want to store your entire data range to a Range variable. The following code shows you how to incorporate the last column number you calculated into a Range reference.

Dim DataRange As Range

Set DataRange = Range(Cells(1, 1), Cells(100, LastColumn))

VBA Function To Find Last Row or Column

Tim provided the inspiration for a function that can return either the last row or column number through a user-defined function for a given worksheet.

An example of how you could call this function to return the last row on the active worksheet would be written as: x = LastRowColumn(ActiveSheet, “Row”)

Function LastRowColumn(sht As Worksheet, RowColumn As String) As Long
‘PURPOSE: Function To Return the Last Row Or Column Number In the Active Spreadsheet
‘INPUT: “R” or “C” to determine which direction to search

Select Case LCase(Left(RowColumn, 1)) ‘If they put in ‘row’ or column instead of ‘r’ or ‘c’.
  Case “c”
    LastRowColumn = sht.Cells.Find(“*”, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious).Column
  Case “r”
    LastRowColumn = sht.Cells.Find(“*”, LookIn:=xlFormulas, SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
  Case Else
    LastRowColumn = 1
End Select

End Function

What Can I Do With A LastRow Or LastColumn Variable?

There are many things you can do by calculating the last row or last column of a data set.  Examples could be:

  • Resizing a Pivot Table range

  • Looping through cells in a column

  • Deleting only the raw data range

There are many, many more examples of this and I’m sure you can think of a few examples yourself.

Let me know in the comments section below how you use resizing a range in your macro code!  Also, if you can think of any other ways to use VBA code to find the last row or last column, post your coding method in the comments section so we can improve the current list.  I look forward to reading about your experiences.

I Hope This Excel Tutorial Helped!

Hopefully, I was able to explain how you can use VBA code to find the last row or last column of your range to add dynamic capabilities to your macros. If you have any questions about these techniques or suggestions on how to improve this article, please let me know in the comments section below.

About The Author

Hey there! I’m Chris and I run TheSpreadsheetGuru website in my spare time. By day, I’m actually a finance professional who relies on Microsoft Excel quite heavily in the corporate world. I love taking the things I learn in the “real world” and sharing them with everyone here on this site so that you too can become a spreadsheet guru at your company.

Through my years in the corporate world, I’ve been able to pick up on opportunities to make working with Excel better and have built a variety of Excel add-ins, from inserting tickmark symbols to automating copy/pasting from Excel to PowerPoint. If you’d like to keep up to date with the latest Excel news and directly get emailed the most meaningful Excel tips I’ve learned over the years, you can sign up for my free newsletters. I hope I was able to provide you with some value today and hope to see you back here soon! – Chris

Bottom line: Learn how to find the last row, column, or cell in a worksheet using three different VBA methods.  The method used depends on the layout of your data, and if the sheet contains blank cells.

Skill level: Intermediate

VBA Methods to Find the Last Cell Row Column in Excel

Video: 3 Part Series How to Find the Last Cell with VBA

Watch on YouTube & Subscribe to our Channel

Video best viewed in full screen HD

Download the Excel file that contains the code:

Jump to the Code Examples on this page:

  • Range.End VBA Code Example
  • Range.Find Code Example
  • Range.SpecialCells Code Example

Finding the Last Cell is All About the Data

Finding the last used row, column, or cell is one very common task when writing macros and VBA applications.  Like anything in Excel and VBA, there are many different ways to accomplish this.

Choosing the right method mostly depends on what your data looks like.

In this article I explain three different VBA methods of the Range object that we can use to find the last cell in a worksheet.  Each of these methods has pros and cons, and some look scarier than others. 🙂

But understanding how each method works will help you know when to use them, and why.

#1 – The Range.End() Method

The Range.End method is very similar to pressing the Ctrl+Arrow Key keyboard shortcut.  In VBA we can use this method to find the last non-blank cell in a single row or column.

VBA Range.End(xlUp) Method to Find Last Non-Blank Row

Range.End VBA Code Example

Sub Range_End_Method()
'Finds the last non-blank cell in a single row or column

Dim lRow As Long
Dim lCol As Long
    
    'Find the last non-blank cell in column A(1)
    lRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    'Find the last non-blank cell in row 1
    lCol = Cells(1, Columns.Count).End(xlToLeft).Column
    
    MsgBox "Last Row: " & lRow & vbNewLine & _
            "Last Column: " & lCol
  
End Sub

To find the last used row in a column, this technique starts at the last cell in the column and goes up (xlUp) until it finds the first non-blank cell.

The Rows.Count statement returns a count of all the rows in the worksheet.  Therefore, we are basically specifying the last cell in column A of the sheet (cell A1048567), and going up until we find the first non-blank cell.

It works the same with finding the last column.  It starts at the last column in a row, then goes to the left until the last non-blank cell is found in the column.  Columns.Count returns the total number of columns in the sheet.  So we start at the last column and go left.

The argument for the End method specifies which direction to go.  The options are: xlDown, xlUp, xlToLeft, xlToRight.

Pros of Range.End

  • Range.End is simple to use and understand since it works the same way as the Ctrl+Arrow Key shortcuts.
  • Can be used to find the first blank cell, or the last non-blank cell in a single row or column.

Cons of Range.End

  • Range.End only works on a single row or column.  If you have a range of data that contains blanks in the last row or column, then it may be difficult to determine which row or column to perform the method on.
  • If you want to find the last used cell then you have to evaluate at least two statements.  One to find the last row and one to find the last column.  You can then combine these to reference the last cell.

Here are the help articles for Range.End

  • MSDN help page for Range.End
  • MSDN help for xlDirection Enumerations

#2 – The Range.Find() Method

Range.Find VBA Method When Data Contains Blanks

The Range.Find method is my preferred way to find the last row, column, or cell.  It is the most versatile, but also the scariest looking.  🙂

Range.Find Method Arguments VBA

Range.Find has a lot of arguments, but don’t let this scare you.  Once you know what they do you can use Range.Find for a lot of things in VBA.

Range.Find is basically the way to program the Find menu in Excel.  It does the same thing, and most of the arguments of Range.Find are the options on the Find menu.

Range.Find Same As Find Window in Excel

Range.Find Code Example

The following is the code to find the last non-blank row.

Sub Range_Find_Method()
'Finds the last non-blank cell on a sheet/range.

Dim lRow As Long
Dim lCol As Long
    
    lRow = Cells.Find(What:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False).Row
    
    MsgBox "Last Row: " & lRow

End Sub

The Range.Find Method Explained

The Find method is looking for the first non-blank cell (“*”).  The asterisk represents a wildcard character that looks for any text or numbers in the cell.

Starting in cell A1, it moves backwards (xlPrevious) and actually starts it’s search in the very last cell in the worksheet.  It then moves right-to-left (xlByRows) and loops up through each row until it finds a non-blank cell.  When a non-blank is found it stops and returns the row number.

Range.Find Method to Find Last Row Explained - Excel VBA

Here is a detailed explanation for each argument.

  • What:=”*” – The asterisk is a wildcard character that finds any text or number in the cell.  It’s basically the same as searching for a non-blank cell.
  • After:=Range(“A1”) – Start the search after cell A1, the first cell in the sheet.  This means that A1 will NOT be searched.  It will start the search after A1 and the next cell it searches depends on the SearchOrder and SearchDirection.  This argument can be changed to start in a different cell, just remember that the search actually starts in the cell after the one specified.
  • LookAt:=xlPart – This is going to look at any part of the text inside the cell.  The other option is xlWhole, which would try to match the entire cell contents.
  • LookIn:=xlFormulas – This tells Find to look in the formulas, and it is an important argument.  The other option is xlValues, which would only search the values.  If you have formulas that are returning blanks (=IF(A2>5,”Ok”,””) then you might want to consider this a non-blank cell.  Specifying the LookIn as xlFormulas will consider this formula as non-blank, even if the value returned is blank.
  • SearchOrder:=xlByRows – This tells Find to search through each entire row before moving on to the next.  The direction is searches left-to-right or right-to-left depends on the SearchDirection argument.  The other option here is xlByColumns, which is used when finding the last column.
  • SearchDirection:=xlPrevious – This specifies which direction to search.  xlPrevious means it will search from right-to-left or bottom-to-top.  The other option is xlNext, which moves in the opposite direction.
  • MatchCase:=False – This tells Find not to consider upper or lower case letters.  Setting it to True would consider the case.  This argument isn’t necessary for this scenario.

Ok, I know that’s a lot to read, but hopefully you will have a better understanding of how to use these arguments to find anything in a worksheet.

Pros of Range.Find

  • Range.Find searches an entire range for the last non-blank row or column.  It is NOT limited to a single row or column.
  • The last row in a data set can contain blanks and Range.Find will still find the last row.
  • The arguments can be used to search in different directions and for specific values, not just blank cells.

Cons of Range.Find

  • It’s ugly.  The method contains 9 arguments.  Although only one of these arguments (What) is required, you should get in a habit of using at least the first 7 arguments.  Otherwise, the Range.Find method will default to your last used settings in the Find window.  This is important.  If you don’t specify the optional arguments for LookAt, LookIn, and SearchOrder then the Find method will use whatever options you used last in Excel’s Find Window.
  • Finding the last cell requires two statements.  One to find the last row and one to find the last column.  You then have to combine these to find the last cell.

Macro Recorder to the Rescue!

Range.Find is still my preferred method for finding the last cell because of it’s versatility.  But it is a lot to type and remember.  Fortunately, you don’t have to.

You can use the macro recorder to quickly create the code with all the arguments.

  1. Start the macro recorder
  2. Press Ctrl+F
  3. Then press the Find Next button

The code for the Find method with all the arguments will be generated by the macro recorder.

Use a Custom Function for the Find Method

You can also use a custom function (UDF) for the find method.  Ron de Bruin’s Last Function is a perfect example.  You can copy that function into any VBA project or code module, and use it to return the last row, column, or cell.

I also have a similar function in the example workbook.  My function just has additional arguments to reference the worksheet and range to search in.

Here are the help articles for Range.Find

  • MSDN Help Article for Range.Find Method

#3 – Range.SpecialCells(xlCellTypeLastCell)

The SpecialCells method does the same thing as pressing the Ctrl+End keyboard shortcut, and selects the last used cell on the sheet.

VBA Range.SpecialCells Method to Find Last Used Cell in Excel

SpecialCells(xlCellTypeLastCell) Code Example

Sub Range_SpecialCells_Method()

    MsgBox Range("A1").SpecialCells(xlCellTypeLastCell).Address
      
End Sub

It’s actually the easiest way to find the last used cell.  However, this method is finding the last used cell, which can be different than the last non-blank cell.

Often times you will hit Ctrl+End on the keyboard and be taken to some cell way down at the end of the sheet that is definitely not used.  This can occur for a number of reasons.  One common reason is the formatting properties for that cell have been changed.  Simply changing the font size or fill color of a cell will flag it as a used cell.

Pros of Range.SpecialCells

  • You can use this method to find “used” rows and columns at the end of a worksheet and delete them.  Comparing the result of Range.SpecialCells with the result of the Range.Find for non-blanks can allow you to quickly determine if any unused rows or columns exist on the sheet.
  • Deleting unused rows/columns can reduce file size and make your scroll bar bigger.

Cons of Range.SpecialCells

  • Excel only resets the last cell when the workbook is saved.  So if the user or macro deletes the contents of some cells, then this method will not find the true last cell until after the file is saved.
  • It finds the last used cell and NOT the last non-blank cell.

Other Methods for Finding the Last Cell

Well, that should cover the basics of finding the last used or non-blank cell in a worksheet.  If you sheet contains objects (tables, charts, pivot tables, slicers, etc.) then you might need to use other methods to find the last cell.  I will explain those techniques in a separate post.

I also have an article on how to find the FIRST used cell in the worksheet.

Please leave a comment below if you have any questions, or are still having trouble finding the last cell.  I’ll be happy to help! 🙂

Хитрости »

1 Май 2011              406605 просмотров


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

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

Одинаковые переменные для всех примеров
Во всех примерах ниже мы будем запоминать номер последней строки или столбца в одни и те же переменные:

    Dim lLastRow As Long
    'а для lLastCol можно было бы применить и тип Integer,
    'т.к. столбцов в Excel пока меньше 32767, но для однообразности назначим тоже Long
    Dim lLastCol As Long
  • Определение последней ячейки через свойство End
  • Определение последней ячейки через SpecialCells
  • Определение последней ячейки через UsedRange
  • Определение последней ячейки через метод Find
  • Несколько практических кодов

  • Способ 1:

    Определение

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

    через свойство End

        lLastRow = Cells(Rows.Count,1).End(xlUp).Row
        'или
        lLastRow = Cells(Rows.Count, "A").End(xlUp).Row

    1 или “A” – это номер или имя столбца, последнюю заполненную ячейку в котором мы определяем. По сути обе приведенные строки дадут абсолютно одинаковый результат. Просто иногда удобнее указать номер столбца, а иногда его имя. Поэтому использовать можно любой из приведенных вариантов, в зависимости от ситуации.
    Определение последнего столбца через свойство End

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

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

    Данный метод определения последней строки/столбца самый распространенный. Используя его мы можем определить последнюю ячейку только в одном конкретном столбце(или строке). В большинстве случаев этого более чем достаточно.

    Метод основан именно на принципе работы свойства End. На примере поиска последней строки опишу принцип так, как бы мы это делали руками через выделение ячеек на листе:

    • выделили самую последнюю ячейку столбца А на листе(для Excel 2007 и выше это А1048576, а для Excel 2003 – А65536)
    • и выполнили переход вверх комбинацией клавиш Ctrl+стрелка вверх. Данная комбинация заставляет Excel двигаться вверх(если точнее, то в направлении стрелки, нажатой вместе с Ctrl) до тех пор, пока не встретиться первая ячейка с формулой или значением. А в случае, если сочетание было вызвано из уже заполненных ячеек – то до первой пустой. И как только Excel доходит до этой ячейки – он её выделяет
    • А через свойство .Row мы просто получаем номер строки этой выделенной ячейки

    Нюансы:

    • даже если в ячейке нет видимого значения, но есть формула – End посчитает ячейку не пустой. С одной стороны вполне справедливо. Но иногда нам надо определить именно “визуально” заполненные ячейки. Поиск ячеек при подобных условиях будет описан ниже(Способ 4: Определение последней ячейки через метод Find)
    • если на листе заполнены все строки в просматриваемом столбце(или будут заполнены несколько последних ячеек столбца или даже только одна последняя) – то результат может быть неверный(ну или не совсем такой, какой ожидали)
    • Данный способ игнорирует строки, скрытые фильтром, группировкой или командой Скрыть (Hide). Т.е. если последняя строка таблицы будет скрыта, то данный метод вернет номер последней видимой заполненной строки, а не последней реально заполненной.

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


    Способ 2:

    Определение

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

    через SpecialCells

        lLastRow = Cells.SpecialCells(xlLastCell).Row

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

        lLastCol = Cells.SpecialCells(xlLastCell).Column

    Данный метод не требует указания номера столбца и возвращает последнюю ячейку(Row – строку, Column – столбец).
    Если хотите получить номер первой пустой строки или столбца на листе – к результату необходимо прибавить 1.
    Нюансы:

    • Используя данный способ следует помнить, что не всегда можно получить реальную последнюю заполненную ячейку, т.е. именно ячейку со значением. Метод SpecialCells определяет самую “дальнюю” ячейку на листе, используя при этом механизм “запоминания” тех ячеек, в которых мы работали в данном листе. Т.е. если мы занесем в ячейку AZ90345 значение и сразу удалим его – lLastRow, полученная через SpecialCells будет равна значению именно этой ячейки, из которой вы только что удалили значения(т.е. 90345). Другими словами требует обязательного обновления данных, а этого можно добиться только сохранив файла, а временами даже только закрыв файл и открыв его снова. Так же, если какая-либо ячейка содержит форматирование(например, заливку), но не содержит никаких значений, то метод SpecialCells посчитает её используемой и будет учитывать как заполненную.
      Этот недостаток можно попробовать обойти, вызвав перед определением последней ячейки вот такую строку кода:

      With ActiveSheet.UsedRange: End With

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

      Sub GetLastCell()
          Dim lLastRow As Long
          'переопределяем рабочий диапазон листа
          With ActiveSheet.UsedRange: End With
          'ищем последнюю заполненную ячейку на листе
          lLastRow = Cells.SpecialCells(xlLastCell).Row
      End Sub
    • даже если в ячейке нет видимого значения, но есть формула – SpecialCells посчитает ячейку не пустой
    • Данный метод определения последней ячейки не будет работать на защищенном листе(Рецензирование(Review)Защитить лист(Protect Sheet)).
    • Данный метод не будет работать при использовании внутри UDF. Точнее будет работать не так, как ожидается. Подробнее про некоторые “баги” работы встроенных методов внутри UDF(функций пользователя) я описывал в этой статье: Глюк работы в UDF методов SpecialCells и FindNext

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


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

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

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

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

    НЕМНОГО ПОЯСНЕНИЙ:

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

    Нюансы:

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

    Однако метод через UsedRange.Row работает прекрасно и при установленной на лист защите и внутри UDF, что делает его более предпочтительным, чем метод через SpecialCells при равных условиях.


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

    Sub GetLastCell_Find()
        Dim rF As Range
        Dim lLastRow As Long, lLastCol As Long
        'ищем последнюю ячейку на листе, в которой хранится хоть какое-то значение
        Set rF = ActiveSheet.UsedRange.Find(What:="*", LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False)
        If Not rF Is Nothing Then
            lLastRow = rF.Row    'последняя заполненная строка
            lLastCol = rF.Column 'последний заполненный столбец
            MsgBox rF.Address 'показываем сообщение с адресом последней ячейки
        Else
            'если ничего не нашлось - значит лист пустой
            'и можно назначить в качестве последних первую строку и столбец
            lLastRow = 1
            lLastCol = 1
            MsgBox "A1" 'показываем сообщение с адресом ячейки А1
        End If
    End Sub

    Этот метод, пожалуй, самый оптимальный в случае, если надо определить последнюю строку/столбец на листе без учета форматов и формул – только по отображаемому значению в ячейке. Например, если на листе большая таблица и последние строки заполнены формулами, возвращающими при определенных условиях пустую ячейку(=ЕСЛИ(A1>0;1;””)), предыдущие варианты вернут строку/столбец ячейки с последней ячейкой, в которой формула. В то время как данный метод вернет адрес ячейки только в случае, если в ячейке реально отображается какое-то значение. Такой подход часто используется для того, чтобы определить границы данных для последующего анализа заполненных данных, чтобы не захватывать пустые ячейки с формулами и не тратить время на их проверку.
    Здесь следует обратить внимание на параметры метода Find. В данном случае мы специально указываем искать по значениям, а не по формулам:
    Set rF = ActiveSheet.UsedRange.Find(What:=”*”, LookIn:=xlValues, LookAt:=xlWhole, SearchDirection:=xlPrevious, MatchCase:=False, MatchByte:=False)
    Нюансы:

    • Метод Find, вызванный с листа или другим кодом, имеет свойство запоминать все параметры последнего поиска, а если поиск еще не вызывался – то применяются параметры по умолчанию. А по умолчанию поиск идет всегда по формулам. Поэтому я настоятельно рекомендую указывать принудительно все необходимые параметры, как в примере.
    • Метод Find не будет учитывать в просмотре скрытые строки и столбцы. Это следует учитывать при его применении.

    Пара небольших практических кодов

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

    Sub GetLastCell()
        Dim lLastRow As Long
        Dim lLastCol As Long
        'определили последнюю заполненную ячейку с учетом формул в столбце А
        lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
        MsgBox "Заполненные ячейки в столбце А: " & Range("A1:A" & lLastRow).Address
        'определили последний заполненный столбец на листе(с учетом формул и форматирования)
        lLastCol = Cells.SpecialCells(xlLastCell).Column
        MsgBox "Заполненные ячейки в первой строке: " & Range(Cells(1, 1), Cells(1, lLastCol)).Address
        'выводим сообщение с адресом последней ячейки на листе(с учетом формул и форматирования)
        MsgBox "Адрес последней ячейки диапазона на листе: " & Cells.SpecialCells(xlLastCell).Address
    End Sub

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

    Sub SelectToLastCell()
        Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row).Select
    End Sub

    Копируем ячейку B1 в первую пустую ячейку столбца A этого же листа:

    Sub CopyToFstEmptyCell()
        Dim lLastRow As Long
        lLastRow = Cells(Rows.Count, 1).End(xlUp).Row 'определили последнюю заполненную ячейку
        Range("B1").Copy Cells(lLastRow+1, 1)         'скопировали В1 и вставили в следующую после определенной ячейки
    End Sub

    А код ниже делает тоже самое, но одной строкой – применяется Offset и используется тот факт, что изначально методом End мы получаем именно ячейку, а не номер строки(номер строки мы получаем позже через свойство .Row):

    Sub CopyToFstEmptyCell()
        Range("B1").Copy Destination:=Cells(Rows.Count, 1).End(xlUp).Offset(1)
    End Sub

    Range(“B1”).Copy – копирует ячейку В1. Если для аргумента Destination указать другую ячейку, то в неё будет вставлена скопированная ячейка. Мы передаем в этот аргумент определенную методом End ячейку
    Cells(Rows.Count, 1).End(xlUp) – возвращает последнюю заполненную ячейку в столбце А (не строку, а именно ячейку)
    Offset(1) – смещает полученную ячейку на строку вниз
    Используем инструмент автозаполнение(протягивание) столбца В, начиная с ячейки B2 и определяя последнюю ячейку для заполнения на основании столбца А

    Sub AutoFill_B()
        Dim lLastRow As Long
        lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
        Range("B2").AutoFill Destination:=Range("B2:B" & lLastRow)
    End Sub

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

    Так же см.:
    Как получить последнюю заполненную ячейку формулой?
    Как определить первую заполненную ячейку на листе?
    Что такое переменная и как правильно её объявить?


    Статья помогла? Поделись ссылкой с друзьями!

      Плейлист   Видеоуроки


    Поиск по меткам

    

    Access
    apple watch
    Multex
    Power Query и Power BI
    VBA управление кодами
    Бесплатные надстройки
    Дата и время
    Записки
    ИП
    Надстройки
    Печать
    Политика Конфиденциальности
    Почта
    Программы
    Работа с приложениями
    Разработка приложений
    Росстат
    Тренинги и вебинары
    Финансовые
    Форматирование
    Функции Excel
    акции MulTEx
    ссылки
    статистика

    Добавить комментарий