Étiquette : formulas_functions

The DataSeries method of the Range object allows you to fill a range with a sequence (arithmetic, geometric, date-based, or AutoFill).
This method programmatically replicates the Fill | Series command available on the Ribbon.

DataSeries(RowCol, Type, Date, Step, Stop, Trend)

Parameters:

• RowCol (optional) – Direction of the series:
  • xlRows → fill across rows.
  • xlColumns → fill down columns.
    If omitted, Excel uses the size of the selected range.
• Type (optional) – The type of series. Possible values:
  • xlDataSeriesLinear (arithmetic, default).
  • xlGrowth (geometric).
  • xlChronological (date series).
  • xlAutoFill (pattern-based autofill).
• Date (optional) – Defines the type of date sequence when Type = xlChronological:
  • xlDay (days, default).
  • xlWeekday (weekdays only).
  • xlMonth (months).
  • xlYear (years).
• Step (optional) – Increment of the series (default = 1).
• Stop (optional) – The upper limit of the series. If omitted, Excel fills the entire selected range.
• Trend (optional) – Boolean.
  • True → generates arithmetic or geometric progression.
  • False → generates a list.

Examples

1. Arithmetic Progression
   The following macro fills range A1:A6 with an arithmetic progression starting at 0, step = 2, ending at 10.
   Result: 0, 2, 4, 6, 8, 10

Sub Progr1()
    Range("A1").Value = 0
    Range("A1").DataSeries Rowcol:=xlColumns, Type:=xlDataSeriesLinear, _
        Step:=2, Stop:=10
End Sub

2. Geometric Progression
   This macro fills range B1:B5 with a geometric progression starting at 1, multiplied by 3 each step.
   Result: 1, 3, 9, 27, 81

Sub Progr2()
    Range("B1").Value = 1
    Range("B1:B5").DataSeries Rowcol:=xlColumns, Type:=xlGrowth, Step:=3
End Sub

3. Date Progression
   This macro fills range C1:C4 with dates that increase by one month each step.
   Result: 01/01/2011, 01/02/2011, 01/03/2011, 01/04/2011

Sub Progr3()
    Range("C1").Value = "1/01/2011"
    Range("C1:C4").DataSeries Rowcol:=xlColumns, Type:=xlChronological, _
        Date:=xlMonth
End Sub

The Range object has a large collection of methods, giving developers the ability to program a wide variety of actions—from copying a range to the clipboard to solving nonlinear equations.

The most commonly used methods of the Range object include:

• Activate
• AddComment
• AutoFill
• AutoFit
• BorderAround
• Clear
• ClearComments
• ClearContents
• ClearFormats
• ClearNotes
• Copy
• CopyPicture
• Cut
• DataSeries
• Delete
• FillDown
• FillLeft
• FillRight
• FillUp
• Find
• FindNext
• FindPrevious
• FunctionWizard
• GoalSeek
• Insert
• PasteSpecial
• Replace
• Select
• Show

You can find detailed information about each method in the VBA Help system. Below are some basic examples.

Activating and Selecting a Range

• The Activate method makes a specific range the active one.
• The Select method highlights a range and returns a Selection object.

For example, the code below first activates cell A2, assigns it the value 1, then selects the range A3:A4 and assigns the value 3 to the selected cells:

Range("A2").Activate
ActiveCell.Value = 1
Range("A3:A4").Select
Selection.Value = 3

Automatically Adjusting Range Size to Fit Data

The AutoFit method automatically adjusts column width and row height to fit the entered data.

The following example demonstrates how to use AutoFit when creating the header row of a report table:

Sub DemoAutoFit()
    Range("A1").Value = "June"
    Range("B1").Value = "July"
    Range("C1").Value = "August"
    Columns("A:C").AutoFit   
    Range("D1").Value = "Total Sales Volume"
    Range("D1").Columns.AutoFit
End Sub

Filling a Range with a Single Value

• FillDown – fills a range from top to bottom, copying the values from the top row into all other cells of the range.
• FillUp – fills a range from bottom to top, copying the values from the bottom row.
• FillLeft – fills a range from right to left, using the values from the rightmost column.
• FillRight – fills a range from left to right, using the values from the leftmost column.

Example: The following instruction copies the value from cell A10 into every cell in the range A1:A9:

Range("A1:A10").FillUp

Adding Borders Around a Range

The BorderAround method applies a border around a range.

BorderAround(LineStyle, Weight, ColorIndex, Color)

• LineStyle (optional) – specifies the line style. Possible constants:
  xlContinuous, xlDash, xlDashDot, xlDashDotDot, xlDot, xlDouble, xlLineStyleNone, xlSlantDashDot.
• Weight (optional) – specifies the thickness. Possible constants:
  xlHairline, xlMedium, xlThick, xlThin.
• ColorIndex (optional) – specifies the color from the current palette. Constants:
  xlColorIndexAutomatic, xlColorIndexNone.
• Color (optional) – specifies the color using the RGB model.

Example: The following instruction applies a thick double green border around the range A1:B2:

Range("A1:B2").BorderAround LineStyle:=xlDouble, Weight:=xlThick, _
    Color:=RGB(0, 255, 0)

Clearing a Range

• Clear – clears everything (content, formatting, comments, notes).
• ClearComments – clears only comments.
• ClearContents – clears only the values and formulas (but keeps formatting).
• ClearFormats – clears only formatting.
• ClearNotes – clears notes.

Example: The following instruction clears the range A1:G37:

Range("A1:G37").Clear

Copying, Cutting, and Deleting Data

• Copy(Destination) – copies the range to another range or to the clipboard.
  • If Destination is omitted → copied to the clipboard.

