Cach chon /dai bng quy trinh Visual Basic trong Excel

D?ch tiu ? D?ch tiu ?
ID c?a bi: 291308 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny

Thng tin thm

Microsoft chi cung cp m hinh lp trinh minh hoa ma khng am bao di bt ky hinh thc nao du co quy inh ro rang hay ngu y. iu nay bao gm nhng khng gii han trong cac hinh thc bao hanh i vi tinh thng mai cung nh tinh thich hp cho mt muc ich cu th. Bai vit nay gia inh rng ban a quen vi ngn ng lp trinh ang c giai thich, va cng cu c s dung tao va g li quy trinh. Microsoft h tr cac ky s giai thich chc nng cua quy trinh c bit, nhng ho se khng sa i nhng m hinh nay cung cp chc nng c b sung hoc xy dng cac quy trinh nhm ap ng yu cu cu th cua ban. Cac m hinh trong bai vit nay bao gm phng phap Visual Basic c lit k trong bang sau.
   Phng thc             i s
   ------------------------------------------
   ang hoat ng           khng
                 rowIndex, columnIndex
   Application.Goto   reference, scroll
   Bu             rowOffset, columnOffset
   Dai               cell1
                      cell1, cell2
   Sa lai kich c             rowSize, columnSize
   Chon             khng
   Trang            chi muc (hoc sheetName)
   S lam vic          chi muc (hoc bookName)
   Kt thuc                hng dn
   Vung hin tai      khng
				
Cac m hinh trong bai vit nay s dung thuc tinh trong bang sau.
   Thuc tinh          S dung
   ---------------------------------------------------------------------
   ActiveSheet       chi inh trang ang hoat ng 
   ActiveWorkbook    chi inh s lam vic ang hoat ng
   Columns.Count     tinh s ct trong muc a chi inh
   Rows.Count        tinh s hang trong muc a chi inh
   Selection         tham khao dai  hin a chon
				

1: Cach Chon trong Trang tinh ang hoat ng

chon D5 trn trang tnh ang hoat ng, ban co th s dung mt trong hai mu sau:
ActiveSheet.Cells(5, 4).Select
				
-hoc-
ActiveSheet.Range("D5").Select
				

2: Cch Ch?n trn Trang tnh Khc trong Cng m?t S? lm vi?c

? ch?n E6 trn trang tnh khc trong cng m?t s? lm vi?c, b?n c th? s? d?ng m?t trong hai m?u sau:
Application.Goto ActiveWorkbook.Sheets("Sheet2").Cells(6, 5)
				
   -or-
				
Application.Goto (ActiveWorkbook.Sheets("Sheet2").Range("E6"))
				
Hoc, ban co th kich hoat trang tinh, va sau o s dung phng phap 1 phia trn chon :
Sheets("Sheet2").Activate
ActiveSheet.Cells(6, 5).Select
				

3: Cch ch?n trn Trang tnh trong S? lm vi?c Khc

? ch?n F7 trn trang tnh trong s? lm vi?c khc, b?n c th? s? d?ng m?t trong hai m?u sau:
Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Cells(7, 6)
				
-hoc-
Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("F7")
				
Hoc, ban co th kich hoat trang tinh va sau o s dung phng phap 1 phia trn chon :
Workbooks("BOOK2.XLS").Sheets("Sheet1").Activate
ActiveSheet.Cells(7, 6).Select
				

4: Cch Ch?n D?i trn Trang tnh ang ho?t ?ng

? ch?n d?i C2:D10 trn trang tnh ang ho?t ?ng, b?n c th? s? d?ng b?t k? m?u no trong nh?ng m?u sau:
ActiveSheet.Range(Cells(2, 3), Cells(10, 4)).Select
				
ActiveSheet.Range("C2:D10").Select
				
ActiveSheet.Range("C2", "D10").Select
				

5: Cch Ch?n D?i trn Trang tnh Khc trong Cng m?t S? lm vi?c

? ch?n d?i D3:E11 trn trang tnh khc trong cng m?t s? lm vi?c, b?n c th? s? d?ng m?t trong cc m?u sau:
Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("D3:E11")
				
Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("D3", "E11")
				
Hoc, ban co th kich hoat trang tinh, va sau o s dung phng phap 4 phia trn chon dai :
Sheets("Sheet3").Activate
ActiveSheet.Range(Cells(3, 4), Cells(11, 5)).Select
				

6: Cach chon Dai trn Trang tinh trong S lam vic khac

chon dai E4:F12 trn trang tinh trong s lam vic khac, ban co th s dung mt trong hai mu sau:
Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("E4:F12")
				
Application.Goto _
      Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("E4", "F12")
				
Hoc, ban co th kich hoat trang tinh, va sau o s dung phng phap 4 phia trn chon dai :
Workbooks("BOOK2.XLS").Sheets("Sheet1").Activate
   ActiveSheet.Range(Cells(4, 5), Cells(12, 6)).Select
				

7: Cach Chon Dai a t tn trn Trang tinh ang hoat ng

chon dai a t tn "Test" trn trang tinh ang hoat ng, ban co th s dung mt trong hai cach sau:
Range("Test").Select
				
Application.Goto "Test"
				

8: Cach Chon Dai a t tn trn Trang tinh Khac trong Cung mt S lam vic

chon dai a t tn "Test" trn trang tinh khac trong cung mt s lam vic, ban co th s dung mu sau:
Application.Goto Sheets("Sheet1").Range("Test")
				
Hoc, ban co th kich hoat trang tinh, va sau o s dung phng phap 7 phia trn chon dai a t tn:
Sheets("Sheet1").Activate
Range("Test").Select
				

9: Cach Chon Dai a t tn trn Trang tinh trong S lam vic Khac

chon dai a t tn "Test" trn trang tinh trong s lam vic khac, ban co th s dung mu sau:
Application.Goto _
   Workbooks("BOOK2.XLS").Sheets("Sheet2").Range("Test")
				
Hoc, ban co th kich hoat trang tinh, va sau o s dung phng phap 7 phia trn chon dai a t tn:
Workbooks("BOOK2.XLS").Sheets("Sheet2").Activate
Range("Test").Select
				

10: Cach Chon Lin quan n ang hoat ng

chon trong 5 hang bn di va 4 ct bn trai ang hoat ng, ban co th s dung mu sau:
ActiveCell.Offset(5, -4).Select
				
chon trong 2 hang bn trn va 3 ct bn phai ang hoat ng, ban co th s dung mu sau:
ActiveCell.Offset(-2, 3).Select
				
Chu y Li se xay ra nu ban chon "khng nm trong trang tinh." Mu u tin hin thi trn se tra lai li nu ang hoat ng nm trong ct t A n D, do vi?c di chuyn 4 ct bn trai se di chuyn ang hoat ng n ia chi khng hp l.

11: Cach Chon Lin quan n Khc (Khng ph?i ang hoat ng)

chon trong 5 hang bn d?i va 4 ct bn ph?i C7, ban co th s dung m?t trong hai mu sau:
ActiveSheet.Cells(7, 3).Offset(5, 4).Select
				
ActiveSheet.Range("C7").Offset(5, 4).Select
				

12: Cach Chon Dai Bu t Dai a Chi inh

chon dai co cung kich thc vi dai a t tn "Test" nhng a c chuyn 4 hang xung di va 3 ct sang phai, ban co th s dung mu sau:
ActiveSheet.Range("Test").Offset(4, 3).Select
				
Nu dai a t tn nm trn trang tinh khac (khng phai trang tinh ang hoat ng), trc tin phai kich hoat trang tinh o, va sau o chon dai bng mu sau:
Sheets("Sheet3").Activate
ActiveSheet.Range("Test").Offset(4, 3).Select
				

13: Cach Chon Dai a Chi inh va Sa lai kich c Dai c chon

chon dai a t tn "Database" va sau o m rng dai c chon thm 5 hang, ban co th s dung mu sau:
Range("Database").Select
Selection.Resize(Selection.Rows.Count + 5, _
   Selection.Columns.Count).Select
				

