Explain how we can find last data cell in excel

We can find last data cell through two methods

XL Method
Used Range Method

We can find last data cell in two ways

Explain about XL Down method

Through XL down method we can find Last Data cell at either ROW Level or Column Level

XLDown Method
XLUP Method
XLTOLeft Method
XLTORight Method

XL Method Syntax:

1) Write a program to Color LASTCELL in 4 directions, Starting from E8 cell

2) How to find the FIRST DATA CELL:

This is applicable to ROW LEVEL only
This is useful to find first cell which consists of data
This program is useful to find first data cell in 4th row
We can use End(XltoRight) Method

Copy the Code:

Private Sub CommandButton1_Click()
'When first data cell not started from column "A" Range("A4").End(xlToRight).Rows.Select
End Sub

3) How to find the LAST DATA CELL:

We can find last data cell in two ways

From TOP to BOTTOM - - End(XLdown) Method
From BOTTOM to TOP - - End(XLUP) Method

3(i)FINDING LAST DATA CELL from TOP to BOTTOM - IN A COLUMN: End(xlDown) Method

Copy the Code from here:

Top to Bottom:

Private Sub CommandButton1_Click()
'it selects the last cell which consits of data from B4 cell - from TOP to Bottom
Range("B4").End(xlDown).Rows.Select
End Sub

Bottom to Top:

Private Sub CommandButton2_Click()
'From Bottom to Top
Range("B101").End(xlUp).Rows.Select
End Sub

3(ii):FINDING LAST DATA CELL from BOTTOM to TOP - End(xlUP) Method

Copy the Code from here:

Private Sub CommandButton1_Click()
'from bottom of excel sheet to last data cell
Cells(Rows.Count, "C").End(xlUp).Rows.Select
'from 1000th row to last data cell
Range("C1000").End(xlUp).Rows.Select
End Sub

'Cells(Rows.Count, "C")

it represents to all the rows 1048576(Excel 2007 version), excel application calcuates from bottom of the cells to TOP

(or) we can assign a range, from where we can start to move upward., in this example i assinged 1000 row as base. From that cell application move upward for last data cell

4) How to find the LAST DATA CELL - Left to Right:

Last Data Cell from LEFT to RIGHT - End(xlToRight) Method:

We can find last data cell in two ways

From LEFT to RIGHT - - End(xlToRight) Method
From RIGHT to LEFT - - End(xlToLeft) Method

Copy the code

Private Sub CommandButton1_Click()
'from first cell to last cell in same row (LEFT -RIGHT)
Range("D4").End(xlToRight).Columns.Select
'from the end of columns count to last cell(RIGHT - LEFT)
Cells(4, Columns.Count).End(xlToLeft).Columns.Select
End Sub

We can use either one of aforementioned methods

Last empty row from BOTTOM To TOP

Lastrow = ThisWorkbook.Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row

Last empty row from BOTTOM To TOP:

Private Sub CommandButton1_Click()
Private Sub CommandButton1_Click()
Range("A1000").End(xlUp).Rows.Select
MsgBox Selection.Row
MsgBox Range("D15").Row
End Sub

Next Available Cell Offset Method

Private Sub CommandButton1_Click()
Dim j As Integer
j = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
MsgBox j
End Sub

Next_Available_Cell_Endxlup_Method

Private Sub CommandButton1_Click()
j = Range("A" & Rows.Count).End(xlUp).Row + 1
MsgBox j
End Sub

Last used cell

Private Sub CommandButton1_Click()
f = Range("A1").End(xlDown).Row
MsgBox f
End Sub

Last cells in USED RANGE

Private Sub CommandButton1_Click()
UsedRange.Select
r = Selection.Rows.Count
c = Selection.Columns.Count
Cells(r, c).Select
End Sub

Define The Lastrow using Find Function

Sub DefineTheLastRowUsingFindFunction()
Dim sh As Worksheet
Set sh = ActiveSheet
Dim LRow As Long
LRow = sh.Cells.Find("*", LookIn:=xlFormulas, searchorder:=xlByRows, searchdirection:=xlPrevious).Row
End Sub