VBA/Excel/Access/Word/Excel/Range

Adding Clickable Sorting to Worksheet Lists

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

Sub cellAddress()

   MsgBox range(cells(1, 1), cells(5, 5)).Address

End Sub

</source>
   
  

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

   <source lang="vb">

Sub valueDemo()

    Worksheets("Sales").range("D10").value = range("C1").value

End Sub

</source>
   
  

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

   <source lang="vb">

Sub valueDemo2()

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

End Sub

</source>
   
  

Building the Table

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

Sub changeMain()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub copyDemo()

    range("A1:B3").Copy

End Sub

</source>
   
  

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

   <source lang="vb">

Sub count()

   MsgBox range("A1:C3").count

End Sub

</source>
   
  

Count the blank elements in a range

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

Sub deleteLeft()

   range("C6:C10").delete xlToLeft

End Sub

</source>
   
  

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

   <source lang="vb">

Sub valueDemo()

   MsgBox Worksheets("Sheet1").range("A1").value

End Sub

</source>
   
  

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

   <source lang="vb">

Sub offset()

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

End Sub

</source>
   
  

Find in a range

   <source lang="vb">

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

</source>
   
  

HasFormula property

   <source lang="vb">

Sub hasfor()

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

End Sub

</source>
   
  

Highlights selected range

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

Sub columnRange()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub rowRange()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub rangeDemo()

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

End Sub

</source>
   
  

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

   <source lang="vb">

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

Read a range from InputBox

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

Sub main()

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

End Sub

</source>
   
  

Returns the type of a range in an area

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

Sub row()

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

End Sub

</source>
   
  

Select a range and activate another

   <source lang="vb">

Public Sub SelectAndActivate()

 Range("B3:E10").Select
 Range("C5").Activate

End Sub

</source>
   
  

Show Edit Ranges

   <source lang="vb">

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

</source>
   
  

Sum the elements in a range

   <source lang="vb">

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

</source>
   
  

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

   <source lang="vb">

Sub column()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub text()

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

End Sub

</source>
   
  

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

   <source lang="vb">

Sub valueDemo()

    Range("C10").Value = "Florence"

End Sub

</source>
   
  

Use object variables to represent the ranges

   <source lang="vb">

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

</source>
   
  

Value is the default property for a Range object.

   <source lang="vb">

Sub def()

   Worksheets("Sheet1").range("A1").value = 75
   Worksheets("Sheet1").range("A1") = 75

End Sub

</source>