14: Cach chon Dai a Chi inh, Bu Dai va Sau o Sa lai kich c

chon dai 4 hang bn di va 3 ct bn phai dai a t tn "Database" va bao gm thm 2 hang va 1 ct, nhiu hn dai a t tn, ban co th s dung mu sau:
Range("Database").Select
Selection.Offset(4, 3).Resize(Selection.Rows.Count + 2, _
   Selection.Columns.Count + 1).Select
				

15: Cach Chon Tp hp Hai hay Nhiu Dai a Chi inh

chon tp hp (nghia la vung chon c kt hp) hoc hai dai a t tn "Test" va "Sample," ban co th s dung mu sau:
Application.Union(Range("Test"), Range("Sample")).Select
				
Lu y rng ca hai dai phai trn cung mt trang tinh mu nay hoat ng. Cung lu y rng phng phap Tp hp khng hoat ng trn tt ca cac trang. Vi du: dong nay se hoat ng tt
Set y = Application.Union(Range("Sheet1!A1:B2"), Range("Sheet1!C3:D4"))
				
nhng dong nay
Set y = Application.Union(Range("Sheet1!A1:B2"), Range("Sheet2!C3:D4"))
				
se tra v thng bao li:
Phng phap tp hp cua lp ng dung khng thanh cng

16: Cach Chon Giao im cua Hai hay Nhiu Dai a Chi inh

chon giao im cua hai dai a t tn "Test" va "Sample," ban co th s dung mu sau:
Application.Intersect(Range("Test"), Range("Sample")).Select
				
Lu y rng ca hai dai phai trn cung mt trang tinh mu nay hoat ng.



Cac mu t 17-21 trong bai vit nay cp n b d liu mu sau. Mi mu cho bit mt dai trong d liu mu se c chon.
   1: Tn    B1: Hang ban    C1: S lng
   A2: a       B2: $10      C2: 5
   A3: b       B3:          C3: 10
   A4: c       B4: $10      C4: 5
   A5:         B5:          C5:
   A6: Tng   B6: $20      C6: 20
				

17: Cach Chon cui cung trong Ct D liu Tip giap

chon cui cung trong ct tip giap, s dung mu sau:
ActiveSheet.Range("a1").End(xlDown).Select
				
Khi ma nay c s dung cung bang mu, A4 se c chon.

18: Cach Chon Trng Cui Ct D liu Tip giap

chon bn di dai tip giap, s dung mu sau:
ActiveSheet.Range("a1").End(xlDown).Offset(1,0).Select
				
Khi ma nay c s dung cung bang mu, A5 se c chon.

19: Cach Chon Toan b Dai Tip giap trong mt Ct

chon dai tip giap trong mt ct, s dung mt trong nhng mu sau:
ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).Select
				
   -hoc-
				
ActiveSheet.Range("a1:" & ActiveSheet.Range("a1"). _
      End(xlDown).Address).Select
				
Khi ma nay c s dung cung bang mu, cac t A1 n A4 se c chon.

20: Cach Chon Toan b Dai Khng Tip giap trong mt Ct

chon dai khng tip giap, s dung mt trong nhng mu sau:
ActiveSheet.Range("a1",ActiveSheet.Range("a65536").End(xlUp)).Select
				
   -hoc-
				
ActiveSheet.Range("a1:" & ActiveSheet.Range("a65536"). _
   End(xlUp).Address).Select
				
Khi ma nay c s dung cung bang mu, cac t A1 n A6 se c chon.

21: Cach Chon Vung Dai Hinh ch nht

chon vung dai hinh ch nht xung quanh mt , s dung phng phap CurrentRegion. Dai c chon bng phng phap CurrentRegion la mt vung c gii han bi moi tp hp hang va ct trng. Sau y la vi du v cach s dung phng phap CurrentRegion:
ActiveSheet.Range("a1").CurrentRegion.Select
				
Ma nay se chon cac t A1 n C4. Cac mu khac chon dai tng t c lit k di y:
ActiveSheet.Range("a1", _
   ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Select
				
   -hoc-
				
ActiveSheet.Range("a1:" & _
   ActiveSheet.Range("a1").End(xlDown).End(xlToRight).Address).Select
				
Trong mt s trng hp, ban co th chon cac t A1 n C6. Trong mu nay, phng phap CurrentRegion se khng hoat ng bi dong trng trn Hang 5. Cac mu sau se chon tt ca cac :
lastCol = ActiveSheet.Range("a1").End(xlToRight).Column
lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow, lastCol)).Select
				
    -hoc-
				
lastCol = ActiveSheet.Range("a1").End(xlToRight).Column
lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row
ActiveSheet.Range("a1:" & _
   ActiveSheet.Cells(lastRow, lastCol).Address).Select
				

22. Cach Chon Nhiu Ct Khng Tip giap co Chiu dai Khac nhau

chon nhiu ct khng tip giap co chiu dai khac nhau, s dung bang mu va mu macro sau:
   1: 1   B1: 1   C1: 1  D1: 1
   A2: 2   B2: 2   C2: 2  D2: 2
   A3: 3   B3: 3   C3: 3  D3: 3
   A4:    B4: 4   C4: 4  D4: 4
   A5:    B5: 5   C5: 5  D5:
   A6:    B6:    C6: 6  D6:
				
StartRange = "A1"
EndRange = "C1"
Set a = Range(StartRange, Range(StartRange).End(xlDown))
Set b = Range(EndRange, Range(EndRange).End(xlDown))
Union(a,b).Select
				
Khi ma nay c s dung cung bang mu, cac A1:A3 va C1:C6 se c chon.

CAC LU Y VI MU

  • Thuc tinh Kich hoat Trang co th lun bi bo sot, bi no se bi n nu khng t tn trang cu th. Vi du, thay vi
    ActiveSheet.Range("D5").Select
    						
    ban co th s dung:
    Range("D5").Select
    						
  • Thuc tinh Kich hoat S lam vic cung co th lun bi sot. S lam vic ang hoat ng se n tr khi s lam vic cu th c t tn.
  • Khi ban s dung phng phap Application.Goto, nu ban mun s dung hai phng phap nm trong phng phap Dai khi dai cu th nm trn trang tinh khac (khng phai trang tinh ang hoat ng), ban phai bao gm i tng Trang mi ln. Vi du:
    Application.Goto Sheets("Sheet1").Range( _
          Sheets("Sheet1").Range(Sheets("Sheet1").Cells(2, 3), _
          Sheets("Sheet1").Cells(4, 5)))
    					
  • Vi mi muc trong du ngoc kep (vi du, dai a t tn "Test"), ban cungco th s dung bin s co gia tri la chui vn ban. Vi du, thay vi
    ActiveWorkbook.Sheets("Sheet1").Activate
    						
    ban co th s dung
    ActiveWorkbook.Sheets(myVar).Activate
    						
    gia tri cua myVar nm trong "Sheet1".

Tham kh?o


bit thm thng tin v cach s dung ma mu trong bai vit nay, bm vao s bai vit sau y xem bai vit trong C s Kin thc Microsoft:
290140 Cach chay ma mu cho chng trinh Office XP t bai vit trong C s Kin thc
Chu y y la mt bai vit "XUT BAN NHANH" c tao trc tip t trong trung tm h tr cua Microsoft. Thng tin co trong tai liu nay c cung cp nhm tra li cac vn mi xut hin. Do vic cung cp nhanh chong, tai liu co th co li in n va co th c sa i bt ky luc nao ma khng cn thng bao. Hay xem iu khoan S dung xem xet thm.

Thu?c tnh

ID c?a bi: 291308 - L?n xem xt sau cng: 09 Thang Ba 2014 - Xem xt l?i: 3.0
p d?ng
  • Microsoft Office Excel 2003
  • Microsoft Office Excel 2007
  • Microsoft Excel 2010
T? kha:
kbautomation kbmacro kbdtacode kbhowto kbprogramming KB291308

Cung cp Phan hi

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com