VBA/Excel/Access/Word/Excel/Range

Adding Clickable Sorting to Worksheet Lists

 
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
    Dim mnDirection As Integer 
    Dim mnColumn As Integer 
    If Target.Column < 5 And Target.Row = 1 Then 
        If Target.Column <> mnColumn Then 
            mnColumn = Target.Column 
            mnDirection = xlAscending 
        Else 
            If mnDirection = xlAscending Then 
                mnDirection = xlDescending 
            Else 
                mnDirection = xlAscending 
            End If 
        End If 
        Dim rg As Range 
        Set rg = Me.Cells(1, 1).CurrentRegion 
        rg.Sort Key1:=rg.Cells(1, mnColumn), _ 
                 Order1:=mnDirection, _ 
                 Header:=xlYes 
    
        Set rg = Nothing 
    End If 
End Sub

Address, a read-only property, displays the cell address for a Range object in absolute notation (a dollar sign before the column letter and before the row number).

 
Sub cellAddress()
    MsgBox range(cells(1, 1), cells(5, 5)).Address
End Sub

Assign the value in C1 in the active sheet to D10 in the sheet named Sales, in the active workbook

 
Sub valueDemo()
     Worksheets("Sales").range("D10").value = range("C1").value
End Sub

Assign the value of a property to a variable so it can be used in later code

 
Sub valueDemo2()
     OpeningStock = range("M100").value
     range("M100").value = 100
     ActiveSheet.printOut
     range("M100").value = OpeningStock
End Sub

Building the Table

 
Sub MultiplicationTable()
    " Build a multiplication table using a single formula
    Range("B1:M1").Value = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12)
    Range("B1:M1").Font.Bold = True
    Range("B1:M1").Copy
    Range("A2:A13").PasteSpecial Transpose:=True
    Range("B2:M13").FormulaR1C1 = "=RC1*R1C"
    Cells.EntireColumn.AutoFit
End Sub

Change the Value property for a range of any size: statement enters the number 123 into each cell in a range

 
Sub changeMain()
    Worksheets("Sheet1").range("A1:C3").value = 123
End Sub

Copies the contents of range A1:B3 to the clipboard:

 
Sub copyDemo()
     range("A1:B3").Copy
End Sub

Count property returns the number of cells in a range (all cells, not just the nonblank cells). It"s a read-only property.

 
Sub count()
    MsgBox range("A1:C3").count
End Sub

Count the blank elements in a range

 

Public Sub Array3()
  Dim Data As Variant, X As Variant
  Dim Message As String, i As Integer
  Data = Range("A1:A20").Value
  i = 1
  Do
    Debug.Print "Lower Bound = " & LBound(Data, i)
    Debug.Print "Upper Bound = " & UBound(Data, i)
    i = i + 1
    On Error Resume Next
    X = UBound(Data, i)
    If Err.Number <> 0 Then Exit Do
    On Error GoTo 0
  Loop
  Debug.Print "Number of Non Blank Elements = " & WorksheetFunction.CountA(Data)
End Sub

Deletes a range and then fills the resulting gap by shifting the other cells to the left:

 
Sub deleteLeft()
    range("C6:C10").delete xlToLeft
End Sub

Displays a message box that shows the value in cell A1 on Sheet1:

 
Sub valueDemo()
    MsgBox Worksheets("Sheet1").range("A1").value
End Sub

Expression refers to a cell one row below cell A1 and two columns to the right of cell A1: this refers to the cell commonly known as C2

 
Sub offset()
    range("A1").offset(1, 2).Select
End Sub

Find in a range

 
Public Sub FindIt()
  Dim aRange As Range
  
  Set aRange = Range("A1:A12").Find(what:="Jun",LookAt:=xlWhole, LookIn:=xlValues)
    
  If aRange Is Nothing Then
    MsgBox "Data not found"
    Exit Sub
  Else
    aRange.Resize(1, 3).Copy Destination:=Range("G1")
  End If
End Sub

HasFormula property

 
Sub hasfor()
    Dim FormulaTest As Boolean
    FormulaTest = range("A1:A2").hasFormula
End Sub

