RangeRange property and shortcut references
Application.Range("B2")Range("B2")Range("A1:D10")Range("A1:A10, C1:C10, E1:E10")Range("A1", "D10")Range("Name")Range("A1", Range("Name"))'Shortcut:[B2][A1:D10][A1:A10, C1:C10][Name]
Ranges on Inactive Worksheets
Workbooks("1.xlsx").Worksheets("Sheet1").Range("A1")With Workbooks("1.xlsx").Worksheets("Sheet1").Range.SelectEnd With
Range property of a range object
Range("B2").Range("A1") '=Range("B2")
CellsCells Property
ActiveSheet.CellsApplication.CellsCellsCells.Item(2,2)Cells.Item(2, "B")Cells(2,2)Cells(2, "B")
Cells Used in Range
Range(Cells(1,1), Cells(10, 5))
More on the Cells Property of the Range Object
Range("D10").Cells(2,3) '=Range("D10")(2,3) '=[D10].Cells(2,3)
Single-Parameter Range Reference
Range(“D10:E11")(2) '=E11Range(“D10:E11")(7) '=D13
Offset Property
Range("A1").Offset(0, 0)
Resize Property
Range("A1: B10").Resize(1,2)Range("A1: B10").Resize(, 2)
SpecialCells MethodLast Cell
'last row & column in the worksheet:Set rngLast = Range("A1").SpecialCells(xlCellTypeLastCell)lLastRow = rngLast.RowlLastCol = rngLast.Column'A more complex one:Sub GetRealLastCell()Dim lRealLastRow As LongDim lRealLastColumn As Long'Get bottom right corner of cells with dataRange("A1").SelectOn Error Resume NextlRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).RowlRealLastColumn = Cells.Fin("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).ColumnCells(lRealLastRow, lRealLastColumn).SelectEnd Sub'eg: Delete Unused Formats:Sub DeleteUnusedFormats()Dim lLastRow as Long, lLastColumn As LongDim lRealLastRow As Long, lRealLastColumn as Long'Delete from used range rows & columns that have no data'Delete end of used range including empty formatted cellsWith Range("A1").SpecialCells(xlCellTypeLastCell)lLastRow = .RowlLastColumn = .ColumnEnd With'Find end of cells with dataOn Error Resume NextlRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious).RowlRealLastColumn = Cells.Fin("*", Range("A1"), xlFormulas, , xlByColumns, xlPrevious).ColumnCells(lRealLastRow, lRealLastColumn).Select'If used range exceeds data, delete unused rows & columnsIf lRealLastRow < lLastRow ThenRange(Cells(lRealLastRow + 1, 1), Cells(lLastRow, 1)).EntireRow.DeleteEnd IfIf lRealLastColumn < lLastColllumn ThenRange(Cells(1, lRealLastColumn + 1,), Cells(1, lLastColumn)).EntireColumn.DeleteEnd IfActiveSheet.UsedRange 'Resets LastCell
Deleting Numbers
On Error Resume NextCells.SpecialCells(xlCellTypeConstants, xlNumbers).ClearContents'to keep date:On Error Resume NextFor Each rng In Cells.SpecialCells(xlCellTypeConstants, xlNumbers)If Not IsDate(rng.Value) Then rng.ClearContentsNext rng
CurrentRegion Property
Range("name").CurrentRegion.Select
End Property
Range("A1").End(xlDown)Range("A1").End(xlToRight)
Referring to Ranges with End
Range("B3", Range("B3").End(xlToRight).End(xlDown)).Select
Summing a Range
With ActiveCellSet rng = Range(.Offset(1), .Offset(1).End(xlDown)).Formula = "=SUM(" * rng.Address(RowAbsolute:=False, ColumnAbsolute:=False) & ")".Copy Destination:=Range(.Cells(1), .Offset(1).End(xlToRight).Offset(-1))End With
Columns and Rows Properties
Rows.Countrng.Rows
Areas
For Each rng in Range("name").Areas//Note: .Columns.Count gives the first continus area's count, same as Rows.Count
Union and Intersect Methods
Union(rng1, rng2)Intersect(rng1, rng2)//eg: Prevent a user from changing data in particular range, for example, B10:F20 + H10:L20Private Sub Worksheet_SelectionChange(ByVal Target as Range)Dim rngForbidden As RangeSet rngForbidden = Union(Range("B10:F20"), Range("H10:L20")If Intersect(Target, rngForbidden) Is Nothing Then Exit SubRange("A1").SelectMsgBox "You cannot select cells in ..."End Sub
Empty Cells
IsEmpty(ActiveCell.Value)
Transferring Values between Arrays and Ranges
'eg1:Dim vSalesData As VariantDim vaDiscount As VariantvSalsesData = Range().ValueRedim vaDiscount(1 To Ubound(vSalesData, 1), 1 To 1)Range().Value = vaDiscount'or, change toRiDim vaDiscount(1 To Ubound(vSalesData,1))Range().Value = WorkSheetFunction.Transpose(vaDiscount)
Deleting Rows
The fast way to delete certain rows may be techniques with AutoFilter
Sub DeleteRows3()Dim lLastRow As LongDim rng As RangeDim rngDelete As Range'FreezeApplicaiton.ScreenUpdating = False'Insert dummy row for dummy field name? why?Rows(1).Insert'Insert dummy field nameRange("C1").Value = "Temp"With ActiveSheet'Reset Last Cell.UsedRange'Determine last rowlLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row'Set rng to the C column data rowsSet rng = Range("C1", Cells(lLastRow, "C"))'Filter the C column to show only the data to be deletedrng.AutoFilter Field:=1, Criterial:="Mangoes"'Get referencce to the visible cells, including dummy field nameSet rngDelete = rng.SpecialCells9xlCellTypeVisible)'Turn off AutoFilterrng.AutoFilter'Delete rowsrngDelete.EntireRow.Delete'Reset the last cell.usedRangeEnd WithEnd Sub
因为有了梦想，我们才能拥有奋斗的目标，
