Tag Archives: range

vba last row featured

Excel VBA Last Row, Last Column, Last Cell

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 3.67 out of 5)
Loading...

Today I am going to take on one of the most frequent question people ask about Excel VBA – how to the the last row, column or cell of a spreadsheet using VBA. The Worksheet range used by Excel is not often the same as the Excel last row and column with values. Therefore I will be careful to explain the differences and nuisances in our quest to find the last row, column or cell in and Excel spreadsheet.

CONTENTS

VBA Last Row

The Last Row may as be interpreted as:

Last Row in a Column

vba last row
Last Row in a Column

To get the Last Row with data in a Column we need to use the End property of an Excel VBA Range.

Dim lastRow as Range

'Get Last Row with Data in Column
Debug.Print Range("A1").End(xlDown).Row 'Result: 5
Set lastRow = Range("A1").End(xlDown).EntireRow

'Get Last Cell with Data in Row
Dim lastRow as Range
Set lastRow = Range("A1").End(xlDown)

Last Row with Data in Worksheet

vba last used row
Last Used Row with Data in Worksheet

To get the Last Row with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastRow as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Row with Data in Worksheet using SpecialCells
Debug.Print ws.Cells.SpecialCells(xlCellTypeLastCell).Row
Set lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireRow 

'Get Last Row with Data in Worksheet using Find
Debug.Print Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Row
Set lastRow = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).EntireRow

Last Row in Worksheet UsedRange

vba last used row usedrange
Last Used Row in Worksheet UsedRange

To get the Last Row in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Row in Worksheet UsedRange
Dim lastRow as Range, ws As Worksheet
Set ws = ActiveSheet
Debug.Print ws.UsedRange.Rows(ws.UsedRange.Rows.Count).Row
Set lastRow = ws.UsedRange.Rows(ws.UsedRange.Rows.Count).EntireRow
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA Last Column

The Last Column may as be interpreted as:

Last Column with Data in a Row

vba last column
Last Column in Row

To get the Last Column with data in a Row we need to use the End property of an Excel VBA Range.

Dim lastColumn as Range

'Get Last Column with Data in Row
Debug.Print Range("A1").End(xlRight).Row 'Result: 5
Set lastColumn = Range("A1").End(xlRight).EntireColumn

'Get Last Cell with Data in Column
Dim lastColumn as Range
Set lastColumn = Range("A1").End(xlRight)

Last Column with Data in Worksheet

vba last used column
Last Column with Data in a Worksheet

To get the Last Column with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastColumn as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Column with Data in Worksheet using SpecialCells
Debug.Print ws.Cells.SpecialCells(xlCellTypeLastCell).Column
Set lastColumn = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireColumn 

'Get Last Column with Data in Worksheet using Find
Debug.Print Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).Column
Set lastColumn = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False).EntireColumn

Last Column in Worksheet UsedRange

vba last used column usedrange
Last Used Column in Worksheet UsedRange

To get the Last Column in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Column in Worksheet UsedRange
Dim lastColumn as Range, ws As Worksheet
Set ws = ActiveSheet
Debug.Print ws.UsedRange.Columns(ws.UsedRange.Columns.Count).Column
Set lastColumn = ws.UsedRange.Columns(ws.UsedRange.Columns.Count).EntireColumn
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA Last Cell

The Last Cell may as be interpreted as:

Last Cell in a series of data

vba last cell in data series
Last Cell in data series

To get the Last Cell in a series of data (table with non-blank values) we need to use the End property of an Excel VBA Range.

Dim lastCell as Range

'Get Last Cell in a series of data
Dim lastCell as Range
Set lastCell = Range("A1").End(xlRight).End(xlDown)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

Last Cells with Data in Worksheet

vba last cell in worksheet
Last Cell with data in Worksheet

To get the Last Cell with data in a Worksheet we need to use the SpecialCells or Find properties of an Excel VBA Range.

Dim lastCell as Range, ws As Worksheet
Set ws = ActiveSheet

'Get Last Cell with Data in Worksheet using SpecialCells
Set lastCell = ws.Cells.SpecialCells(xlCellTypeLastCell)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

'Get Last Cell with Data in Worksheet using Find
Set lastColumn = Debug.Print ws.Cells.Find(What:="*", _
        After:=ws.Cells(1), _
        Lookat:=xlPart, _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlPrevious, _
        MatchCase:=False)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column

Last Cell in Worksheet UsedRange

vba last cell in usedrange
Last Cell in UsedRange

To get the Last Cell in the Worksheet UsedRange we need to use the UsedRange property of an VBA Worksheet.

'Get Last Cell in Worksheet UsedRange
Dim lastCell as Range, ws As Worksheet
Set ws = ActiveSheet

Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count,ws.UsedRange.Columns.Count)
Debug.Print "Row: " & lastCell.row & ", Column: " & lastCell.column
The UsedRange represents a Range used by an Excel Worksheet. The Used Range starts at the first used cell and ends with the most right, down cell that is used by Excel. This last cell does not need to have any values or formulas as long as it was edited or formatted in any point in time

VBA UsedRange

vba usedrange
The UsedRange in a Worksheet with not Values or Formulas

The VBA UsedRange represents the area reserved and saved by Excel as the currently used Range on and Excel Worksheet. The UsedRange constantly expands the moment you modify in any way a cell outside of the previously Used Range of your Worksheet.

The UsedRange is not reduced if you Clear the Contents of Range. The only way to reduce a UsedRange is to delete the unused rows and columns.

How to check the UsedRange

The easiest way to check the currently UsedRange in an Excel Worksheet is to select a cell (best A1) and hitting the following key combination: CTRL+SHIFT+END. The highlighted Range starts at the cell you selected and ends with the last cell in the current UsedRange.

Often bloated, large Excel files are effects of misuse of UsedRange. If you want to know more about reducing Excel file size read my post

Check UsedRange in VBA

Use the code below to check the area of the UsedRange in VBA:

Dim lastCell As Range, firstCell As Range, ws As Worksheet
Set ws = ActiveSheet
    
Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count)
Set firstCell = ws.UsedRange.Cells(1, 1)
Debug.Print "First Cell in UsedRange. Row: " & firstCell.Row & ", Column: " & firstCell.Column
Debug.Print "Last Cell in UsedRange. Row: " & lastCell.Row & ", Column: " & lastCell.Column    

For the screen above the result will be:

First Cell in UsedRange; Row: 2, Column: 2
Last Cell in UsedRange; Row: 5, Column: 6

First UsedCell in UsedRange

The below will return get the first cell of the VBA UsedRange and print its row and column:

Dim firstCell as Range
Set firstCell = ws.UsedRange.Cells(1, 1)
Debug.Print "First Cell in UsedRange. Row: " & firstCell.Row & ", Column: " & firstCell.Column

Last UsedCell in UsedRange

The below will return get the first cell of the VBA UsedRange and print its row and column:

Dim lastCell as Range
Set lastCell = ws.UsedRange.Cells(ws.UsedRange.Rows.Count, ws.UsedRange.Columns.Count)
Debug.Print "Last Cell in UsedRange; Row: " & lastCell.Row & ", Column: " & lastCell.Column
vba excel range object

Excel VBA Range Tutorial

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

The VBA Range Object

The Excel Range Object is an object in Excel VBA that represents a cell, row, column, a selection of cells or a 3 dimensional range. The Excel Range is also a Worksheet property that returns a subset of its cells.

Contents
worksheet range
worksheet cells
range rows and columns
range cut paste
range clear
traverse ranges
range addresses
merged ranges
named ranges
specialcells

Worksheet Range

worksheet range property
The Worksheet Range property
The Range is a Worksheet property which allows you to select any subset of cells, rows, columns etc. You can use the Range property to:

  • Obtain a reference to a single Worksheet Cell:
    range select cell
    Select a single cell using the Range object
  • Obtain a range of cells using a string:
    range select range
    Select a Range of cells using a string parameter
  • Obtain a range of cells using other Range objects:
    range select range
    Select a Range using other Range objects
The Range.Select function select the range within a Worksheet.
The Range.Activate function makes the range visible

Working with Range variables

The Range is a separate object variable and can be declared as other variables:

Dim myRange as Range

Excel facilitates a Range function that allows you to obtain cells from within the ActiveSheet, current top-most worksheet.

Range("A1").Select
'...is the same as...
ActiveSheet.Range("A1").Select
The ActiveWorkbook is not same to ThisWorkbook. Same goes for the ActiveSheet. This may reference a Worksheet from within a Workbook external to the Workbook in which the macro is executed as Active references simply the currently top-most worksheet. Read more here

Range properties

The Range object contains a variety of properties with the main one being it’s Value and an the second one being its Formula.

A Range Value is the evaluated property of a cell or a range of cells. For example a cell with the formula =10+10 has an evaluated value of 20.

A Range Formula is the formula provided in the cell or range of cells. For example a cell with a formula of =10+10 will have the same Formula property.

Other Range properties include:
Work in progress

Worksheet Cells

worksheet cells arguments
The Worksheet Cells property
A Worksheet Cells property is similar to the Range property but allows you to obtain only a SINGLE CELL, based on its row and column index. Numbering starts at 1:
cells select cell
Select a single Cell using the Cells property

The Cells property is in fact a Range object not a separate data type.
Excel facilitates a Cells function that allows you to obtain a cell from within the ActiveSheet, current top-most worksheet.

Cells(2,2).Select 'Selects B2
'...is the same as...
ActiveSheet.Cells(2,2).Select 'Select B2

Cells are Ranges which means they are not a separate data type:

Dim myRange as Range
Set myRange = Cells(1,1) 'Cell A1

Range Rows and Columns

As we all know an Excel Worksheet is divided into Rows and Columns. The Excel VBA Range object allows you to select single or multiple rows as well as single or multiple columns. There are a couple of ways to obtain Worksheet rows in VBA:

Getting an entire row or column

entirerow range
Range EntireRow property
To get and entire row of a specified Range you need to use the EntireRow property. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the row number. Row indexing starts at 1.
entirecolumn range
Range EntireColumn property
To get and entire column of a specified Range you need to use the EntireColumn property. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the column number. Column indexing starts at 1.

Range("B2").EntireRows(1).Hidden = True 'Gets and hides the entire row 2

Range("B2").EntireColumns(1).Hidden = True 'Gets and hides the entire column 2
The three properties EntireRow/EntireColumn, Rows/Columns and Row/Column are often misunderstood so read through to understand the differences.

Get a row/column of a specified range

range rows function
Range Rows function
If you want to get a certain row within a Range simply use the Rows property of the Worksheet. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex it is enough just to provide the row number. Row indexing starts at 1.
range columns function
Range Columns property
Similarly you can use the Columns function to obtain any single column within a Range. Although, the function’s parameters suggest taking both a RowIndex and ColumnIndex actually the first argument you provide will be the column index. Column indexing starts at 1.

Rows(1).Hidden = True 'Hides the first row in the ActiveSheet
'same as
ActiveSheet.Rows(1).Hidden = True

Columns(1).Hidden = True 'Hides the first column in the ActiveSheet
'same as
ActiveSheet.Columns(1).Hidden = True

To get a range of rows/columns you need to use the Range function like so:

Range(Rows(1), Rows(3)).Hidden = True 'Hides rows 1:3
'same as
Range("1:3").Hidden = "True
'same as 
ActiveSheet.Range("1:3").Hidden = "True

Range(Columns(1), Columns(3)).Hidden = True 'Hides columns A:C
'same as
Range("A:C").Hidden = "True
'same as 
ActiveSheet.Range("A:C").Hidden = "True

Get row/column of specified range

The above approach assumed you want to obtain only rows/columns from the ActiveSheet – the visible and top-most Worksheet. Usually however, you will want to obtain rows or columns of an existing Range. Similarly as with the Worksheet Range property, any Range facilitates the Rows and Columns property.

Dim myRange as Range
Set myRange = Range("A1:C3")

myRange.Rows.Hidden = True 'Hides rows 1:3
myRange.Columns.Hidden = True 'Hides columns A:C

Set myRange = Range("C10:F20")
myRange.Rows(2).Hidden = True 'Hides rows 11
myRange.Columns(3).Hidden = True 'Hides columns E

Getting a Ranges first row/column number

Aside from the Rows and Columns properties Ranges also facilitate a Row and Column property which provide you with the number of the Ranges first row and column.

Set myRange = Range("C10:F20")

'Get first row number
Debug.Print myRange.Row 'Result: 10
'Get first column number
Debug.Print myRange.Column 'Result: 3

Converting Column number to Excel Column

This is an often question that turns up – how to convert a column number to a string e.g. 100 to “CV”.

Function GetExcelColumn(columnNumber As Long)
    Dim div As Long, colName As String, modulo As Long
    div = columnNumber: colName = vbNullString

    Do While div > 0
        modulo = (div - 1) Mod 26
        colName = Chr(65 + modulo) & colName
        div = ((div - modulo) / 26)
    Loop

    GetExcelColumn = colName
End Function

Range Cut/Copy/Paste

Cutting and pasting rows is generally a bad practice which I heavily discourage as this is a practice that is moments can be heavily cpu-intensive and often is unaccounted for.

Copy function

Range copy function
Range copy function
The Copy function works on a single cell, subset of cell or subset of rows/columns.

'Copy values and formatting from cell A1 to cell D1
Range("A1").Copy Range("D1")

'Copy 3x3 A1:C3 matrix to D1:F3 matrix - dimension must be same
Range("A1:C3").Copy Range("D1:F3")

'Copy rows 1:3 to rows 4:6
Range("A1:A3").EntireRow.Copy Range("A4")

'Copy columns A:C to columns D:F
Range("A1:C1").EntireColumn.Copy Range("D1")

The Copy function can also be executed without an argument. It then copies the Range to the Windows Clipboard for later Pasting.

Cut function

range cut function
Range Cut function
The Cut function, similarly as the Copy function, cuts single cells, ranges of cells or rows/columns.

'Cut A1 cell and paste it to D1
Range("A1").Cut Range("D1")

'Cut 3x3 A1:C3 matrix and paste it in D1:F3 matrix - dimension must be same
Range("A1:C3").Cut Range("D1:F3")

'Cut rows 1:3 and paste to rows 4:6
Range("A1:A3").EntireRow.Cut Range("A4")

'Cut columns A:C and paste to columns D:F
Range("A1:C1").EntireColumn.Cut Range("D1")

The Cut function can be executed without arguments. It will then cut the contents of the Range and copy it to the Windows Clipboard for pasting.

Cutting cells/rows/columns does not shift any remaining cells/rows/columns but simply leaves the cut out cells empty

PasteSpecial function

range pastespecial function
Range PasteSpecial function
The Range PasteSpecial function works only when preceded with either the Copy or Cut Range functions. It pastes the Range (or other data) within the Clipboard to the Range on which it was executed.

Syntax

The PasteSpecial function has the following syntax:

PasteSpecial( Paste, Operation, SkipBlanks, Transpose)
The PasteSpecial function can only be used in tandem with the Copy function (not Cut)
Parameters

Paste
The part of the Range which is to be pasted. This parameter can have the following values:

Parameter Constant Description
xlPasteSpecialOperationAdd 2 Copied data will be added with the value in the destination cell.
xlPasteSpecialOperationDivide 5 Copied data will be divided with the value in the destination cell.
xlPasteSpecialOperationMultiply 4 Copied data will be multiplied with the value in the destination cell.
xlPasteSpecialOperationNone -4142 No calculation will be done in the paste operation.

xlPasteSpecialOperationSubtract 3 Copied data will be subtracted with the value in the destination cell.

Operation
The paste operation e.g. paste all, only formatting, only values, etc. This can have one of the following values:

Name Constant Description
xlPasteAll -4104 Everything will be pasted.
xlPasteAllExceptBorders 7 Everything except borders will be pasted.
xlPasteAllMergingConditionalFormats 14 Everything will be pasted and conditional formats will be merged.
xlPasteAllUsingSourceTheme 13 Everything will be pasted using the source theme.
xlPasteColumnWidths 8 Copied column width is pasted.
xlPasteComments -4144 Comments are pasted.
xlPasteFormats -4122 Copied source format is pasted.
xlPasteFormulas -4123 Formulas are pasted.
xlPasteFormulasAndNumberFormats 11 Formulas and Number formats are pasted.
xlPasteValidation 6 Validations are pasted.
xlPasteValues -4163 Values are pasted.
xlPasteValuesAndNumberFormats 12 Values and Number formats are pasted.

SkipBlanks
If True then blanks will not be pasted.

Transpose
Transpose the Range before paste (swap rows with columns).

PasteSpecial Examples

'Cut A1 cell and paste its values to D1
Range("A1").Copy
Range("D1").PasteSpecial
 
'Copy 3x3 A1:C3 matrix and add all the values to E1:G3 matrix (dimension must be same)
Range("A1:C3").Copy 
Range("E1:G3").PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd

Below an example where the Excel Range A1:C3 values are copied an added to the E1:G3 Range. You can also multiply, divide and run other similar operations.

PasteSpecial example - Copy and Add
PasteSpecial example – Copy and Add

Paste

The Paste function allows you to paste data in the Clipboard to the actively selected Range. Cutting and Pasting can only be accomplished with the Paste function.

'Cut A1 cell and paste its values to D1
Range("A1").Cut
Range("D1").Select
ActiveSheet.Paste
 
'Cut 3x3 A1:C3 matrix and paste it in D1:F3 matrix - dimension must be same
Range("A1:C3").Cut 
Range("D1:F3").Select
ActiveSheet.Paste
 
'Cut rows 1:3 and paste to rows 4:6
Range("A1:A3").EntireRow.Cut 
Range("A4").Select
ActiveSheet.Paste
 
'Cut columns A:C and paste to columns D:F
Range("A1:C1").EntireColumn.Cut 
Range("D1").Select
ActiveSheet.Paste

Range Clear/Delete

The Clear function

The Clear function clears the entire content and formatting from an Excel Range. It does not, however, shift (delete) the cleared cells.

Range("A1:C3").Clear
Excel Range Clear function example
Excel Range Clear function example

The Delete function

Range Delete function
Range Delete function
The Delete function deletes a Range of cells, removing them entirely from the Worksheet, and shifts the remaining Cells in a selected shift direction.
Although the manual Delete cell function provides 4 ways of shifting cells. The VBA Delete Shift values can only be either be xlShiftToLeft or xlShiftUp.

'If Shift omitted, Excel decides - shift up in this case
Range("B2").Delete 

'Delete and Shift remaining cells left
Range("B2").Delete xlShiftToLeft  

'Delete and Shift remaining cells up
Range("B2").Delete xlShiftTop

'Delete entire row 2 and shift up
Range("B2").EntireRow.Delete

'Delete entire column B and shift left
Range("B2").EntireRow.Delete
Excel Range Delete - shifting cells
Excel Range Delete – shifting cells

Traversing Ranges

Traversing cells is really useful when you want to run an operation on each cell within an Excel Range. Fortunately this is easily achieved in VBA using the For Each or For loops.

Dim cellRange As Range
    
For Each cellRange In Range("A1:C3")
  Debug.Print cellRange.Value
Next cellRange
Although this may not be obvious, beware of iterating/traversing the Excel Range using a simple For loop. For loops are not efficient on Ranges. Use a For Each loop as shown above. This is because Ranges resemble more Collections than Arrays. Read more on For vs For Each loops here

Traversing the UsedRange

Excel Range - Worksheet UsedRange
Excel Range – Worksheet UsedRange
Every Worksheet has a UsedRange. This represents that smallest rectangle Range that contains all cells that have or had at some point values. In other words if the further out in the bottom, right-corner of the Worksheet there is a certain cell (e.g. E8) then the UsedRange will be as large as to include that cell starting at cell A1 (e.g. A1:E8). In Excel you can check the current UsedRange hitting CTRL+END. In VBA you get the UsedRange like this:

ActiveSheet.UsedRange
'same as
UsedRange

You can traverse through the UsedRange like this:

Dim cellRange As Range
    
For Each cellRange In UsedRange
  Debug.Print "Row: " & cellRange.Row & ", Column: " & cellRange.Column
Next cellRange
The UsedRange is a useful construct responsible often for bloated Excel Workbooks. Often delete unused Rows and Columns that are considered to be within the UsedRange can result in significantly reducing your file size. Read also more on the XSLB file format here

Range Addresses

The Excel Range Address property provides a string value representing the Address of the Range.

Excel Range Address property
Excel Range Address property

Syntax

Below the syntax of the Excel Range Address property:

Address( [RowAbsolute], [ColumnAbsolute], [ReferenceStyle], [External], [RelativeTo] )

Parameters

RowAbsolute
Optional. If True returns the row part of the reference address as an absolute reference. By default this is True.

$D$10:$G$100 'RowAbsolute is set to True
$D10:$G100 'RowAbsolute is set to False

ColumnAbsolute
Optional. If True returns the column part of the reference as an absolute reference. By default this is True.

$D$10:$G$100 'ColumnAbsolute is set to True
D$10:G$100 'ColumnAbsolute is set to False

ReferenceStyle
Optional. The reference style. The default value is xlA1. Possible values:

Constant Value Description
xlA1 1 Default. Use xlA1 to return an A1-style reference
xlR1C1 -4150 Use xlR1C1 to return an R1C1-style reference

External
Optional. If True then property will return an external reference address, otherwise a local reference address will be returned. By default this is False.

$A$1 'Local
[Book1.xlsb]Sheet1!$A$1 'External

RelativeTo
Provided RowAbsolute and ColumnAbsolute are set to False, and the ReferenceStyle is set to xlR1C1, then you must include a starting point for the relative reference. This must be a Range variable to be set as the reference point.

Merged Ranges

Excel Range Merge function
Excel Range Merge function
Merged cells are Ranges that consist of 2 or more adjacent cells. To Merge a collection of adjacent cells run Merge function on that Range.

The Merge has only a single parameter – Across, a boolean which if True will merge cells in each row of the specified range as separate merged cells. Otherwise the whole Range will be merged. The default value is False.

Merge examples

To merge the entire Range:

'This will turn of any alerts warning that values may be lost
Application.DisplayAlerts = False

Range("B2:C3").Merge

This will result in the following:

Excel Range Merged cells
Excel Range Merged cells

To merge just the rows set Across to True.

'This will turn of any alerts warning that values may be lost
Application.DisplayAlerts = False

Range("B2:C3").Merge True

This will result in the following:

Excel Range Merged cells across rows
Excel Range Merged cells across rows

Remember that merged Ranges can only have a single value and formula. Hence, if you merge a group of cells with more than a single value/formula only the first value/formula will be set as the value/formula for your new merged Range

Checking if Range is merged

To check if a certain Range is merged simply use the Excel Range MergeCells property:

Range("B2:C3").Merge

Debug.Print Range("B2").MergeCells 'Result: True

The MergeArea

The MergeArea is a property of an Excel Range that represent the whole merge Range associated with the current Range. Say that $B$2:$C$3 is a merged Range – each cell within that Range (e.g. B2, C3..) will have the exact same MergedArea. See example below:

Range("B2:C3").Merge
Debug.Print Range("B2").MergeArea.Address 'Result: $B$2:$C$3

Named Ranges

Named Ranges are Ranges associated with a certain Name (string). In Excel you can find all your Named Ranges by going to Formulas->Name Manager. They are very useful when working on certain values that are used frequently through out your Workbook. Imagine that you are writing a Financial Analysis and want to use a common Discount Rate across all formulas. Just the address of the cell e.g. “A2”, won’t be self-explanatory. Why not use e.g. “DiscountRate” instead? Well you can do just that.

Creating a Named Range

Named Ranges can be created either within the scope of a Workbook or Worksheet:

Dim r as Range
'Within Workbook
Set r = ActiveWorkbook.Names.Add("NewName", Range("A1"))
'Within Worksheet
Set r = ActiveSheet.Names.Add("NewName", Range("A1"))

This gives you flexibility to use similar names across multiple Worksheets or use a single global name across the entire Workbook.

Listing all Named Ranges

You can list all Named Ranges using the Name Excel data type. Names are objects that represent a single NamedRange. See an example below of listing our two newly created NamedRanges:

Call ActiveWorkbook.Names.Add("NewName", Range("A1"))
Call ActiveSheet.Names.Add("NewName", Range("A1"))

Dim n As Name
For Each n In ActiveWorkbook.Names
  Debug.Print "Name: " & n.Name & ", Address: " & _
       n.RefersToRange.Address & ", Value: "; n.RefersToRange.Value
Next n

'Result:
'Name: Sheet1!NewName, Address: $A$1, Value:  1 
'Name: NewName, Address: $A$1, Value:  1 

SpecialCells

SpecialCells are a very useful Excel Range property, that allows you to select a subset of cells/Ranges within a certain Range.

Syntax

The SpecialCells property has the following syntax:

SpecialCells( Type, [Value] )

Parameters

Type
The type of cells to be returned. Possible values:

Constant Value Description
xlCellTypeAllFormatConditions -4172 Cells of any format
xlCellTypeAllValidation -4174 Cells having validation criteria
xlCellTypeBlanks 4 Empty cells
xlCellTypeComments

-4144

Cells containing notes
xlCellTypeConstants 2 Cells containing constants
xlCellTypeFormulas -4123 Cells containing formulas
xlCellTypeLastCell 11 The last cell in the used range
xlCellTypeSameFormatConditions -4173 Cells having the same format
xlCellTypeSameValidation -4175 Cells having the same validation criteria
xlCellTypeVisible 12 All visible cells

Value
If Type is equal to xlCellTypeConstants or xlCellTypeFormulas this determines the types of cells to return e.g. with errors.

Constant Value
xlErrors 16
xlLogical 4
xlNumbers 1
xlTextValues 2

SpecialCells examples

Get Excel Range with Constants

This will return only cells with constant cells within the Range C1:C3:

For Each r In Range("A1:C3").SpecialCells(xlCellTypeConstants)
  Debug.Print r.Value
Next r

Search for Excel Range with Errors

For Each r In ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas, xlErrors)
  Debug.Print r.Address
Next r
autofilter featured

Automatic Excel Autofilter

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 out of 5)
Loading...

With today’s Excel tip of the day let’s have some fun with learning how to create an auto-applying filter to an Excel Table (or range). Let’s jump right to see how we expect our Excel Autofilter to work:

excel autofilter
Excel Autofilter: Type while the table is filtered
We want to achieve the following behavior where our Table column is filtered as we type. This is a very convenient feature especially if you need to dynamically sit through a large dataset of different values. It is perfectly possible to be done in Excel with just a little VBA code.

The whole exercise takes just a minute or so – therefore I find the Excel Autofilter a really useful and flexible tool to use.

How to create an Autofilter

Feel free to review my video or read through for a step-by-step tutorial:

Let’s assume we have a similar dataset as shown above. This does not need be limited to a single column but can be spread out across as many columns we want. To facilitate this example I have however limited the data just to 1 data column of a simple list of first names.

Convert the Range to a Table

This step is not necessary, although will make matters much easier when wanting to create the macro as we move on.

convert to table
Convert the column to an Excel Table

Insert a TextBox from the Developer tab

Now we need to go to the DEVELOPER tab and select Insert and pick Text Box from the ActiveX Controls section.

insert a textbox
Insert a TextBox control

It is suggested that you name your text box appropriately e.g. “NameTextBox”.

Add filtering macro to TextBox Change event

Now the hard part. To create our Excel Autofilter we need to make sure that a filtering event is triggered whenever we want it to happen. Fortunately the TextBox ActiveX Control has a Change Event. The Change Event is triggered whenever the text in the Text Box is modified.

Open the DEVELOPER tab and open your VBA Project using the Visual Basic button. Now make sure to open the Worksheet on which your data and the Text Box is located as shown below:

macro placement
Open the Worksheet where the filtering takes place
.
Now assuming the textbox is named “NameTextBox” and your Table is named “Names” paste the following macro into your Worksheet module:

Private Sub NameTextBox_Change()
    ActiveSheet.ListObjects("Names").Range _
        .AutoFilter Field:=1, Criteria1:="=*" & NameTextBox.Text & "*"
End Sub

Notice that the criteria is specified as follows “=*” & NameTextBox.Text & “*”. How to understand this? The “*” symbol is a wildcard which captures any number of characters (0 or more). Hence this expression will capture any string containing the text we type in our text box. If you want to filter names beginning with the string you are typing simply replace it with: “=” & NameTextBox.Text & “*” – notice I removed the first “*”.

Download the example

Feel free to download the working Excel Autofilter example here:

Excel Optimizer

Optimize Excel formulas with the Excel Optimizer!

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 5.00 out of 5)
Loading...

Ever been in a situation where your Excel workbook runs so slow you could make yourself a coffee before it finishes recalculating? Fighting to reduce the file size of your latest Excel report? Or just looking to optimize your Excel workbook a bit? Welcome to the AnalystCave Excel Optimizer the AddIn that will optimize Excel for you! Excel Optimizer
Stay tuned for updates on this post!
I must say this idea has been popping in and out of my head for some time now – although it turned out the first working version of the Optimizer took me only a couple of hours to build. Often coming along Excel files that literally called for wrath from the Gods, I yearned for a time that Excel would include a “Fix Me” button – doing away with all the wrongs of the Workbook I was working on. Unfortunately this is (still) not the case and building your Excel file many of us have to be constantly aware of the dangers that lie ahead. What is more, many Excel users waste a lot of time not appreciating simple tips and tricks that can seriously enhance their Excel experience (like simply saving files in XLSB file format).

Optimize Excel with the Excel Optimizer

The Excel Optimizer is a simple AddIn to Excel that runs certain rules across your Workbook and points out the main pain-points or suggests certain improvements – all with performance in mind. Some rules can be implemented (I am hoping making all as such) while others will simply point out what needs to get done.

This is still a beta-work-in-progress-prototype so keep in mind that there may be some error/bugs here and there. What is more the Excel Optimizer current will only be able to suggest certain fixes (such as replacing some Excel functions with others). Depending on how popular this AddIn becomes I am planning to extend it to be able to actually implement most of the recommended tweaks. Optimizing Excel for best performance has become much easier.

See this video for a quick showcase of how the Excel Optimizer works.

Documentation

The Excel Optimizer consists of 2 features:

  • Optimize Excel – the main feature. Opens a window (screen below) that will allow you to run a rule-analysis on your current Workbook. Each rule with validate against different performance standards, rules also have different scopes. There are rules validating the format of the Workbook, validating worksheets and then there are the low-level rules that validate each and every formula. Using the settings button you can turn rules on/off or configure them to enhance your experience
  • Timer Full Calculation Rebuild – a simple macro that calculates the time needed to do a Full Rebuild Calculation of your Excel workbook. This comes in handy when wanting to set a baseline for the improvements you might want to implement

Excel Optimizer: AddInRibbon buttons
Excel Optimizer: AddInRibbon buttons

Below a screenshot of how the main window looks like:
Excel Optimizer: Main window
Excel Optimizer: Main window

Rules

Rules are what drive the Excel Optimizer. The AddIn has been built in a way to make adding/removing/enabling/disabling rules as easy as possible. Currently the Excel Optimizer introduces the following types of rules – many of most still cannot be implemented automatically (expect this in the near future):

Rule Scope Description
XLSB Workbook Checks if workbooks is in XLSB format if not suggests so
UsedRange Worksheet Validates the Used Range of each Worksheet to see if the Used Range is excessive and can be reduced for better performance and optimum file size
VLOOKUP Range Looks for exact (FALSE) VLOOKUPs. If found suggest replacing them with double approximate (TRUE) VLOOKUPs for best performance
Repeating formula Range Looks for exact repetitions of certain functions and suggest replacing them with a certain cell to minimize the need to recalculate the same function multiple times
Volatile functions Range Looks for volatile native Excel functions and suggests removing or replacing them
External links Range Looks for any external links used within cells in the entire Workbook and suggests removing them or reducing to a bare minimum
Array Formula Range Looks for Array Formulas within cells in the entire Workbook and suggests replacing them with regular Excel functions if possible
Error Range Looks errors within cells in the entire Workbook and suggests correcting them

Download

You can download the latest version here:

Do you have ideas for new rules? Any suggestions / wishes for additional features? Put your comment below or on my Facebook / Twitter page.