ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

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

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

¥»©«³Ì«á¥Ñ ¤p«L«È ©ó 2015-10-9 13:48 ½s¿è

¨ä¹ê§Ú¦³¨â­Ó°ÝÃ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

¦^´_ 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®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

¥»©«³Ì«á¥Ñ ¤p«L«È ©ó 2015-10-11 16:32 ½s¿è

¹ï¤£°_¡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. Application.ScreenUpdating = False

  3. Out.Cells.Clear
  4. Out.Columns.Ungroup
  5. Out.Cells.EntireColumn.Hidden = False
  6.   
  7. For i = 0 To 5

  8.     Out.Columns("B:D").Insert
  9.     Out.Columns("C:D").Columns.Group
  10.     Out.Cells(1, 2) = i & " / FY"
  11.     Out.Cells(1, 3) = i & " / 2H"
  12.     Out.Cells(1, 4) = i & " / 1H"
  13.    
  14.     With RecordsetA
  15.         .MoveFirst
  16.         
  17.         Do Until .EOF
  18.             Out.Cells(.Fields("Item_ID"), 2) = .Fields("FY").Value
  19.             Out.Cells(.Fields("Item_ID"), 3) = .Fields("Second_Half").Value
  20.             Out.Cells(.Fields("Item_ID"), 4) = .Fields("First_Half").Value
  21.             Out.Rows(.Fields("Item_ID")).NumberFormatLocal = .Fields("Format")
  22.             .MoveNext
  23.         Loop
  24.        

  25.     End With
  26. next i

  27. ConfigCN.Close
  28. Set ConfigCN = Nothing
  29. Set RecordsetA= Nothing

  30. Application.ScreenUpdating = True

  31. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# GBKEE
  1. Sub Integrate()

  2. Set out = ActiveSheet

  3. Application.ScreenUpdating = False

  4. Debug.Print out.UsedRange.Address
  5. On Error Resume Next

  6. out.Cells.Clear
  7. out.Columns.Ungroup
  8. out.Cells.EntireColumn.Hidden = False
  9.   
  10. Debug.Print out.UsedRange.Address
  11. For i = 0 To 5

  12.     out.Columns("B:D").Insert
  13.     out.Columns("C:D").Columns.Group
  14.     out.Cells(1, 2) = i & " / FY"
  15.     out.Cells(1, 3) = i & " / 2H"
  16.     out.Cells(1, 4) = i & " / 1H"

  17. Next i

  18. Application.ScreenUpdating = True

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

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2015-10-11 17:42 ½s¿è

¸Õ¸Õ¬Ý¡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¨Ï¨äÅã¥Ü

TOP

¦]¬°¸Ó¤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
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¥»©«³Ì«á¥Ñ ¤p«L«È ©ó 2015-10-12 12:09 ½s¿è

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

temp.zip (91.63 KB)

TOP

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

TOP

¦^´_ 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
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

        ÀR«ä¦Û¦b : ¥Ç¿ù¥XÄb®¬¤ß¡A¤~¯à²M²bµL·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD