Current Data Region and related items

This page will provide some macros to simulate manual use of  such things as CTRL + arrow key, which moves to the edge of the current data region.

Basic Concepts

Some concepts:
 all cells    --   Ctrl+A      (at least it was before Excel 2003)
current range   --  Ctrl+Shift+*
last cell in used range      --   Ctrl + End  (for last cell)

cells.select
ActiveCell.CurrentRegion.Select
Cells.SpecialCells(xlLastCell)
activesheet.usedrange.select

Column C = Column A  x  Column B

manual procedure:
Select cell C1 and place into it "=a1*b1", then since it only makes sense to want a product if there is something in column B, extend the formula down as long as there is something in adjacent column B (or column D for that matter), by double-clicking on the fill handle.  The macro example will check Col A instead of Col B.

macro solution: (solution provided by Thomas Ogilvy)
This programming solution is specific to the example (value in column A times value in column B, results in column C => row by row multiplication)

Sub A1xB1()
   ' Thomas Ogilvy,  ColumnProduct Wed, 10 Mar 1999
    Set rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
    rng.Offset(, 2).Formula = "=" & rng(1, 1).Address(False, False)  _
       & "*" & rng(1, 2).Address(False, False)
End Sub

Modified to start in column C2, allowing for Headings on Row 1
Sub A2xB3()
   ' Thomas Ogilvy,  ColumnProduct Wed, 10 Mar 1999
    Set rng = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
    rng.Offset(, 2).Formula = "=" & rng(2, 1).Address(False, False)  _
       & "*" & rng(2, 2).Address(False, False)
End Sub

Add 10 to each cell in a range

manual procedure:

1. select a cell or create one with a 10 in it
2. copy (ctrl+c)
3. select the range
4. edit --> paste special
5. operation: add

manual procedure:

 Sub Macro16()
   k = 10
   'k = InputBox("Supply number to be added to selected range")
  For Each cell In Selection
     If IsNumeric(cell) Then
        If cell.HasFormula Then
           cell.Formula = cell.Formula & "+" & k
        Else    '**make a choice**
          cell.Value = k + cell.Value
          '**If Trim(cell) <> "" Then cell.Value = cell.Value + k
        End If
     End If
  Next cell
End Sub

Differences between manual, macro and comments in general

A cell that refers to another cell that is not numeric will generate "#VALUE!"  when using Paste Special.  Any cell in the same range referring to another cell that tests as numeric will have value added twice, once for the initial cell and again in the dependent cell.  Which means for some the following may be sufficient;

   For each c in selection
       if isnumeric(c) & NOT c.hasformula then c.value = c.value + 10
   next c

Move cursor 3 cells to the right

    ActiveCell.Offset(0, 3).Select

Confusion between CELLS in VBE, and CELL as a Worksheet Function

That was tricky I missed it a little confusion between two very familar functions that are named almost alike but are completely different in use and meaning.

Excel Worksheet Function: CELL_Worksheet_Function
    CELL(info_type,reference)

VBE: Cells Property
    Use Cells(row, column) where row is the row index
    and column is the column index, ...

The following statement is invalid and displays #NAME? on the worksheet
    ActiveCell.Formula = "=Sum(Cells(5, 7) : Cells(5, " & lngCOLBODY & "))"

Either of these are valid, the first might generate =Sum(G5:J5) and the second might generate =SUM($G$5:$J$5)

  ActiveCell.Formula = "=SUM(" & Cells(5, 7).Address(False, False) _
        & ":" & Cells(5, lngCOLBODY).Address(False, False) & ")"
  ActiveCell.Formula = "=Sum(" & Range(Cells(5, 7), Cells(5, _
        lngCOLBODY)).Address & ")"

Also see Alan Beban's page (see additional or related references below).

Last Cell in a Range

Lastcell

     Cells.SpecialCells(xlLastCell).address(0,0)

Last row or column in current region, or specified range.

     activecell.currentregion.item(activecell.currentregion.count).activate

Last item in a range:

    'activate doesn't change range but will change active cell in range
    Selection.Item(Selection.Count).activate
    Range("A1:F200").item(Range("A1:F200").Count).activate

Split into two printed reports

Split a report into groups with separate sets of page numbering for printing.

Suppose the second portion has something you can identify like the string "PartTwo" in Column A. Capitalization (lettercase) does not matter.

Option Explicit
Sub Macro17()
    Dim rng1 As Range, rng2 As Range
    Columns("A:A").Select
    Selection.Find(What:="PARTTWO", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False).Activate
    Set rng1 = Range(Cells(1, 1), ActiveCell.Offset(-1, 0)).EntireRow
    Set rng2 = Range(ActiveCell, Cells.SpecialCells(xlLastCell))
    rng1.Select
    Selection.PrintOut Copies:=1, Collate:=True
    rng2.Select
    Selection.PrintOut Copies:=1, Collate:=True
End Sub

Related Information

How to install/use a macro can be found on my formula

Creation of Shortcut keys spreadsheet and an Example from XL95.  Same directions as in join.htm#shortcut.

Related Information in Google Usenet Archives

• Cells Within Ranges
by Alan Beban.  Describes the Visual Basic procedures to access a particular cell in a range of cells, such as a named range.
• Working With named ranges In Excel by Chip Pearson.
• Zooming And Centering On Cells Chip Pearson, more on this at Determining the Video Resolution tip 06, John Walkenbach.

Microsoft Knowledge Data Base (MS KB)

(no additiona information at this time)

Visit [my Excel home page]   [Index page]   [Excel Onsite Search]   [top of this page]

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

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