Open In App

How to Find the Last Used Row and Column in Excel VBA?

Last Updated: 09 Nov, 2021

A

Asha Senthilkumar
Summarize
Comments
Improve
Suggest changes
Like Article
Like
Share
Report
News Follow

We use  Range.SpecialCells() method in the below VBA Code to find and return details of last used row, column, and cell in a worksheet.

Sample Data:

Sample Data

Syntax:

expression.SpecialCells (Type, Value)

Eg: To return the last used cell address in an activesheet. ActiveSheet.Range(“A1”).SpecialCells(xlCellTypeLastCell).Address

VBA Code:

Declaring Variables:

Variable Data Type Comments
LastRow Long Find and store last used row
LastCol Long store last used column
LastCell String store last used cell address 
'Variable Declaration

Dim LastRow As Long, LastCol As Long, LastCell As String

Use SpecialCells function to find last used row/column/cell 

'Find Last Used Row
LastRow = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Row

'Find Last Used Column
LastCol = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Column

'Find Last Used Cell
LastCell = ActiveSheet.Range("A1").SpecialCells(xlCellTypeLastCell).Address

Concatenate all three variables (LastRow/LastCol/LastCell), add a new line between variables use Chr(10).   Show the final output in an Excel Message box.

'Display the last used row/column/cell

MsgBox "Last Used Row : " & LastRow & Chr(10) & "Last Used Column : " & LastCol & Chr(10) & "Last Used Cell : " & LastCell

Implementation:

Follow the below steps to find the Last Used Row and Last Used Column in Excel VBA

Step 1: Add a shape (Find Last Row/Column/Cell) on your worksheet.  

Step 2: Right-click on “Find Last Row/Column/Cell” and “Assign Macro..”

Step 3: Select “findLastUsedCell”, you can see a list of macros if available in your workbook

Step 4: Save your excel file as “Excel Macro-Enabled Workbook” *.xlsm

Step 5: Click “Find Last Row/Column/Cell” to execute the VBA code.  Code will popup below output for the above given example (Output).

Output:



Article Tags :

A

Asha Senthilkumar
News
Improve
Discuss
Do you want to advertise with us?Click here to know more

A

Ayush Dey

VBA Subroutine in Excel - How to Call Sub in VBA?

article_img
When a specified action is performed on a worksheet with the help of a collection of code known as a VBA Subroutine. It also helps to read an external file, also it can open other applications from Excel. A large piece of code can be broken into small parts so that we can manage it easily. Let's learn why to use submarines: Converts large piece of codes into small parts so that the computer ignores all kind of complexities that arises because of large codesReusability of code suppose we in a program have to access the database frequently so instead of writing the code again and again we can create a function to access the databaseSubroutines are self-documenting functions which means a coder can easily say what the program does by looking into the name of the function Naming Rules of SubroutinesIt can start with a letter or an underscore but it cannot start with a number or a special character.It cannot contain any space in the name.The name of the subroutine cannot be a keyword like Private, Sub, End, etc. Syntax Private Sub function_name( ByVal arg1 As String, ByVal arg2 As String) End Sub Syntax Explanation Code Action "Private Sub function_name(...)"Private is the keyword whic
Read More

A

Ayush Dey

VBA Find Function in Excel

article_img
In an Excel sheet subset of cells represents the VBA Range which can be single cells or multiple cells. The find function will help to modify our search within its Range object. A specific value in the given range of cells is to search with the help of the Find function. Excel VBA provides different parameters in the Find function so that we can search according to the search order or direction and also we can make a case-sensitive search which will be discussed further in this article. Excel VBA Find FunctionAs we know about the Find Function in Excel, we can use the Shortcut key "Ctrl+F" to find the content that you are looking for. The FIND function of VBA searches for a specified value in the range that is defined by the user. A VBA code is written by entering arguments of the FIND function. Below is the syntax given for using the find function in VBA, Syntax: Find(What, [After], [LookIn], [LookAt], [SearchOrder], [SearchDirection AsXlSearchDirection = xlNext], [MatchCase], [MatchByte], [SearchFormat]) As Range Parameter Required Description WhatRequiredThe value for which we are searchingAfterOptionalRange of cells from where the search will startLookinOptionalFunction search
Read More

