Cell Related Coding

Cells in Worksheet Formulas

You can get a reference to another cell in another sheet by
entering the = sign into the formula box and then
going to the other sheet and clicking on the cell. Then
return to your original sheet and the formula is filled in.
For more information on worksheets see Worksheets in VBA Coding and in Worksheet Formulas (sheets.htm)

Cells, Cell Selection and Ranges in VBA coding (#coding)

This page is a conglomeration of aspects of cell related coding snipets.
Currently this page remains NOINDEX

Cells, last used or next free in a column (#next)

last cell in column A within the same region as cell A1: Range("A1").End(xlDown)

last cell even with empty cells in the column you could use
Range("A65535").End(xlUp).select '-- Example only Range("A" & Rows.Count).End(xlUp).Select
Cells(Rows.Count,1).End(xlUp).Select '-- preferred usage

The use of 65536 to identify the last row is for
illustration purposes only, do not code constants into a program as
the last row and hopefully the last column can and will change
between versions of Excel. Instead use Rows.Count and Columns.Count
-- For examples of these in macros with toolbar icons see
Toolbars page.

Last used row in column A: => 8
Worksheets("Sheet1").Range("A65536").End(xlUp).Row
Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
Next available empty row in column A: => 9
Worksheets("Sheet1").Range("A65536").End(xlUp).Row + 1
Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
xlDown looks for the first empty cell it finds and returns the last used row
before that first empty cell, so:
'Last used row before the first empty cell: => 3
Worksheets("Sheet1").Range("A1").End(xlDown).Row
'First empty cell in column A => 4
Worksheets("Sheet1").Range("A1").End(xlDown).Row + 1
If you want to find the last possible row all the way down column A, use
xlUp.

ActiveCell.End(xlDown)
Range("A1").End(xlDown)

As improbable as it seems you can do something similar with
a Worksheet Function. The following array formula [ctrl+Shift+enter]
will provide the row number for the last cell in Column A.
Dave Perterson 2001-12-23 in
Worksheet.Functions
=MAX(IF(ISBLANK(A1:A1000),"",ROW(A1:A1000)))

Deletion Related (#deletion-related)

Reduce the size of the workbook by eliminating
the rows and columns past the actual data, where the last cell indication
Ctrl+End is incorrect.
"Why do my scrollbars go to row 500 -- my data ends in cell E50?"
http://www.contextures.com/xlfaqApp.html#Unused
Delete the rows and columns outside of a single selection area.
See example within this topic.
Leave the single selection area intact but clear the contents
but not delete cells outside a selection(s).
would be similar to the macro here but would clearcontents
instead of delete rows and columns.

Identification of the current region [Ctrl+SHIFT+*], could be used as a selection.
but you'd probably not have complete control of what you are working with.

Cell Navigation (#navigation)

Options

From one cell to a specific cell

Cell protection will guide you to next unprotected cell

Select a group of cells, use Enter or Tab key

Select individual cells with help of the Ctrl key, and Tab key will
follow the sequence they were laid down. Example: Select A1,B2,C3,D4,E5,D6,C7,B8,A9, TabKey, then in Name box enter "MyEntry".
When you enter "MyEntry" into name box at a later time the sequence will be laid out
for you.