Example: Copy range A1:D4 to E5:H8 on Sheet2:

Range("A1:D4").Copy Worksheets("Sheet2").Range("E5")

• Cut(Destination) – cuts (copies + deletes) the range to another range or to the clipboard.

Example: Cut A1:D4 from Sheet1 to the clipboard:

Worksheets("Sheet1").Range("A1:D4").Cut

• Delete – deletes a range.

Example: Delete the third row of the active worksheet:

Rows(3).Delete

Paste Special

The PasteSpecial method pastes data from the clipboard with special options.

expression.PasteSpecial(Paste, Operation, SkipBlanks, Transpose)

• expression – a Range object that specifies the top-left cell of the paste area.
• Paste (optional) – specifies what to paste. Constants include:
  xlPasteAll, xlPasteAllExceptBorders, xlPasteColumnWidths, xlPasteComments, xlPasteFormats, xlPasteFormulas, xlPasteFormulasAndNumberFormats, xlPasteValidation, xlPasteValues, xlPasteValuesAndNumberFormats.
• Operation (optional) – specifies how the pasted data combines with existing data. Constants:
  xlPasteSpecialOperationAdd, xlPasteSpecialOperationDivide, xlPasteSpecialOperationMultiply, xlPasteSpecialOperationNone, xlPasteSpecialOperationSubtract.
• SkipBlanks (optional) – Boolean. If True, blanks in the source range are ignored.
• Transpose (optional) – Boolean. If True, transposes rows and columns.

Example 1: Add values from C1:C5 to existing values in D1:D5 on Sheet1:

With Worksheets("Sheet1")
    .Range("C1:C5").Copy
    .Range("D1:D5").PasteSpecial Operation:=xlPasteSpecialOperationAdd
End With

Example 2: Same as above, but paste starting at the top-left cell D1:

With Worksheets("Sheet1")
    .Range("C1:C5").Copy
    .Range("D1").PasteSpecial Operation:=xlPasteSpecialOperationAdd
End With

Example 3: Copy only values from A1:C1 and paste them into A5:C5:

Range("A1:C1").Copy
Range("A5").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone

Pasting a Range with Transposition

The PasteSpecial method allows you to paste a copied range with transposed orientation (rows become columns, and columns become rows) by setting the parameter Transpose = True.

Example: The following code copies the values from range A1:C2 and pastes them transposed into the range E1:F3:

Sub Transp()
    Range("A1:C2").Copy
    Range("E1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
        Transpose:=True
End Sub

Removing Selection After Paste Special

When data is copied or pasted into the clipboard, the source range remains highlighted, even after a PasteSpecial operation.
To clear this selection, set the Application.CutCopyMode property to False.

Example:

Range("C1:C5").Copy
Range("D1").PasteSpecial Operation:=xlPasteSpecialOperationAdd
Application.CutCopyMode = False

Inserting Cells, Rows, or Columns

The Insert method of the Range object adds a new cell, row, or column to a worksheet.

Insert(Shift, CopyOrigin)

• Shift (optional) – specifies how existing cells should be shifted. Possible values:
  • xlShiftToRight – shifts existing cells to the right.
  • xlShiftDown – shifts existing cells down.
• CopyOrigin (optional) – specifies how formatting and data sources should be copied.

Examples:

• Insert a new row before row 4:

Rows(4).Insert

• Insert a new cell to the left of G9, shifting existing cells to the right:

Range("G9").Insert Shift:=xlShiftToRight

On the active worksheet, one cell is always active (highlighted with a black border). You can move between cells using either the mouse or the arrow keys. Each cell on the active worksheet is identified by its address (or cell reference), which consists of the column letter and the row number, for example, A1. This type of referencing is called the A1 style.

MS Excel also supports another referencing system, known as the R1C1 style, where both rows and columns are numbered. In this system, for instance, the active cell with the address R4C3 means “row 4, column 3.” This style is often used when writing VBA programs.

To change the referencing style, go to the File tab, select Options, and in the Excel Options window choose Formulas on the left. Then, in the Working with formulas section, check or uncheck the box R1C1 reference style.

Another way to reference a cell is by its name.

 

The name or address of the active cell is shown in the Name Box (located on the left edge of the formula bar). To assign a name to a cell or range, select the desired range, go to the Formulas tab on the Ribbon, and in the Defined Names group choose Define Name. When creating names, keep in mind:

• Names must begin with a letter or an underscore (_).
• Instead of spaces or hyphens (-), use underscores (_) or periods (.).
• Names should be short and must not resemble cell references like A1 or R1C1.

A cell on an inactive worksheet is identified by the sheet name followed by the cell address, for example: Sheet2!A1 (note that the exclamation mark is required). However, referencing by name is absolute, meaning when you refer to a named cell on another sheet, you don’t need to specify the sheet name.

A cell in an Excel worksheet can contain different types of information: text, numbers, or formulas. In addition, each cell can be formatted independently, and formatting options do not affect the actual content of the cell.

When entering data, Excel automatically recognizes the data type. Input is always placed in the active cell. As soon as you type a character, the content immediately appears in the formula bar, along with three buttons used for handling cell content.

To confirm data entry, press Enter, click the checkmark button in the formula bar, or press a cursor-control key.

If the entered text is longer than the cell width, it will either spill over into adjacent empty cells or be cut off at the right edge.

The content displayed in the cell may differ from what is shown on the screen—the actual value is always fully represented in the formula bar.

To edit cell content, activate the cell and press F2, or click in the formula bar.

If you don’t want Excel to automatically assign a format to your entry, type an apostrophe (‘) before the data.