A

Ankur Pal

Variables and Data Types in VBA Excel

article_img
In a computer system, variables and data types are almost used in every program to store and represent data. Similarly, Excel VBA also has variables and data types to store and represent data and its type. In this article, we will learn about VBA variables, their scope, data types, and much more. VBA Variables VBA(Visual Basic for Application) variables are similar to other programming languages variables, they act as a container that is used to store data(integer, string, floats, etc). We can use the variables in the code at multiple places and executer the programs. Defining Variables In VBA VBA gives permission to define variables in two ways: Implicitly - In VBA, we can implicitly declare variables using the assignment(=) operator. All the variables that are implicitly declared in VBA are of type "Variant". The variant type variables required more memory space than usual variables. Example: label="gfg"Explicitly - Explicitly we can declare variables using "Dim" keyword. Explicit variable also reduces the naming conflicts and spelling mistakes. Example: Num as password Syntax For VBA Variables // macro definition Sub VBA_Variable_Example () Dim <name> End Sub VBA V
Read More

R

RISHAB NIGAM

Function and Sub in Excel VBA

article_img
In Visual Basic, the functions and sub-procedures play similar roles but have different or unique characteristics. However, both perform a programmed task. They utilize a set or group of commands to deliver the required results. The key difference between the sub and the functions is that a sub-procedure generally does not return a result whereas functions tend to return a result. Hence if there is a need for having a value post execution of tasks, then place the VBA code under a function or otherwise place the code under a sub-procedure. In Excel, there is the availability of large numbers of VBA functions that could be utilized in the development of new VBA codes. Such functions are referred to as Built-in functions. With the increase in the size of a VBA program, both Functions and Sub-procedures play a crucial role in the management and performance of VBA code. Functions in VBA A function in VBA can be defined as a procedure that executes a piece of code or instructions and post-execution, it returns the value of the tasks performed. A function is hence invoked using a variable. Functions are directly called in the spreadsheets by using excel based formulas. An excel VBA functi
Read More

A

Ayush Dey

VBA Date and Time Functions in Excel

article_img
Date and Time Functions are the inbuilt functions that give us the opportunity to see the date or time according to the user's need. Suppose a user needs to see the month or the day or the year then it can be easily seen by different date functions. Similarly, for the time function, also we can manipulate it according to the need of the user. Date and Time functions are used to interconvert date and time in different formats. In this article, we will learn about the most commonly used date and time functions. VBA Date Functions There are fifteen-plus different date functions in VBA, but here we will talk about some of the most commonly used date functions. VBA Date Function The Date() function returns the current date. The Date() function does not require any arguments. For example, declare a variable name date_1 of Date data type, call the Date() function, and store the return value in date_1, then print the date_1 in the console. Syntax of the function: Date() VBA DateAdd Function The DateAdd() function is used to add an interval of date/time to the respective date or time. The function will return the resulting date or time. The function takes three arguments, Interval, Nu
Read More

A

Ayush Dey

How to Declare and Initialize String Array in Excel VBA?

article_img
A string array is an array where we can store only string values in the array, with the help of a string array, we can store more than one string value. We can declare the string array in many ways like declaring a static string array, declaring a variant size array of string using the Array function, and a string array using the split function which we will discuss in this article Declaring a Static String Array A static Array is an array whose size is fixed and it can be declared in two ways one is declared implicitly and another one is explicit. The following code is to declare a string array implicitly. The following code is to declare a string array explicitly. Declaring a Variant Size Array of String using Array Function In the following code an array is declared with variant size and string values are initialized using the array function: If we want to access the strings in the array then we have to write, Declaring a String Array using Split Function The following code is to declare an array without any fixed size and a split function is used to assign the string values. If we want to access the strings in the array then we have to write, By default, the lower bound
Read More

A

Ayush Dey

How to Insert and Run VBA Code in Excel?

