[µo°Ý] ¦p¦ó¥ÎVBA §âSUBOROUTINE¼g¤J¯S©wWORKSHEET¡H

¤p«L«È

¨ä¹ê§Ú¦³¨â­Ó°ÝÃD·Q¤F¸Ñ¡G

1) ¦p¦ó¯à­«³]¤@­Ó¤u§@ªíªºusedrange.address¡H
§Ú¼gªºµ{¦¡¤j·§¬yµ{¬O¡G¡u²M°£¬Y¤@¤u§@ªí¸ê®Æ¡v>¡u¼g¤J¸ê®Æ(·í¤¤·|´¡¤J¼Æ­ÓÄæ¦ì)¡v>¡u¦b¦³¸ê®Æªº¦ì¸m¦A§@³B²z(usedrange)¡v¡C
¦ý¹B¦æ«Ü¦h¦¸«á§Úµo²{¡A§Y¨Ï§Úªº¸ê®Æ¥u¬O¥ÑA1:Q100¡A¸Ó¤u§@ªíªºusedrange.addressÅܱo«Ü¤j(A1:ZQ100)¡A§Ú²q¬O¤§«eªº´¡¤JÄæ¦ì°Ê§@¦Ó¼vÅT¡C¦ýµL½×§Ú¬O¥Îcells.delete©Îcells.clear¡Ausedrange.address ³£µLªk­«³]¦¨§Ú¦³¸ê®Æªºrange¡A¨Ïµ{¦¡¹B¦æ±o¶V¨Ó¶VºC¡C

¥Ñ©ó§ÚµLªk¸Ñ¨Musedrange.addressªº¿ù»~¡A©Ò¥H§Ú§ï¤Fµ{¦¡¡A¥Ñ¡u²M°£¬Y¤@¤u§@ªí¸ê®Æ¡vÅܦ¨¡u§R°£¸Ó¤u§@ªí¦A­«·s¥[¤J¦P¦W¦rªº¤u§@ªí¡v¡Ausedrangeªº°ÝÃDºâ¬O¸Ñ¨M¤F¡A¦ý§Ú¤S²£¥Í¥t¤@­Ó°ÝÃD
2) ¦p¦ó¥ÎVBA §âSUBOROUTINE¼g¤J¯S©wWORKSHEET¡H
¦]¬°¸Ó¤u§@ªí¦­¤w¼g¤J¤@¨Çsubroutine¡A¦ý§R°£¤u§@ªí®É«o¦P®É§â¤u§@ªí¤Uªºsubroutine§R±¼¡A½Ð°Ý¦³¨S¦³¤èªk¥[¦^¨º¨Çsubroutine?

°ÝÃD¹ê¦b©_©Ç¡A¥ýÁÂÁ¤j®a¡C

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

¤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­ì¦]......

¤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«È

¦^´_ 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

­ã´£³¡ª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¨Ï¨äÅã¥Ü

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

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

¤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¬Ý

stillfish00

¦^´_ 9# ¤p«L«È
1. ªþÀɤâ°Ê§R°£Äd T:ND ­«¶]¡AUsedRange.Address ´N·|Åã¥Ü $A$1:$S$31
    ©Î¬O MsgBox Out.UsedRange.Address ¤]¥i¥H§ï¬°
    With Out
        MsgBox .Range(.Cells(1, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, .Cells(1, Columns.Count).End(xlToLeft).Column)).Address
    End With

2. ¥ý¤â°Ê§R°£¨º¨Ç¤£¥Îªº³s½u
    ¦b§A¥Î QueryTable ªºµ{¦¡½X(ªþ¥ó¨S¦³§Ú§ä¤£¨ì) , refresh «á­±¥[¤@¦æ delete
    With ActiveSheet.QueryTables.Add(XXX)
        ..........
        .Refresh BackgroundQuery:=False
        .Delete
    End With

3. selection change ¬O¤u§@ªí¨Æ¥ó¡A¨º¨Ç code ¤]¯à²¾´Ó¨ì ThisWorkbook ªº Workbook_SheetSelectionChange¡A¥u­n§A¦h§PÂ_¬O§_¬O§A­nIJµoªº¤u§@ªí¡C