You can find the last used cell in Excel, which contains data or formatting on a worksheet. There are various ways you can do this, depending on the cell you want to locate.
How to locate the last used cell which contains data or formatting on a worksheet
Use the following steps;
1. Click anywhere in the worksheet
2. Press Ctrl+ END
To select the last used cell in any row or column, just press END> Click the Right Arrow /Down Arrow
3. To clear formatting between the last cell and the data, do the following
Selecting all columns that contain data;
1. Select the first column heading
2. Press Ctrl and click the column heading
3. Press CTRL+ SHIFT+ END
Selecting rows below the row that has the data
1. Select the heading of the first row
2. Press Ctrl
3. Click the headings of the rows that you want to select
4. Use Ctrl+ Shift+ END
5. Go to the Home Tab
6. Choose the Editing Group and click the arrow just before Clear. Click on Clear All.
7. Go to File Tab and Save your worksheet
8. Close Worksheet
Opening it again, you will find that the last used cell is the last in your worksheet.
Finding the Last Used Cell that has Data in a Row or Column using VBA
Using VBA, you will find the last used cells with data in a row or column. Use the following steps;
1. Press the keys Alt+ F11 to open Microsoft Visual Basic for Applications Window
2. Select Insert and click Module. Copy Paste the VBA code to the Module window that appears
code source: Visit Here for the code Source
3. Press Run/ F5 for running VBA. A dialog box pops out and tells you where the last cell of the column is.
Finding the last Used Cell that has Data using Kutools
There is a feature called Select Last Cells in Kutools, which can select the Last Cell in the last column or row. You have to install Kutools in Excel, then follow the easy steps below to find the cells;
1. Enable the worksheet that you want to find the last the cells
2. Go to Kutools
3. Click on Select>Select Last Cells
4. You can apply Select Last Cells> Last Row to select the last cell with data in the last row.
5. To select the last used cell with data in the last column, click Select Last Cells> Last Column
Using the MAX function
You can find the last used cell using the MAX function also.
This formula is as; =INDEX ($B$2:$B$14,SUMPRODUCT( MAX(ROW($A$2:$A$14)*($D$3=$A$2:$14))-1))
The formula works as follows;
MAX finds the row number of the last used cell.
SUMPRODUCT ensures that you don't have to use Ctrl+ Shift + Enter since SUMPRODUCT can also handle formulas
INDEX function finds the date that the cell was last used. This technique is quite easy and fast to use.