[µ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

Scott090

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


    ³o­Ó¥Î 13# ªºªþ¥ó¸Õ¹L

1. Sub CompactSheet()
   
2.     Dim ws As Worksheet
   
3.     Dim C%, Col%
   
4.     Set ws = Sheets("Output")
   
5.     ws.Select
   
6.     With ws
   
7. '        .[A1].Select
   
8.         Debug.Print .UsedRange.Address
   
9.         On Error Resume Next
   
10. '        .Cells.Ungroup
    
11. '        .Cells.EntireColumn.Hidden = False
    
12.         If ActiveWindow.FreezePanes Then ActiveWindow.FreezePanes = False
    
13.         
    
14. '        Columns("A:Z").Delete Shift:=xlToLeft
    
15.          Col = .UsedRange.Column
    
16.         C = .UsedRange.Columns.Count
    
17.         .Cells(1, Col).Resize(.Rows.Count, C).Delete Shift:=xlToLeft
    
18.          .[A1].Select
    
19.         Debug.Print .UsedRange.Address
    
20.               
    
21.         On Error GoTo 0
    
22.     End With
    
23. End Sub

½Æ»s¥N½X

¤p«L«È

¦^´_ 26# Scott090

ÁÂÁ§A¡A§Ú§â§Aªº¥N½X½Æ»s¤@¦¸¡A·í¦¨RESET USEDRANGE.ADDRESS¡A¦ý¥i±¤¥¼¯à¦¨¥\¡C
¥i¯à¬OexcelªºBUG¡AµLªk¸Ñ¨M...

Scott090

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


    ¬Ý¬Ý¤U¦Cµ{¦¡¯à¤£¯à¾A¦X¨Ï¥Î

1. Sub CompactSheet()
   
2.     Dim ws As Worksheet
   
3.     Dim R%, C%
   
4.     Set ws = Sheets("Output")
   
5.     With ws
   
6.         ws.[A1].Select
   
7.         Debug.Print .UsedRange.Address
   
8.         On Error Resume Next
   
9.         .Cells.Ungroup
   
10.         .Cells.EntireColumn.Hidden = False
    
11.         .Cells.Delete
    
12.         
    
13. 'Copy ¤@°Ï¨S¦³ÁôÂùLªº Columns ¨ì .UsedRange
    
14. 
    
15.         [A1].Select
    
16.         Debug.Print .UsedRange.Address
    
17.         C = .UsedRange.Columns.Count
    
18.         Range("A1").Resize(.Rows.Count, C).Copy .UsedRange
    
19.         [A1].Select
    
20.         Debug.Print .UsedRange.Address
    
21.         On Error GoTo 0
    
22.     End With
    
23. End Sub

½Æ»s¥N½X

lpk187

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


    ¤£¦n·N«ä¡I­ì¨Ó§Ú¨S¬Ý«e­±ªº°Q½×¡A¯u©êºp¡I
§Ú¸Õ¤F¤£¤Ö¤èªk¡A³Ì«á¦³µo²{¡A¦bµ²§ô¬¡­¶Ã¯¤§«e²MªÅ¤u§@ªí¡C¦A¥´¶}ªº®É­Ô¡A´N·|«ì´_Âk¹sªºUsedRange¡C§A¥i¥H¸Õ¸Õ¬Ý¡I

¤p«L«È

¦^´_ 22# lpk187


ÁÂÁ§Aªº«ü±Ð¡Aªº½T¦p¦¹¡AÁöµM³o­Ó°µªk¬O¥i¥H¥OUSEDRANGE¤£Åܤj¡A¦ýUSEDRANGEªº¸ê®Æ¤´µM¤£¥¿½T
¦pªG°õ¦æ¤F§A´£¨Ñªº¥N½X«á¡A¤â°Ê§â©Ò¦³ªº¸ê®Æ§R°£¡AUSEDRANGE¤´µMÅã¥Ü¡G$C$1:$T$1

stillfish00

¦^´_ 22# lpk187
­«ÂI¤£¬O³o­Ó§a ...
²{¦b¬O¦b»¡©ú©ú¨S¸ê®Æ¨S®æ¦¡ªºÀx¦s®æ¡Ausedrange ¦b¬Y¨Ç±¡ªp·|»~§P¬°¦³¨Ï¥Î¡C

lpk187

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

¦³®É­Ô¡A¸ÕÅç¤@¤U´Nª¾¹D¡A¤U­±§A¸Õ¤@¤U´Nª¾¹D¤£·|¼W¥[¡I

1. Set LayoutRS = ConfigCN.Execute(LayoutSQL)
   
2.     B = 2
   
3.     C = 3
   
4.     D = 4
   
5.    
   
6. For i = StartYear To EndYear
   
7.     'Out.Columns("B:D").Insert
   
8.     Out.Range(Columns(C), Columns(D)).Columns.Group
   
9.     Out.Cells(1, B) = i & " / FY"
   
10.     Out.Cells(1, C) = i & " / 2H"
    
11.     Out.Cells(1, D) = i & " / 1H"
    
12.    
    
13.     With LayoutRS
    
14.         .MoveFirst
    
15.         
    
16.         Do Until .EOF
    
17.             If Out.Cells(.Fields("Item_ID"), 1) = "" Then
    
18.                 Out.Cells(.Fields("Item_ID"), 1) = .Fields("Item_Name").Value
    
19.                 'End If
    
20.             End If
    
21.             .MoveNext
    
22.         Loop
    
23.     End With
    
24.    
    
25. 
    
26.     DataSQL = "select * from tbl_Income_Sub where Code = " & Code & " and S_Year = '" & i & "'"
    
27.     Set DataRS = DataCN.Execute(DataSQL)
    
28.     With DataRS
    
29.     Do Until .EOF
    
30.         Select Case .Fields("Term")
    
31.             Case "1H"
    
32.                 TargetCol = D
    
33.             Case "FY"
    
34.                 TargetCol = B
    
35.         End Select
    
36.         Out.Cells(2, TargetCol) = .Fields("Currency")
    
37.         Out.Cells(3, TargetCol) = .Fields("Unit")
    
38.         Out.Cells(4, TargetCol) = .Fields("Report_Date")
    
39.         CurrUnit = .Fields("Unit")
    
40.         If TargetCol = B Then
    
41.             Out.Cells(2, TargetCol + 1) = .Fields("Currency")
    
42.             Out.Cells(3, TargetCol + 1) = .Fields("Unit")
    
43.             Out.Cells(4, TargetCol + 1) = .Fields("Report_Date")
    
44.         End If
    
45.         .MoveNext
    
46.     Loop
    
47.     End With
    
48.    
    
49.     DataSQL = "select * from tbl_Income where Code = " & Code & " and S_Year = '" & i & "'"
    
50.     Set DataRS = DataCN.Execute(DataSQL)
    
51.    
    
52.     With DataRS
    
53.     Do Until .EOF
    
54.         If Not Out.Columns(1).Find(.Fields("Item"), lookat:=xlWhole) Is Nothing Then
    
55.             TargetRow = Out.Columns(1).Find(.Fields("Item"), lookat:=xlWhole).Row
    
56.             Select Case .Fields("Term")
    
57.                 Case "1H"
    
58.                     TargetCol = D
    
59.                 Case "FY"
    
60.                     TargetCol = B
    
61.             End Select
    
62. 
    
63.             Out.Cells(TargetRow, TargetCol) = Round(.Fields("Amount"), 4)
    
64.         End If
    
65.         .MoveNext
    
66.     Loop
    
67.     End With
    
68.     B = B + 3
    
69.     C = C + 3
    
70.     D = D + 3
    
71. 
    
72. Next i

½Æ»s¥N½X

¤p«L«È

¦^´_ 20# lpk187


    ¦³§r¡A§Ú¥H¬°¬O³o¼Ë¡G¥Ñ©óµLªk­«³]¸Ó¤u§@ªíªºUSEDRANGE¡A©Ò¥H¨C¦¸¦bUSEDRANGE¤¤¶¡insert¤F10Äæ¡Ausedrange³£·|Åܤj10Äæ
§A¥i¥H¸Õ¸Õ¥Î§ÚªºV2¡A³ÌªìªºUSEDRANGE¬O¡u$A$1:$S$31¡v¡AµM«á¥þ¿ï¦A¥þ³¡²M°£¸ê®Æ¡AUSEDRANGEÅܦ¨¡u$C$1:$S$1¡v¡A©Ò¥H¨C¦¸µ{¦¡INSERT COLUMNS¡A³£·|¥OUSEDRANGEÅܤj¡C¦Ó¦pªG§âinsert¨º¥y§R¥h¡Aµ{¦¡«K·|§â©Ò¦³¸ê®Æ¼g¦bB¦ÜDÄæ¤W¡AUSEDRANGE«K¨S¦³Åܤj
¤£ª¾¹D¬O¤£¬O³o¼Ë¡A§Ú¬O³o¼Ë¸ÑÄÀ¡C

lpk187

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

§A¤£Ä±±o©_©Ç¶Ü¡H¬°¤°»ò¨C¦¸¼WªºÄæ©M§A´¡¤Jªº¤@¼Ë¦h¶Ü¡H