¤p«L«È

¦^´_ 7# stillfish00


    §Ú¤]©ú¥Õ³o­Ó°ÝÃD¬O©Ç©Ç¡A¦ý§R°£¤u§@ªí¬O§Ú¸Ñ¨Musedrange.address¿ù»~ªº¥tÃþ¤èªk¡A¥i±¤§Ú¦b¸Ó¤u§@ªí¼g¤Fselection changeªºsubroutine¡A§R¤u§@ªí®É·|§âsubroutine§R±¼.....

¡u¥ÎQueryTables·|«Ø¥ß³s½u¡A¦pªG¥u¬O¥Î¨Ó¨ú±o¸ê®Æ¡A¤£»Ý­n«O«ù³s½u¡A¨ú§¹«á´N°¨¤W§R°£³s½u§a
§A¥i¥HÀˬd ¸ê®Æ>³s½u ¸Ì¬O¤£¬O«Ü¦h¤£¥²­nªº³s½u¡C¡v

³o­Ó¥i¯à¬O§a¡A§Úªº¥Õ·ö¸Ñ¨M¤èªk¬O...... ©w´Á§R±¼¸Ó¤u§@ªí¦A­«·s«Ø¥ß.......
½Ð°Ý¦p¦ó¥ÎVBA§â¤u§@ªíªº³s½u§R±¼¡H

¡u*usedrange §ì¤£¨ì­nªº½d³ò¡AªþÀÉ·|¤ñ¸û®e©ö§ä°ÝÃD¡F¤£µM´N¨Ì¸ê®Æ±¡ªp¬Ý¯à§_±Ä¥ÎcurrentRegion ©Î¨ä¥L©w¦ìªº¤èªk¡v
¤w¦b8¼Ó¤W¶Ç¤FÀɮסA·Ð½Ð¬d¬Ý

¤p«L«È

¦^´_ 6# ­ã´£³¡ªL


    ÁÂÁª©¤jÀ°¦£¡A¥i±¤§Ú¥Î¤F§Aªº¥N½XÁÙ¬OµLªk¸Ñ¨M°ÝÃD...

