VBA/Excel/Access/Word/Excel/Range Reference — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
|
Текущая версия на 12:47, 26 мая 2010
Содержание
- 1 Activate the Range
- 2 Noncontiguous ranges
- 3 Reference a range by name
- 4 Refer to an entire column
- 5 Refer to an entire row
- 6 Refer to a range outside the active sheet by qualifying the range reference with a worksheet name from the active workbook
- 7 Refer to the B2 cell in DataInput, while another workbook is active
- 8 returns a range object that represents the last non-empty cell in the same row
- 9 Select a range A1:A3
- 10 Select a range by using the ActiveCell
- 11 Select Last Cell
- 12 Specifying Individual Cells with the Cells Property
- 13 The Offset method can also use negative arguments. A negative row offset refers to a row above the range. A negative column offset refers to a column to the left of the range.
- 14 To refer to a range in a different workbook
- 15 Use 0 as one or both of the arguments for Offset: expression refers to cell A1
- 16 Use ActiveCell.End to select a range
- 17 Use disgonal cells to select a range
- 18 Use the cell reference directly
- 19 Using the End Property to Navigate within a Worksheet
- 20 Using the Range Property to Refer to Groups of Cells
Activate the Range
Sub selectRange()
Range("A1:A3").Activate
End Sub
Noncontiguous ranges
Sub non()
range("A1:B8,D9:G16").Select
End Sub
Reference a range by name
Sub rangeName()
range("PriceList").Select
End Sub
Refer to an entire column
Sub column()
range("D:D").Select
End Sub
Refer to an entire row
Sub entire()
range("3:3").Select
End Sub
Refer to a range outside the active sheet by qualifying the range reference with a worksheet name from the active workbook
Sub sheetRange()
Worksheets("Sheet1").range ("A1:C5")
End Sub
Refer to the B2 cell in DataInput, while another workbook is active
Sub ref3()
Workbooks("Sales.xls").Worksheets("DataInput").Range("B2")
End Sub
returns a range object that represents the last non-empty cell in the same row
Function GetLastCellInRow(rg As Range) As Range
Dim lMaxColumns As Long
lMaxColumns = ThisWorkbook.Worksheets(1).Columns.Count
If IsEmpty(rg.Parent.Cells(rg.Row, lMaxColumns)) Then
Set GetLastCellInRow = _
rg.Parent.Cells(rg.Row, lMaxColumns).End(xlToLeft)
Else
Set GetLastCellInRow = rg.Parent.Cells(rg.Row, lMaxColumns)
End If
End Function
Select a range A1:A3
Sub selectRange()
Range("A1:A3").Select
End Sub
Select a range by using the ActiveCell
Sub MyMacro
ActiveCell.Range("A1:C1").Select
End Sub
Select Last Cell
Public Sub SelectLastCell()
Dim aRange As Range
Dim LastRow As Integer
Dim lastColumn As Integer
Set aRange = Range("A1").SpecialCells(xlCellTypeLastCell)
LastRow = aRange.Row
lastColumn = aRange.Column
MsgBox lastColumn
End Sub
Specifying Individual Cells with the Cells Property
Sub UsingCells()
Dim rg As Range
Dim nRow As Integer
Dim nColumn As Integer
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets(1)
For nRow = 1 To 10
For nColumn = 1 To 10
Set rg = ws.Cells(nRow, nColumn)
rg.Value = rg.Address
Next
Next
Set rg = Nothing
Set ws = Nothing
End Sub
The Offset method can also use negative arguments. A negative row offset refers to a row above the range. A negative column offset refers to a column to the left of the range.
Sub offsetNe()
range("C2").offset(-1, -2).Select
End Sub
To refer to a range in a different workbook
Sub bookRange()
Workbooks("Budget.xlsx").Worksheets("Sheet1").range ("A1:C5 ")
End Sub
Use 0 as one or both of the arguments for Offset: expression refers to cell A1
Sub zero()
range("A1").offset(0, 0).Select
End Sub
Use ActiveCell.End to select a range
Sub ColumnSelect()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub
Use disgonal cells to select a range
Sub Diagonal()
Range("A10", "D1").Select
End Sub
Use the cell reference directly
Sub SelectIt()
[A1:A10,C1:C10,E1:E10].Select
End Sub
Sub ExperimentWithEnd()
Dim ws As Worksheet
Dim rg As Range
Set ws = ThisWorkbook.Worksheets(1)
Set rg = ws.Cells(1, 1)
ws.Cells(1, 8).Value = "rg.address = " & rg.Address
ws.Cells(2, 8).Value = "rg.End(xlDown).Address = " & rg.End(xlDown).Address
ws.Cells(3, 8).Value = "rg.End(xlDown).End(xlDown).Address = " & rg.End(xlDown).End(xlDown).Address
ws.Cells(4, 8).Value = "rg.End(xlToRight).Address = " & rg.End(xlToRight).Address
Set rg = Nothing
Set ws = Nothing
End Sub
Using the Range Property to Refer to Groups of Cells
Sub UsingRange()
Dim ws As Worksheet
Dim rg As Range
Set ws = ThisWorkbook.Worksheets(1)
Set rg = ws.Range(ws.Cells(1, 1), ws.Cells(10, 10))
rg.Value = 1
Set rg = ws.Range("D4", "E5")
rg.Font.Bold = True
ws.Range("A1:B2").HorizontalAlignment = xlLeft
Set rg = Nothing
Set ws = Nothing
End Sub