Cách lựa chọn Cell/ranges bằng VB trong Excel. (2 người xem)

[SA_DQ]

How to select cells/ranges by using Visual Basic procedures in Excel

1 ActiveSheet.Cells(5, 4).Select
2 ActiveSheet.Range("D5").Select
 
3 Application.Goto ActiveWorkbook.Sheets("Sheet2").Cells(6, 5)
4 Application.Goto (ActiveWorkbook.Sheets("Sheet2").Range("E6"))
 
5 Sheets("Sheet2").Activate: ActiveSheet.Cells(6, 5).Select
6 Application.Goto Workbooks("Sh2.XLS").Sheets("Sheet1").Cells(6, 7)
 
7 Workbooks("Sh2.XLS").Sheets("Sheet1").Activate
ActiveSheet.Cells(6, 7).Select
9 ActiveSheet.Range(Cells(2, 3), Cells(9, 4)).Select
 
10 ActiveSheet.Range("C2:D9").Select
11 ActiveSheet.Range("C2", "D9").Select
 
12 Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("D3:E11")
13 Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("D3", "E11")
 
14 Sheets("Sheet3").Activate 
ActiveSheet.Range(Cells(3, 4), Cells(11, 5)).Select
 
16 Application.Goto Workbooks("Sh.XLS").Sheets("Sheet1").Range("E4:F12")
17 Workbooks("Sh.XLS").Sheets("Sheet1").Activate 
ActiveSheet.Range(Cells(4, 5), Cells(12, 6)).Select
 
18 Range("Test").Select
19 Application.Goto "Test"
20 Application.Goto Sheets("Sheet1").Range("Test")
 
21 Sheets("Sheet1").Activate 
Range("Test").Select
 
22 Application.Goto Workbooks("Sh2.XLS").Sheets("Sheet2").Range("Test")
23 Workbooks("SH2.XLS").Sheets("Sheet2").Activate
Range("Test").Select
 
24 ActiveCell.Offset(5, 4).Select
25 ActiveCell.Offset(-2, 3).Select
26 ActiveSheet.Cells(7, 3).Offset(5, 4).Select
 
27 ActiveSheet.Range("C7").Offset(5, 4).Select
28 ActiveSheet.Range("Test").Offset(4, 3).Select
29 Sheets("Sheet3").Activate 
ActiveSheet.Range("Test").Offset(4, 3).Select
 
31 Range("CSDL").Select 
32 Selection.Resize(Selection.Rows.Count + 5, Selection.Columns.Count).Select
 
33 Range("CSDL").Select 
Selection.Offset(4, 3).Resize(Selection.Rows.Count + 2, _ 
       Selection.Columns.Count + 1).Select
34 Application.Union(Range("Test"), Range("Sample")).Select
35 Set y = Application.Union(Range("Sheet1!A1:B2"), Range("Sheet1!C3:D4"))
 
[COLOR=red][B][I]‘Union method of application class failed:[/I][/B][/COLOR]
Set y = Application.Union(Range("Sheet1!A1:B2"), Range("Sheet2!C3:D4"))
 
37 Application.Intersect(Range("Test"), Range("Sample")).Select
 
38 ActiveSheet.Range("a1").End(xlDown).Select
39 ActiveSheet.Range("a1").End(xlDown).Offset(1,0).Select

. . . Còn tiếp

 

[SA_DQ]

Tiếp theo

How to select cells/ranges by using Visual Basic procedures in Excel

Option Explicit[b]

Sub SelectionResize()[/b]
13 [COLOR="Blue"]'. Neu ta Da Gan Vung $A$3:$C$7 Voi Ten La 'CSDL', Thi:[/COLOR]
Range("CSDL").Select
  Selection.Resize(Selection.Rows.Count + 5, _
      Selection.Columns.Count).Select
  MsgBox Selection.Address [COLOR="blue"]'$A$3:$C$12[/COLOR]
[B]End Sub[/B]

[B]Sub SelectionOffsetResize()[/B]
14 [COLOR="blue"]'. Neu ta Da Gan Vung $A$3:$C$7 Voi Ten La 'CSDL', Thi:[/COLOR]
  Range("CSDL").Select
  Selection.Offset(, 3).Resize(Selection.Rows.Count + 2, _
      Selection.Columns.Count + 1).Select
  MsgBox Selection.Address [COLOR="blue"]'$D$3:$G$9[/COLOR]
[B]End Sub[/B]

[B]Sub SelectionUnion()[/B]
15[COLOR="blue"] '. Neu Ta Gan Them Vung $B$1:$D$1 Co Ten 'TChuan, Thi:[/COLOR]
  Application.Union(Range("CSDL"), Range("TChuan")).Select
  MsgBox Selection.Address, , "1" [COLOR="blue"]'$A$3:$C$7,$B$1:$D$1[/COLOR]
150 [COLOR="blue"]'Cung Se Co Ket Qua Nhu Vay, Khi:[/COLOR]
 Dim Rng As Range
  Set Rng = Application.Union(Range("Select!$A$3:$C$7"), Range("Select!$B$1:$D$1"))
  MsgBox Rng.Address, , "2"
Set Rng = Nothing
151 [COLOR="blue"]' Se Xuat Hien Canh Bao Loi:'Method 'Union' of Object '_Application' failed _
, Neu Ta Viet:[/COLOR]
  Set Rng = Application.Union(Range("Select!A1:B2"), Range("Sheet2!C3:D4"))
[B]End Sub[/B]

[B]Sub SelectionIntersect()[/B]
16[COLOR="blue"] 'Khi Ta dung Phuong Thuc InterSect, Nhu Sau:[/COLOR]
  Application.Intersect(Range("TChuan"), Range("Select!A1:C5")).Select
  MsgBox Selection.Address '[COLOR="blue"]$B$1:$C$1[/COLOR]
[B]End Sub[/B]

[B]Sub SelectLastCellInContiguousColumn()[/B]
17 [COLOR="blue"]'Chon O Cuoi Trong Cot Co Du Lieu[/COLOR]
  ActiveSheet.Range("G1").End(xlDown).Select
  MsgBox Selection.Address, , "3" [COLOR="blue"]'$G$2[/COLOR]
  ActiveSheet.Range("B1").End(xlDown).Select
  MsgBox Selection.Address, , "4" [COLOR="blue"]'$B$3[/COLOR]
[B]End Sub[/B]

[B]Sub SelectCellBelowRangeOfContiguousCells()[/B]
18 [COLOR="blue"]'Chon O Ke Tiep Voi O Cuoi Trong Cot Co Du Lieu[/COLOR]
  ActiveSheet.Range("G1").End(xlDown).Offset(1, 0).Select
  MsgBox Selection.Address, , "5" [COLOR="blue"]'$G$3[/COLOR]
  ActiveSheet.Range("B1").End(xlDown).Offset(1, 0).Select
  MsgBox Selection.Address, , "6" [COLOR="blue"]'$B$4[/COLOR]
  ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Select
  MsgBox Selection.Address, , "7" [COLOR="blue"]'$A$3[/COLOR]
[B]End Sub[/B]

[B]Sub SelectRangeOfContiguousCellsInColumn()[/B]
19  ActiveSheet.Range("a1", ActiveSheet.Range("b1").End(xlDown)).Select
  MsgBox Selection.Address, , "8" [COLOR="blue"]'$A$1:$B$3[/COLOR]
  ActiveSheet.Range("G1:" & ActiveSheet.Range("G1"). _
        End(xlDown).Address).Select
  MsgBox Selection.Address, , "9" [COLOR="blue"]'$G$1:$G$2[/COLOR]
[B]End Sub[/B]

[B]Sub SelectUseRangeOfColumns()[/B]
20  ActiveSheet.Range("a1", ActiveSheet.Range("a65536").End(xlUp)).Select
  MsgBox Selection.Address, , "A" '$A$1:$A$7
  ActiveSheet.Range("a1:" & ActiveSheet.Range("G65536"). _
        End(xlUp).Address).Select
  MsgBox Selection.Address, , "B" '$A$1:$G$7
[B]End Sub[/B]

[B]Sub SelectRectangularRange()[/B]
21  ActiveSheet.Range("a1").CurrentRegion.Select
  MsgBox Selection.Address, , "C" [COLOR="blue"]'$A$1:$D$1[/COLOR]
  ActiveSheet.Range("a1", ActiveSheet.Range("a1"). _
      End(xlDown).End(xlToRight)).Select
  MsgBox Selection.Address, , "D" [COLOR="blue"]'$A$1:$E$3[/COLOR]          
  ActiveSheet.Range("a1:" & ActiveSheet.Range("B1").End(xlDown) _
        .End(xlToRight).Address).Select
  MsgBox Selection.Address, , "E" [COLOR="blue"]'$A$1:$E$3[/COLOR]

 Dim iCol As Integer, LRow As Long
  iCol = ActiveSheet.Range("a1").End(xlToRight).Column
  LRow = ActiveSheet.Cells(65536, iCol).End(xlUp).Row
  ActiveSheet.Range("a1", ActiveSheet.Cells(LRow, iCol)).Select
  MsgBox Selection.Address, , "F" '$A$1:$B$7
  iCol = ActiveSheet.Range("a3").End(xlToRight).Column
  [COLOR="blue"]' LRow = ActiveSheet.Cells(65536, iCol).End(xlUp).Row[/COLOR]
  ActiveSheet.Range("a1:" & ActiveSheet. _
        Cells(LRow, iCol).Address).Select
  MsgBox Selection.Address, , "G" '$A$1:$E$7
[B]End Sub[/B]

[B]Sub SelectMultipleNon_ContiguousColumnsOfVaryingLength()[/B]
22 Dim StartRng As String, EndRng As String
 Dim aRng As Range, bRng As Range

  StartRng = "A1": EndRng = "G1"
  Set aRng = Range(StartRng, Range(StartRng).End(xlDown))
  Set bRng = Range(EndRng, Range(EndRng).End(xlDown))
  Union(aRng, bRng).Select
  MsgBox Selection.Address, , "H" [COLOR="blue"]'$A$1:$A$3,$G$1:$G$2[/COLOR]
[B]End Sub[/B]

[B]Sub NotesOfSelect()[/B]
 On Error Resume Next
  Application.Goto Sheets("Select").Range( _
        Sheets("Select").Range(Sheets("Select").Cells(2, 3), _
  Sheets("Select").Cells(4, 5)))
  MsgBox Error$, , Err [COLOR="blue"]'Application-defined or object-defined error[/COLOR]
  Application.Goto Sheets("Select").Range("CSDL")
  MsgBox Selection.Address, , "J" [COLOR="blue"]'$A$3:$C$7[/COLOR]
  Application.Goto Sheets("Select").Range("CSDL", "Tchuan")
  MsgBox Selection.Address, , "K" [COLOR="blue"]'$A$1:$D$7[/COLOR]
  Application.Goto Sheets("Select").Range(Cells(2, 3), Cells(3, 2))
  MsgBox Selection.Address, , "L" [COLOR="blue"]'$B$2:$c$3[/COLOR]
[B]End Sub[/B]