²{¤W¶Ç¤F²¤Æ¤Fªºµ{¦¡
À£ÁYÀɤ¤ªºACCESSÀɬO¦s©ñ¸ê®Æ¡A¤j®a¥i¥H¶}±ÒTESTÀÉ¡A«öshort cut ¡uCTRL+Q¡v°õ¦æµ{¦¡¡A¤§«á¿é¤J¡u1¡v«K¥i¥H¤F¡Aµ{¦¡³Ì«á·|Åã¥Üusedrange.address
¬°¤F´î¤ÖÀÉ®×®e¶q¡A©Ò¥HCODE 1¥H¥~ªº¸ê®Æ³£§R¥h......

stillfish00

¦]¬°¸Ó¤u§@ªí¦­¤w¼g¤J¤@¨Çsubroutine¡A¦ý§R°£¤u§@ªí®É«o¦P®É§â¤u§@ªí¤Uªºsubroutine§R±¼¡A½Ð°Ý¦³¨S¦³¤èªk¥[¦^¨º¨Çsubroutine?

³o°ÝÃD©Ç©Çªº¡Asubroutine·|¼g¦b¤u§@ªí¤U³q±`ªí¥Ü¥u¦³¸Ó¤u§@ªí·|¥Î¨ì¡A¦ÛµM§R±¼¤]µL©Ò¿×¡F
¦pªG¬O­nµ¹©Ò¦³¤u§@ªí³£¯à¥Îªºsub¡A¼g¦b¤@¯ë¼Ò²Õ´N¦n¤F¡C

ÃD¥~¸Ü¡G§ÚµoıEXCEL¦b­«½Æ¨Ï¥Î¡uWith QueryTables.Add¡v¦h¦¸(¬ù100¦¸¥H¤W)«á¡A¨C¦¸¶×¤Jªº®É¶¡³£·|´îºC

¥ÎQueryTables·|«Ø¥ß³s½u¡A¦pªG¥u¬O¥Î¨Ó¨ú±o¸ê®Æ¡A¤£»Ý­n«O«ù³s½u¡A¨ú§¹«á´N°¨¤W§R°£³s½u§a
§A¥i¥HÀˬd ¸ê®Æ>³s½u ¸Ì¬O¤£¬O«Ü¦h¤£¥²­nªº³s½u¡C

*usedrange §ì¤£¨ì­nªº½d³ò¡AªþÀÉ·|¤ñ¸û®e©ö§ä°ÝÃD¡F¤£µM´N¨Ì¸ê®Æ±¡ªp¬Ý¯à§_±Ä¥ÎcurrentRegion ©Î¨ä¥L©w¦ìªº¤èªk

­ã´£³¡ªL

¸Õ¸Õ¬Ý¡G
Sub AR1011()
ActiveSheet.UsedRange.EntireColumn.Delete¡@'Äæ¸s²Õ¤]¤@¨Ö¨ú®ø
ActiveSheet.UsedRange.EntireRow.Delete¡@'¦C¸s²Õ¤]¤@¨Ö¨ú®ø
ActiveSheet.UsedRange.Delete¡@'¦¹®É¡AUsedRange ¤´¥¼ÄÀ©ñ¡A¦A Del ¤@¦¸

MsgBox ActiveSheet.UsedRange.Address
End Sub

µù¡G­YÄæ¦C¦³ÁôÂá]«D¸s²ÕªºÁôÂá^¡AÁÙ¶·¦A¥[«ü¥O¨Ï¨äÅã¥Ü

¤p«L«È

¦^´_ 2# GBKEE

1. Sub Integrate()
   
2. 
   
3. Set out = ActiveSheet
   
4. 
   
5. Application.ScreenUpdating = False
   
6. 
   
7. Debug.Print out.UsedRange.Address
   
8. On Error Resume Next
   
9. 
   
10. out.Cells.Clear
    
11. out.Columns.Ungroup
    
12. out.Cells.EntireColumn.Hidden = False
    
13.   
    
14. Debug.Print out.UsedRange.Address
    
15. For i = 0 To 5
    
16. 
    
17.     out.Columns("B:D").Insert
    
18.     out.Columns("C:D").Columns.Group
    
19.     out.Cells(1, 2) = i & " / FY"
    
20.     out.Cells(1, 3) = i & " / 2H"
    
21.     out.Cells(1, 4) = i & " / 1H"
    
22. 
    
23. Next i
    
24. 
    
25. Application.ScreenUpdating = True
    
26. 
    
27. End Sub

½Æ»s¥N½X

³o­Ó¥N½X¯à°÷­«²{usedrangeªº°ÝÃD¡A¬O§Úªº§Ë¿ù¶Ü¡H

¤p«L«È

¹ï¤£°_¡A§Úµo²{¤F°ÝÃD¡A§Ú¬Û«H¬OGROUP°ÝÃD¡A¦pªG¤£¥ÎGROUPING¡A¦n¹³usedrange.address«K¥¿±`¤F
¦ý§Úªºµ{¦¡¤]¦³¥Î.Columns.Ungroup¡A¥i¬O°ÝÃD¤´µM¥X²{......

1. Sub Integrate()
   
2. 
   
3. Application.ScreenUpdating = False
   
4. 
   
5. Out.Cells.Clear
   
6. Out.Columns.Ungroup
   
7. Out.Cells.EntireColumn.Hidden = False
   
8.   
   
9. For i = 0 To 5
   
10. 
    
11.     Out.Columns("B:D").Insert
    
12.     Out.Columns("C:D").Columns.Group
    
13.     Out.Cells(1, 2) = i & " / FY"
    
14.     Out.Cells(1, 3) = i & " / 2H"
    
15.     Out.Cells(1, 4) = i & " / 1H"
    
16.    
    
17.     With RecordsetA
    
18.         .MoveFirst
    
19.         
    
20.         Do Until .EOF
    
21.             Out.Cells(.Fields("Item_ID"), 2) = .Fields("FY").Value
    
22.             Out.Cells(.Fields("Item_ID"), 3) = .Fields("Second_Half").Value
    
23.             Out.Cells(.Fields("Item_ID"), 4) = .Fields("First_Half").Value
    
24.             Out.Rows(.Fields("Item_ID")).NumberFormatLocal = .Fields("Format")
    
25.             .MoveNext
    
26.         Loop
    
27.        
    
28. 
    
29.     End With
    
30. next i
    
31. 
    
32. ConfigCN.Close
    
33. Set ConfigCN = Nothing
    
34. Set RecordsetA= Nothing
    
35. 
    
36. Application.ScreenUpdating = True
    
37. 
    
38. End Sub

½Æ»s¥N½X

¤p«L«È

¦^´_  ¤p«L«È


   
¤£¤Ó¤F¸Ñ....§Aªº°ÝÃD
GBKEE µoªí©ó 2015-10-11 14:43

    ¥Ñ©ó§ÚµLªk¹B¦æ§Aªº¥N½X¡A©Ò¥H§Ú§â¥N½X§ï¦¨¡G

1. Sub Ex()
   
2.     With ActiveSheet
   
3.         Debug.Print "1¡G¡@"¡®.UsedRange.Address
   
4.         .Range("A1:Q100") = "TEST"
   
5.         Debug.Print "¢±¡G¡@"¡®.UsedRange.Address
   
6.         .Cells.Clear
   
7.         Debug.Print "¢²¡G¡@"¡®.UsedRange.Address
   
8.         .Cells.Delete
   
9.         Debug.Print "¢³¡G¡@"¡®.UsedRange.Address
   
10.     End With
    
11. End Sub

½Æ»s¥N½X

µ²ªG¬O¡G
¢°¡G¡@$A$1:$AL$130
¢±¡G¡@$A$1:$AL$130
¢²¡G¡@$C$1:$AL$1
¢³¡G¡@$C$1:$AL$1

¨ä¹ê²Ä¤@­Ó¡u¢°¡G¡@$A$1:$AL$130¡v¤]¬O¿ù¡A¦]¬°¨º¤u§@ªí¤¤¥u¦bA1:Q130¦³¸ê®Æ¡A¦Ó¨C¦¸§Ú­«¼g¸ê®Æ®É³£¥Î¡uCells.Clear¡v§â¸ê®Æ§R±¼¡A¦ý¡uUsedRange.Address¡v¦n¹³µLªk§ä¨ì¥¿½Tµ²ªG($A$1)¡C

ÃD¥~¸Ü¡G§ÚµoıEXCEL¦b­«½Æ¨Ï¥Î¡uWith QueryTables.Add¡v¦h¦¸(¬ù100¦¸¥H¤W)«á¡A¨C¦¸¶×¤Jªº®É¶¡³£·|´îºC¡AµL½×§Ú­«·s¶}±ÒEXCEL©Î­«±Ò¹q¸£¡A¬Æ¦Ü§â¸Ó¤u§@ªí·í¤¤¦]¶×¤J¦Ó²£¥Íªºnamed range§R±¼¡A¶×¤J®É¶¡¤´µM«ÜºC¡A¤£ª¾¹D­ì¦]......

GBKEE

¦^´_ 1# ¤p«L«È


   

¦ýµL½×§Ú¬O¥Îcells.delete©Îcells.clear¡Ausedrange.address ³£µLªk­«³]¦¨§Ú¦³¸ê®Æªºrange

¤£¤Ó¤F¸Ñ....§Aªº°ÝÃD

1. Sub Ex()
   
2.     Application.VBE.Windows("§Y®É¹Bºâ").Visible = True
   
3.     With Cells
   
4.         .Range("A1:Q100") = "TEST"
   
5.         Debug.Print UsedRange.Address
   
6.         .Clear
   
7.         Debug.Print UsedRange.Address
   
8.     End With
   
9. End Sub

½Æ»s¥N½X