article_img
In Excel VBA stands for (Visual Basic for Application Code) where we can automate our task with help of codes and codes that will manipulate(like inserting, creating, or deleting a row, column, or graph) the data in a worksheet or workbook. With the help of VBA, we can also automate the task in excel to perform all these tasks we need to insert and run the VBA code properly which we will discuss in this article. Steps to Insert and Run VBA Code in Excel To use the VBA code properly in Excel we need to change the default macro security settings of excel for that we need to follow further steps Step 1: Click on the "File" menu at the left top of the excel tab. Step 2: Select "Options" to get the "Excel Options" window. Step 3: Select "Customized Ribbon" in the "Excel Options" Window and then select the "Developer" check box in the "Main Tabs". Step 4: Then return to the main Excel window to select the "Developer" ribbon and then click on "Macro Security" in the "Code" group. Step 5: Click on "Macro Settings" to select "Disable all macros except digitally signed macros". Now, to insert and run the VBA in Excel so that we can write codes we need to follow further steps: Ste
Read More

S

SOUNETRA GHOSAL

How to Create Charts in Excel Using Worksheet Data and VBA?

article_img
Excel is an important software provided by Microsoft Corporation. This software belongs to one of the major software suites Office 365. In this software suite, there are other software are present like Word, PowerPoint, etc. They are called Office 365, as this software are mostly used for office purpose. But now the world has changed a lot. After the Corona Pandemic, the world knows the positivity of using digital tools. Office 365 was not different from that. As a part of the software suite, Excel software also gains some importance from the users. They are not only used for official purposes. But they can also be used for school purposes. Excel is software that can able to store data in an effective form. So, searching for the data becomes more manageable in this software. Excel has another great feature. It can be used to derive the charts from the provided data. The charts are helpful for analyzing any growth of the data. If there are thousands of data present, it is a difficult task to extract some analysis from that data. But if those data are converted to charts, then it will be easy to analyze those data. Excel sheet helps to do the same. Charts can be prepared whatever the
Read More

A

Ankur Pal

Workbook and Worksheet Object in Excel VBA

article_img
Excel VBA is an object-oriented programming language, which means in excel every component is an object. The superset of all the objects of excel is the excel application itself. The Excel Application Object is the root of all the other objects in excel. It contains Workbook Object. The Workbook Object contains another object such as Worksheet Object which includes all the worksheets present in a particular workbook. The Worksheet Object contains Cells Object. To perform any task or operation in excel we need to follow this excel object hierarchy. Workbooks Object In excel VBA, the workbooks object represents the collection of the workbook which are open in the excel application at the same time. This is the reason we write it as plural- Workbooks Object, which means it contains more than one workbook. In order to access a single workbook from the workbooks object, we can use the following VBA to access it from its name. How to Refer a Workbook in VBA? To refer a workbook in excel VBA, we can use the following different methods: By Name We can easily refer to a workbook using its name. We need to define a procedure in VBA and declare our excel workbook name. Make sure that the user
Read More

R

Rishabh Chakraborty

How to Use Select Case Statement in Excel VBA?

article_img
VBA in Excel stands for Visual Basic for Applications which is Microsoft's programming language. To optimize the performance and reduce the time in Excel we need Macros and VBA is the tool used in the backend. In this article, we are going to discuss how to use Select Case Statement in Excel VBA. Select Case Statement of Excel VBA The select case in VBA is like a detective that investigates an expression by checking it against different scenarios listed as Case Statements, each with its own conditions. When a scenario matches the expression, it's like solving a piece of the puzzle, and the corresponding code linked to that scenario is activated. Importantly, once a match is found, the detective stops investigating and moves on to executing the discovered code. Yet, if none of the scenarios match, it's like the detective hitting a dead end. In this situation, the code associated with the Case Else statement comes into play, offering a default solution or outcome. Syntax of the Select Case Statement of Excel VBAThe VBA Select Case Statement shares similarities with the Switch Case construct found in programming languages such as Java, C#, and PHP. In Excel VBA, this statement helps
Read More
three90RightbarBannerImg
We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox