Tutorial by Examples

In this example, we will look at a method for returning the last non-empty row in a column for a data set. This method will work regardless of empty regions within the data set. However caution should be used if merged cells are involved, as the End method will be "stopped" against a mer...

In case you have a Named Range in your Sheet, and you want to dynamically get the last row of that Dynamic Named Range. Also covers cases where the Named Range doesn't start from the first Row. Sub FindingLastRow() Dim sht As Worksheet Dim LastRow As Long Dim FirstRow As Long Set sht =...

'if only one area (not multiple areas): With Range("A3:D20") Debug.Print .Cells(.Cells.CountLarge).Row Debug.Print .Item(.Cells.CountLarge).Row 'using .item is also possible End With 'Debug prints: 20 'with multiple areas (also works if only one area): Dim rngArea As Range,...

Private Sub Get_Last_Used_Row_Index() Dim wS As Worksheet Set wS = ThisWorkbook.Sheets("Sheet1") Debug.Print LastCol_1(wS) Debug.Print LastCol_0(wS) End Sub You can choose between 2 options, regarding if you want to know if there is no data in the worksheet :...

Range.CurrentRegion is a rectangular range area surrounded by empty cells. Blank cells with formulas such as ="" or ' are not considered blank (even by the ISBLANK Excel function). Dim rng As Range, lastCell As Range Set rng = Range("C3").CurrentRegion ' or Set rng = Shee...

Private Sub Get_Last_Used_Row_Index() Dim wS As Worksheet Set wS = ThisWorkbook.Sheets("Sheet1") Debug.Print LastRow_1(wS) Debug.Print LastRow_0(wS) End Sub You can choose between 2 options, regarding if you want to know if there is no data in the worksheet :...

In this example, we will look at a method for returning the last non-empty column in a row. This method will work regardless of empty regions within the data set. However caution should be used if merged cells are involved, as the End method will be "stopped" against a merged region, ret...

The first function, using an array, is much faster If called without the optional parameter, will default to .ThisWorkbook.ActiveSheet If the range is empty will returns Cell( 1, 1 ) as default, instead of Nothing Speed: GetMaxCell (Array): Duration: 0.0000790063 seconds GetMaxCell (Find ...