в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:47:15Z

1 версия

Новая страница

==Activate the Range==



<source lang="vb">

Sub selectRange()
Range("A1:A3").Activate
End Sub
</source>

==Noncontiguous ranges==



<source lang="vb">

Sub non()
range("A1:B8,D9:G16").Select

End Sub
</source>

==Reference a range by name==



<source lang="vb">

Sub rangeName()
range("PriceList").Select

End Sub
</source>

==Refer to an entire column==



<source lang="vb">

Sub column()
range("D:D").Select

End Sub
</source>

==Refer to an entire row==



<source lang="vb">

Sub entire()
range("3:3").Select

End Sub
</source>

==Refer to a range outside the active sheet by qualifying the range reference with a worksheet name from the active workbook==



<source lang="vb">

Sub sheetRange()
Worksheets("Sheet1").range ("A1:C5")
End Sub
</source>

==Refer to the B2 cell in DataInput, while another workbook is active==



<source lang="vb">

Sub ref3()
Workbooks("Sales.xls").Worksheets("DataInput").Range("B2")
End Sub
</source>

==returns a range object that represents the last non-empty cell in the same row==



<source lang="vb">

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
</source>

==Select a range A1:A3==



<source lang="vb">

Sub selectRange()
Range("A1:A3").Select
End Sub
</source>

==Select a range by using the ActiveCell==



<source lang="vb">

Sub MyMacro
ActiveCell.Range("A1:C1").Select
End Sub
</source>

==Select Last Cell==



<source lang="vb">

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
</source>

==Specifying Individual Cells with the Cells Property==



<source lang="vb">

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
</source>

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



<source lang="vb">

Sub offsetNe()
range("C2").offset(-1, -2).Select
End Sub
</source>

==To refer to a range in a different workbook==



<source lang="vb">

Sub bookRange()
Workbooks("Budget.xlsx").Worksheets("Sheet1").range ("A1:C5 ")
End Sub
</source>

==Use 0 as one or both of the arguments for Offset: expression refers to cell A1==



<source lang="vb">

Sub zero()
range("A1").offset(0, 0).Select
End Sub
</source>

==Use ActiveCell.End to select a range==



<source lang="vb">

Sub ColumnSelect()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
End Sub
</source>

==Use disgonal cells to select a range==



<source lang="vb">

Sub Diagonal()
Range("A10", "D1").Select
End Sub
</source>

==Use the cell reference directly==



<source lang="vb">

Sub SelectIt()
[A1:A10,C1:C10,E1:E10].Select
End Sub
</source>

==Using the End Property to Navigate within a Worksheet==



<source lang="vb">

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
</source>

==Using the Range Property to Refer to Groups of Cells==



<source lang="vb">

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
</source>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Excel/Range Reference - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия