GBKEE

¦^´_ 43# 198188
Set Rng(1) = .[E1000].End(xlUp):  E1000¶}©l©¹¤WÀˬd³Ì«á¤@¦C=> ¦p¬O E999
Set Rng(1) = .[E1000].End(xlUp).Offset(, -4) => A999
¨º A999 ¨ì Àɮש³³¡ ªº¦C¼Æ¬O 2003-> 65536-999 +1
*********
Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))
AÄæ²Ä¤G¦C¶}©l©¹¤U¨ì³Ì«á¤@µ§½d³òªº¦C¼Æ ?? ¦p¤j©ó 65536-999 +1
********** ½Æ»sªº¸ê®Æ½d³ò>¶K¤W¦ì¸mªº½d³ò ?? ¨º¦h¥Xªº¸ê®Æ­nÂ\­þ¸Ì ??****

GBKEE

¦^´_ 46# 198188
41# ªº¿ù»~¦b

1.           Set Rng(2) = .[A2:L2]
   
2.           Set Rng(2) = .Range(Rng(2), .[A2].End(xlDown))   'AÄæ¨S¸ê®Æ [A2].End(xlDown) ·|¨ìÀɮש³³¡

½Æ»s¥N½X

39# ¤w´£¿ô§A: µ¹§Aªºµ{¦¡½X­n®ø¤Æ¤@¤U,VBA¤~·|¶i¨B

1.    
   
2.           Set Rng(1) = .[E2]  'EÄæ¸ê®Æ¦³³sÄò
   
3.          '
   
4.          '
   
5.            Set Rng(2) = .[A2:L2]
   
6.           ' **** Set Rng(2) = .Range(Rng(2), .[a2].End(xlDown))  ***** ³o¦æ¤£­n¥Î
   
7.             Set Rng(2) = Rng(2).Resize(.[E1].End(xlDown).Row - 1)  
   
8.            '[E1].End(xlDown) ¨ìEÄ榳¸ê®Æªº¦a¤è·|°±¤î,¤~¤£·|¨ìÀɮש³³¡
   
9.             Rng(2).copy Rng(1).Cells(1, -3)

½Æ»s¥N½X

GBKEE

¦^´_ 48# 198188
·í§ÚEÄ椤¶¡¦³¤@¦CªÅ¥Õ: ¥i¥ÑÀɮש³³¡©¹¤W

1.     Set Rng(2) = Rng(2).Resize(.Cells(.Rows.Count, "E").End(xlUp).Row - 1)
   
2.     Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)

½Æ»s¥N½X

¦^´_ 49# 198188
VBA ªº»¡©ú

1. Resize ÄÝ©Ê
   
2. ½Ð°Ñ¾\®M¥Î¦Ü½d¨Ò¯S©w½Õ¾ã«ü©wªº½d³ò¡C¶Ç¦^ Range ª«¥ó¡A¸Óª«¥ó¥Nªí½Õ¾ã«áªº½d³ò¡C
   
3. expression.Resize(RowSize, ColumnSize)
   
4. expression     ¥²¿ï¡C¸Ó¹Bºâ¦¡¶Ç¦^­n½Õ¾ã¤j¤pªº Range ª«¥ó¡C
   
5. RowSize     ¿ï¾Ü©Êªº Variant¡C·s½d³ò¤¤©Ò¥]§tªº¦C¼Æ¡C¦pªG¬Ù²¤¦¹¤Þ¼Æ¡A½d³ò¤¤ªº¦C¼Æ«O«ù¤£ÅÜ¡C
   
6. ColumnSize     ¿ï¾Ü©Êªº Variant¡C·s½d³ò¤¤©Ò¥]§tªºÄæ¼Æ¡C¦pªG¬Ù²¤¦¹¤Þ¼Æ¡A½d³ò¤¤ªºÄæ¼Æ«O«ù¤£ÅÜ¡C

½Æ»s¥N½X

1. For xi = 1 To 5
   
2.         Set Rng(2) = Rng(2).Resize(xi)
   
3.         MsgBox Rng(2).Address
   
4.     Next

½Æ»s¥N½X

GBKEE

¦^´_ 52# 198188

   

¤U¸ü (41.78 KB)

2012-12-9 08:03

¦p¹Ï¾Þ§@¥i¤è«K¥L¤H½Æ»sµ{¦¡½X
²¤Æ§Aªºµ{¦¡½X

1. Option Explicit
   
2. Sub Ex()
   
3.    Dim Rng(1 To 2) As Range, Files_AR(), E As Variant
   
4.      Files_AR = Array("Connie.XLSX", "Lily.XLSX", "Jane.XLSX", "Jenny.XLSX")
   
5.                                                                        'ÀɮצWºÙ¸m¤J°}¦C:²¤Æµ{¦¡ªº®Ñ¼g
   
6.      With Workbooks("payment.XLSM").Sheets("2012")
   
7.         .Range("A2:L65536").ClearContents
   
8.         .Range("A2:L65536").Interior.Color = xlNone
   
9.         .Range("A1").CurrentRegion.Offset(1) = ""                       '²M°£A1³sÄò½d³òOffset(1):²Ä¤@¦C¥H«á³sÄò½d³ò¸ê®Æ
   
10.         For Each E In Files_AR                                          '°j°é¨ú¦P¤@¸ê®Æ§¨ªºÀÉ®×
    
11.             Set Rng(1) = .Range("E" & .Rows.Count).End(xlUp).Offset(1)  'Offset(2)=> ¥»¨­ºâ°_¦p¬OE1-> E3
    
12.             With Workbooks.Open("C:\Documents and Settings\USER\®à­±\" & E).Sheets("SHEET1")
    
13.                 Set Rng(2) = .[A2:L2]
    
14.                 Set Rng(2) = Rng(2).Resize(.Range("E" & .Rows.Count).End(xlUp).Row - 1)
    
15.                 Rng(2).Copy Rng(1).Cells(1, -3)
    
16.                 .Parent.Close False
    
17.             End With
    
18.          Next
    
19.     End With
    
20. End Sub

½Æ»s¥N½X