Explain how we can find last data cell in excel
Explain about XL Down method
2) How to find the FIRST DATA CELL:
Private Sub CommandButton1_Click()
End Sub
3) How to find the LAST DATA CELL:
Private Sub CommandButton1_Click()
Range("B4").End(xlDown).Rows.Select
End Sub
Private Sub CommandButton2_Click()
Range("B101").End(xlUp).Rows.Select
End Sub
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")
4) How to find the LAST DATA CELL - Left to Right:
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
Lastrow = ThisWorkbook.Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
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