VBA Worksheet Manipulations

Select a specific range of cells

Select a specified range of cells using VBA code, then save that range to a variable

    Dim myRange As Range
    
    ' select a specific range of cells in current workbook sheet
    Application.Goto Range("E5:J10")
    
    ' to be more specific, we can select a range on a specific workbook sheet
    Application.Goto ActiveWorkbook.Sheets("Sheet2").Range("C5:G10")

    ' we can also store value of range in a variable
    Set myRange = ActiveWorkbook.Sheets("Sheet2").Range("CA5:G10")
    
    ' show range in message box
    MsgBox (myRange.Address)

Some VBA vocabulary:

Dim declares variables and allocates storage space.

Range Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range. The following properties and methods for returning a Range object include:

• Range property
• Cells property
• Range and Cells
• Offset property
• Union method

Workbook Represents a Microsoft Excel workbook. The Workbook object is a member of the Workbooks collection. The Workbooks collection contains all the Workbook objects currently open in Microsoft Excel.

ActiveWorkbook property returns a Workbook object that represents the workbook in the active window (the window on top). Read-only. Returns Nothing if there are no windows open or if either the Info window or the Clipboard window is the active window.

Application.Goto Method selects any range or Visual Basic procedure in any workbook, and activates that workbook if it’s not already active.

msgBox function that displays a message in a dialog box, waits for the user to click a button, and returns an Integer indicating which button the user clicked.

Determine last used cell in column

Determine last row used in column "A". Show us the Row number, the value of this cell and address of last row.

    Dim lastRowNumber As Long
    Dim lastRowString As String
    Dim lastRowAddress As String
         
    With ActiveSheet
        lastRowNumber = .Cells(.Rows.count, "A").End(xlUp).row
        lastRowString = .Cells(Rows.count, 1).End(xlUp).Value
        lastRowAddress = .Cells(Rows.count, 1).End(xlUp).Address
    End With
    
    MsgBox (" Number of last row: " & lastRowNumber & _
            " Value of Last Row: " & lastRowString & _
            "; Cell of Last Row: " & lastRowAddress)

Range.End Returns a Range object that represents the cell at the end of the region that contains the source range. Equivalent to pressing END+UP ARROW, END+DOWN ARROW, END+LEFT ARROW, or END+RIGHT ARROW. Read-only Range object. To specify direction: xlDown, xlToLeft, xlToRight, xlUp

Copy and Paste contents of Cells

Copy contents of cells to a variable, then copy to new location.

    Dim mySource As Variant
    
    ' store content of range in a variable
    mySource = Sheets("Sheet2").Range("A2:A10").Value
    
    ' copy contents to a new location
    Sheets("Sheet2").Range("D2:F10").Value = mySource

Variant The Variant data type is the data type for all variables that are not explicitly declared as some other type (using statements such as Dim, Private, Public, or Static). The Variant data type has no type-declaration character.

Populate each cell in a range

Copy the the cell address for each cell in a range of cells

Sub CellAddressToContent()

    Dim rng As Range
    Dim row As Range
    Dim cell As Range
    Set rng = Range("E5:J10")
    
    'iterate through rows
    For Each row In rng.Rows
        
        'iterate through cells in row row
        For Each cell In row.Cells
            ' copy the value of the address into each cell
            cell = cell.Address()
            
      Next cell
    Next row    
End Sub

For Each - Repeats a group of statements for each element in a collection.

Address() returns a text representation of a cell address.

Write from Excel to Microsoft Word

Copy a cell from Excel to Microsoft Word

If you want to run code in one Microsoft Office application that works with the objects in another application, follow these steps.

1. Set a reference to the other application's type library in the References dialog box (Tools menu). Then, the objects, properties, and methods will appear in the Object Browser and the syntax will be checked at compile time. You can also get context-sensitive Help on them.

2. Declare object variables that will refer to the objects in the other application as specific types. Qualify each type with the name of the application that is supplying the object.

    ' Do not  forget to add references to Microsoft Word Object Library:
    ' Tool > References > Microsoft Word Object Library
    
    Dim WApp As Word.Application
    Dim WDoc As Word.Document

    Set WApp = CreateObject("word.application")
    WApp.Visible = True
    
    Set WDoc = WApp.Documents.Add
    
    WDoc.Content.InsertAfter Range("A1") & " some phrase"
    WDoc.Content.InsertAfter vbNewLine
    WDoc.Content.InsertAfter Range("A1") & " another phrase"