Cell Related Coding

   ='sheet three'!D3
   =sheet36!D3
   ='[otherworkbook.xls]some other sheet'!$B$7

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

Use of  xldown   xlup   xltoRight   xltoLeft
  Range(ActiveCell, ActiveCell.End(xldown)).Select

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)))

Last Column in a Row

Last cell even with empty cells in the row you could use

  Range("A256").End(xlLeft).select

  Range("A" & Columns.Count).End(xlLeft).Select

  Cells(1,Columns.Count).End(xlLeft).Select

Columns 16 through end (#select)

  Columns("P:IV").Select

  Range(Cells(1, 16), Cells(1, Columns.Count)).EntireColumn.Select

Identification of the last cell address -- varies with different versions   MsgBox Cells(Rows.Count, Columns.Count).Address   '-- i.e. IV65536 or TT2000000 in Excel 12.

Copying specific cells on specific sheets to ... (#hardcoded)

Dim rng as Range
set rng = worksheets("Dest").Cells(rows.count,1).end(xlup)(2)
worksheets("Sheet1").Range("D24").Copy Destination:=rng
Worksheets("Sheet1").Range("F23").Copy Destination:=rng.offset(0,1)

Top Left cell / Bottom Right Cell in a Selection (#topleft)

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   MsgBox Selection.Address & Chr(10) _
      & Selection.Item(1).Address & Chr(10) _
     & Selection.Item(Selection.Count).Address   
End Sub

    Msgbox "TopLeft: " & Selection.Cells(1).Address  _
      & "      TopRIght: " & _
       Selection.Cells(1, Selection.Columns.Count).Address _
      & Chr(10) & "BotLeft " & Selection.Cells(Selection.Rows.Count, 1).Address _
      & "      BotRight " & Selection.Cells(Selection.Cells.Count).Address

Deletions (#deletions)

Deletion of Columns and Rows

 'Delete Columns  
   Range("C:C,E:G,I:I,K:K,M:S,U:U,W:W,Y:Y").Delete xlToLeft

 'Delete Rows
   Range("14:400").Delete 

an example

Some test coding leading up to the Isolate_selection macro

 
  GoTo skip_over
  Range("A1:M30").Select
  Application.Run "personal.xls!MarkCells"
  Range("a1:g10, j12:k12").Select
  
  'GoTo skip_over
  MsgBox Selection.Areas.Count & " in " & Selection.Address  
  MsgBox ActiveSheet.UsedRange.Address & " -- Used Range address"
  MsgBox Selection.Address & " -- " & Selection.Areas(1).Address
  MsgBox Selection.Count & " -- " & Selection.Areas(1).Count
  MsgBox Selection.Areas(1)(Selection.Areas(1).Count).Address

  MsgBox ActiveSheet.UsedRange.Address
  MsgBox Cells.SpecialCells(xlLastCell).Column
  MsgBox Cells(Rows.Count, Columns.Count).Address & " -- lastcell address"
  
skip_over:
  Dim shouldsee As String 
  Dim rng1_address As String, rng2_address As String

The MarkCells macro can be found at join.htm#markcells and the code at code/join.txt

Isolate_Seletion -- delete everythin not in First area (#isolate_seletion)

Sub Isolate_selection()
  'Delete all cells not within the first or only selection area
  'David McRitchie,  excel.misc, cells.htm 2005-11-17

  Dim shouldsee As String 
  Dim rng1_address As String, rng2_address As String
  shouldsee = Selection.Areas(1).Address(0, 0)
  Range(shouldsee).Select
  '-- identify top left cell and bottom right cell of area to be kept
  rng1_address = Range(Selection(1)(1)).Address
  rng2_address = Range(Selection.Areas(1)(Selection.Areas(1).Count).Address)
  On Error Resume Next  'important if no rows and/or columns before seletion
  '-- delete rows below Selection.areas(1) ...
  Range(Range(rng2_address).row + 1 & ":" & Cells.Rows.Count).Delete
  '-- delete columns to right of Selection.areas(1) ...
 ' Range(Range(rng2_address).Offset(0, 1) & ":IV65536").EntireColumn.Delete
  Range(Range(rng2_address).Offset(0, 1).Address & ":" & _
      Cells(Rows.Count, Columns.Count).Address).EntireColumn.Delete
  '-- delete columns to left of selection.areas(1) ...
  Range("a1:" & Range(rng1_address).Offset(0, -1)).EntireColumn.Delete
  '-- delete rows before selection.areas(1) ...
  Range("A1:" & Range(rng1_address).Offset(-1, 0)).EntireRow.Delete
  
  MsgBox "you should see your original " & should see
 ' ActiveWorkbook.Save   '-- fix last cell
End Sub

Deletion Related (#deletion-related)

• Delete rows and columns past the active cell
  http://www.mvps.org/dmcritchie/excel/lastcell.htm#MakeLastCell
• 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.

Related (#related)

• Worksheets in VBA Coding and in Worksheet Formulas (sheets.htm)
• Miscelaneous Code fragments of Worksheet functions (miscws.htm)