Open In App

Trapping Dynamic Ranges in Excel VBA

Improve
Improve
Like Article
Like
Save
Share
Report

Dynamic Ranges give the flexibility to work with more range of cells as compared to a specific range of cells which usually limits us to work with a particular range of cells. As the size of the data may change in an excel sheet so it is also necessary to change the range in the code dynamically for that dynamic ranges are used. We can trap dynamic ranges in different ways:

  • Last Row in Column
  • Last Column in Row 
  • Special cells 
  • Used Range
  • Current Region
  • Named Region 
  • Tables

Last Row in Column

This is used to get the number of the last row in the column by using two methods i.e Range() and End(). Range(“x1048576”) is the syntax where x represents the name of the cell for which we are trapping the last row and 1048576 is the total number of rows in the sheet. End(Direction as xlDirection) where we are searching for the last row we will mention xlUp to go in the last row of the worksheet. The following code is written to get the number of the last row in cell “C”

Last-row-in-column

 

Last Column in Row

This is used to get the number of the last column in the row by using two methods i.e Range() and End(). Range(“XFDz”) is the syntax where XFD is the last column of the worksheet and z represents the number of the row. End(Direction as xlDirection) where we have to mention whether to search for left as xlToLeft or right as xlToRight. The following code is written to get the number of the last column on the left side of the row “3”.

Last-column-in-row

 

SpecialCells – LastCell

Without using the End method we can also get the number of the last row or column with the help of the SpecialCells method.

specialcells-method

 

UsedRange

This method is used to get the range of the cell which has values present in the current worksheet

Range-of-the-cell-obtained

 

CurrentRegion 

It will look around a respective cell and then it will return the range of the cell up to where it will find no blank rows or columns.

Currentregion-code

 

Named Range

It is used to insert or update the data in the range which can be identified by the name of the range

Sample of data

Data-sample

 

The following code is to change the font color of cell A2:A4 to red which is identified by its name of the range “Name”.

Code-to-change-font-color

 

Tables

The table range will refer to the entire rows, columns, and headings present in the table which we can use to manipulate in the worksheet.

Sample of data

Data-sample

 

The following code is to delete the column with the name “Marks” in Table1.

code-to-delete-column

 


Last Updated : 29 Oct, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads