VBA basics
18. Get last row (End, Rows.Count)

Excel macro basics and applications, introduction to Excel VBA
last updated:08-16-2019

18. Get last row (End, Rows.Count)

Some of this topic was processed by machine translation. Original


Getting the last row of a table in an Excel worksheet is an essential technique for macro VBA.


Necessity of obtaining the last row in Excel VBA

Excel is a spreadsheet software, that is, it handles vertical and horizontal tables.
It is common for the data to have items on the side and data for the items on the side.
But,
The number of data lines is usually not a fixed number of lines.
Data is increasing or decreasing.
And
In a macro, it is normal to process all rows of a table.
But,
To process all the rows in the table, if you don't know the last row of data,
Every time you have to change the number on the last line, you can't do that.
Then
How do you get the last line?

The following table explains.

VBA macro excel

In the macro, you want to process from A2 to A11.
And
Even if this number of lines increases, I want to be able to use the macro without changing it.
there,
It means that we want to get the last number of lines that contain data automatically with macro VBA.

.End(xlDown):Ctrl+↓

In the first place, what kind of operation are you doing when moving to the bottom of the table data by hand?

To move manually to the last row of column A,
With A1 cell selected,
Ctrl + ↓
You can now move to A11.


In macro VBA,
Cells(1, 1).End(xlDown)
Write like this.

However, if there is a blank cell in the middle,

VBA macro excel

In this case, Ctrl + ↓ will result in cell A6.
This is inconvenient.

.End(xlUp):Ctrl+↑

If there is a blank in the middle of the table,
Once you move to column A (A1048576) cell at the bottom of the sheet (last row of the sheet)
Ctrl + ↑
Then you can move to A11.

In macro VBA,
Cells (Rows.Count, 1) .End (xlUp)
Write like this.
Rows.Count
Rows.Count is the number of rows in the sheet.
If it is 2007 or later, it is 1048576, if it is 2003, 65536.
Rows are all the rows of the sheet, so it is Count.
That means
From the last row of the A column of the sheet, it is moving upward with Ctrl + ↑.

This End is a property of the Range object and returns a Range object.
*This expression is very difficult to understand, and is explained at the end of this page.

End property direction (↑ ↓ ← →)

The syntax of the End property is
End (Direction)

What you specify for Direction is
xlUp : ↑Up
xlDown : ↓Down
xlToLeft : ←To the left
xlToRight : →To the right

Row property that gets the number of rows in a cell

As obtained with the End property,
Cells(1, 1).End(xlDown)
Or
Cells(Rows.Count, 1).End(xlUp)
These represent the last cell (the cell itself, not the number of rows).
In other words, it becomes the Range object of the last cell.

In other words, the cell value is obtained instead of the number of rows.

To get the number of rows (row position) of a cell,

Cells(1, 1).End(xlDown).Row
Or
Cells(Rows.Count, 1).End(xlUp).Row
In this way, specify .Row.
Now you can get the last number of rows of data.

This .Row is a property of the Range object and returns its row position.
In the above example, 11 is returned.
What is returned is that
Cells (1, 1) .End (xlDown) .Row is the same as 11.

To get the last line,
First
Cells(Rows.Count, 1).End(xlUp).Row
Or
Cells(1, 1).End(xlDown).Row
Please memorize this as a standard sentence.

Translate Cells(Rows.Count, 1) .End(xlUp) .Row into everyday words

Cells(Rows.Count, 1).End(xlUp).Row
Number of rows in cellsARows.Count from Ctrll +

Since this VBA code is long, you can use what you wrote once with copy and paste,
Remember to type in the first few times.
By doing so, you will be able to become familiar with VBA.
This sentence contains many properties and will definitely be useful for future learning.

End property returns a Range object

This word is very difficult to understand.
It doesn't have to be understood now.
Detailed explanation will be explained little by little in the future.

Properties are object attributes
I have explained this.

That property returns an object,
I think it ’s a little difficult to understand.

But to explain this in detail, you have to explain so many things.

Now, properties are those that set and get values,
Just know that there are properties that return objects.
End is a pume property that returns a Range object.

Page in the site for the last line



Articles with the same theme "VBA basics"

15. Four arithmetic operations and annotations (comments)
16. Iterative processing (For Next)
17. Iterative processing (Do Loop)
18. Get last row (End, Rows.Count)
19. General practice question 1
20. Conditional branch (If)
21. Conditional branch (ElseIf)
22. Conditional branch (Select Case)
23. Message box (MsgBox function)
24. Input box (InputBox function)
25. About named arguments



  • >
  • >
  • 18. Get last row (End, Rows.Count)

  • If you find this site useful, please share and bookmark.


    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.




    If you find this site useful, please share and bookmark.
    To the bottom of the text