Highlights selected range

 
Sub GetRange()
   Dim Rng As Range
 
   On Error Resume Next
   Set Rng = Application.InputBox(prompt:="Enter range", Type:=8)
   If Rng Is Nothing Then
      MsgBox "Operation Cancelled"
   Else
      Rng.Select
   End If
End Sub

If the Range object consists of more than one cell, the Column property returns the column number of the first column in the range

 
Sub columnRange()
    MsgBox Sheets("Sheet1").range("A:F3").column
End Sub

If the Range object consists of more than one cell, the Row property returns the row number of the first row in the range.

 
Sub rowRange()
    MsgBox Sheets("Sheet1").range("A1:F3").row
End Sub

If the Range object is not in the active worksheet in the active workbook

 
Sub rangeDemo()
     Workbooks("Sales.xls").Worksheets("DataInput").Range("C10").Value = 10
End Sub

Modify multiple cells at once using a range reference (like A1:A2)

 
     Sub MyMacro()
         " Insert the text "Hello" in ten cells
         Range("A1:A10").Value = "Hello"
     End Sub

Read a range from InputBox

 
Public Sub SelectRange()
  Dim aRange As Range
      
  On Error Resume Next
  Set aRange = Application.InputBox(prompt:="Enter range", Type:=8)
  If aRange Is Nothing Then
    MsgBox "Operation Cancelled"
  Else
    aRange.Select
  End If
End Sub

read the Value property only for a single- cell Range object: statement generates an error

 
Sub main()
    MsgBox Worksheets("Sheet1").range("A1:C3").value
End Sub

Returns the type of a range in an area

 
Private Function AreaType(RangeArea As Range) As String
    Select Case True
        Case RangeArea.Count = 1
            AreaType = "Cell"
        Case RangeArea.Count = Cells.Count
            AreaType = "Worksheet"
        Case RangeArea.Rows.Count = Cells.Rows.Count
            AreaType = "Column"
        Case RangeArea.Columns.Count = Cells.Columns.Count
            AreaType = "Row"
        Case Else
            AreaType = "Block"
    End Select
End Function

Row property returns the row number of a single-cell range.

 
Sub row()
    MsgBox Sheets("Sheet1").range("F3").row
End Sub

Select a range and activate another

 
Public Sub SelectAndActivate()
  Range("B3:E10").Select
  Range("C5").Activate
End Sub

Show Edit Ranges

 
Sub ShowEditRanges()
    Dim ws As Worksheet, ual As UserAccessList, aer As AllowEditRange
    Set ws = ThisWorkbook.Sheets("Protection")
    For Each aer In ws.Protection.AllowEditRanges
        Debug.Print aer.Title, aer.Range.Address
    Next
End Sub

Sum the elements in a range

 
Public Sub Array1()
  Dim Data(10) As Integer
  Dim Message As String, i As Integer
  For i = LBound(Data) To UBound(Data)
    Data(i) = i
  Next i
  Debug.Print "Lower Bound = " & LBound(Data)
  Debug.Print "Upper Bound = " & UBound(Data)
  Debug.Print "Number of Elements = " & WorksheetFunction.Count(Data)
  Debug.Print "Sum of Elements = " & WorksheetFunction.Sum(Data)
End Sub

The Column property returns the column number of a single-cell range;

 
Sub column()
    MsgBox Sheets("Sheet1").range("F3").column
End Sub

The Text property returns a string that represents the text as displayed in a cell: the formatted value. The Text property is read-only.

 
Sub text()
    MsgBox Worksheets("Sheet1").range("A1").text
    MsgBox Worksheets("Sheet1").range("A1").value
End Sub

To enter the name Florence into cell C10, you assign the name to the Value property of the Range object

 
Sub valueDemo()
     Range("C10").Value = "Florence"
End Sub

Use object variables to represent the ranges

 
Sub CopyRange3()
    Set Rng1 = Workbooks("File1.xls").Sheets("Sheet1").range("A1")
    Set Rng2 = Workbooks("File2.xls").Sheets("Sheet2").range("A1")
    Rng1.Copy Rng2
End Sub

Value is the default property for a Range object.

 
Sub def()
    Worksheets("Sheet1").range("A1").value = 75
    Worksheets("Sheet1").range("A1") = 75
End Sub