Cell Related Coding

Location:   http://www.mvps.org/dmcritchie/excel/cells.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

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)
   ='sheet three'!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: 

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

'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


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

Last Column in a Row

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

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

Columns 16 through end (#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)

Addresses for the range, the top left cell and the bottom right cell within a selection event for testing.
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
All four corners:
    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

an example

Some test coding leading up to the Isolate_selection macro

  GoTo skip_over
  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"
  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)
  '-- 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


tools, options, Edit, [x] move selection after entry -- Down/right/up/left

From one cell to a specific cell

If not familiar with installation and use of macros, see Getting Started with Macros and User Defined Functions

This page was created on June 10, 2000. 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Excel Onsite Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send%20email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved