¤£¦P¦WºÙÁôÂÃ

myleoyes

¦U¦ì«e½ú§A­Ì¦n!
         «e½ú!!»Ý¨D¦p°Êµe©Ò¥Ü!
         ¦]¬°Äæ¼Æ¤£¬q¼W¥[©Ò¥H¤£¯à¥Î¿ý»s¤è¦¡
         ½Ð°ÝSub ¤ÀªRÁôÂÃ() ¸ê®ÆÁôÂÃ() Sub ¸ê®Æ¨Ó·½() Sub Ãö«YÁôÂÃ()µ{¦¡¬°¦ó?
         ½Ðª¾¹Dªº«e½ú,¤£§[½ç±ÐÁÂÁ¦A¤T!!

GBKEE

¦^´_ 1# myleoyes

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2.     Application.EnableEvents = False
   
3.     If Target(1).Row = 2 Then           '¦b²Ä2¦C
   
4.          Select Case Target(1).Column
   
5.         Case 1
   
6.               With ActiveWindow         '²¾°£µøµ¡¤À³Î
   
7.                 .SplitColumn = 0
   
8.                 .SplitRow = 0
   
9.             End With
   
10.             Cells.EntireColumn.Hidden = True
    
11.             [A1:F1].Cells.EntireColumn.Hidden = False
    
12.             [A1].Select
    
13.         Case 2 To 5                     'B2:E2
    
14.             ÁôÂÃ Target(1)
    
15.     End Select
    
16.     End If
    
17.     Application.EnableEvents = True
    
18. End Sub
    
19. 
    
20. Sub ÁôÂÃ(xLRng As Range)
    
21.     Dim Rng As Range, xF As Range
    
22.     Set Rng = Range("A2, F2" )
    
23.     Cells.EntireColumn.Hidden = False
    
24.     Set xF = Rows(2).Find(xLRng, LOOKAT:=xlPart, LookIn:=xlValues)   '´M§ä
    
25.     Do
    
26.         Set Rng = Union(Rng, xF)
    
27.         Set xF = Rows(2).FindNext(xF)
    
28.     Loop While xLRng.Address <> xF.Address  '¤@ª½¨ì¬O²Ä¤@´M§ä¨ìªº¦ì¸m
    
29.     Cells.EntireColumn.Hidden = True
    
30.     Rng.EntireColumn.Hidden = False
    
31.     Rng.Select
    
32.     With ActiveWindow
    
33.         .SplitColumn = 6
    
34.         .SplitRow = 2
    
35.         .FreezePanes = True
    
36.     End With
    
37. End Sub

½Æ»s¥N½X

luhpro

¦U¦ì«e½ú§A­Ì¦n!
         «e½ú!!»Ý¨D¦p°Êµe©Ò¥Ü!
         ¦]¬°Äæ¼Æ¤£¬q¼W¥[©Ò¥H¤£¯à¥Î¿ý»s¤è¦¡
       ...
myleoyes µoªí©ó 2012-8-29 12:03

1. Sub ¤ÀªRÁôÂÃ()
   
2.     Dim iCols%, iI%, vCol
   
3.   
   
4.     [B3].Select
   
5.     ActiveWindow.FreezePanes = False
   
6.     iCols = Cells(2, Columns.Count).End(xlToLeft).Column
   
7.     Set vCol = Range(Cells(3), Cells(5))
   
8.     iI = 8
   
9.     Do
   
10.     Set vCol = Application.Union(vCol, Range(Cells(iI), Cells(iI + 2)))
    
11.     iI = iI + 4
    
12.     Loop Until iI > iCols
    
13.     vCol.EntireColumn.Hidden = True
    
14.    
    
15.     '[C:E,H:J,L:N,P:R,T:V,X:Z].EntireColumn.Hidden = True
    
16.    
    
17.     [G3].Select
    
18.     ActiveWindow.FreezePanes = True
    
19. End Sub
    
20. Sub ¸ê®ÆÁôÂÃ()
    
21.     Dim iCols%, iI%, vCol
    
22.   
    
23.     [B3].Select
    
24.     ActiveWindow.FreezePanes = False
    
25.     iCols = Cells(2, Columns.Count).End(xlToLeft).Column
    
26.     Set vCol = Application.Union(Cells(2), Cells(4), Cells(5), Cells(7))
    
27.     iI = 9
    
28.     Do
    
29.     Set vCol = Application.Union(vCol, Range(Cells(iI), Cells(iI + 2)))
    
30.     iI = iI + 4
    
31.     Loop Until iI > iCols
    
32.     vCol.EntireColumn.Hidden = True
    
33.    
    
34.     '[B:B,D:E,G:G,I:K,M:O,Q:S,U:W,Y:AA].EntireColumn.Hidden = True
    
35.    
    
36.     [H3].Select
    
37.     ActiveWindow.FreezePanes = True
    
38. End Sub

½Æ»s¥N½X

¨ä¥Lªº¥u­n°Ñ·Ó¤W­zµ{¦¡¤º®e¾A·íªº®M¥Î§Y¥i§¹¦¨,
³o¸Ì´N¤£¦A¦C¥X¨ÓÅo.

myleoyes

¦^´_ 3# luhpro
  «e½ú!
       ³o¥¿¬O¤p§Ì©Ò»ÝÁÂÁ¦A¤T!!

myleoyes

¦^´_ 2# GBKEE
¨}®v!ÁÂÁÂ!!µ{¦¡¦bÁôÂîɤ֤F1Äæ (ÂI¿ïÄæ)
            ®i¶}®ÉµLªk¥þ¶},¦P®É¨S¦³­áµ²µ¡®æ
            ¦p°Êµe©Ò¥Ü!½Ð¦A­×§ï¤@¤U¨¯­WÅo!!ÁÂÁ¦A¤T!

GBKEE

¦^´_ 5# myleoyes
¦A¸Õ¸Õ¬Ý

1. Sub ÁôÂÃ(xLRng As Range)
   
2.     Dim Rng As Range, xF As Range
   
3.     Set Rng = Range("A2, F2")
   
4.     Cells.EntireColumn.Hidden = False
   
5.     Set xF = Rows(2).Find(xLRng, xLRng.Offset(, -1), LOOKAT:=xlPart, LookIn:=xlValues) '´M§ä
   
6.     Do
   
7.         Set Rng = Union(Rng, xF)
   
8.         Set xF = Rows(2).FindNext(xF)
   
9.     Loop While xLRng.Address <> xF.Address  '¤@ª½¨ì¬O²Ä¤@´M§ä¨ìªº¦ì¸m
   
10.     Cells.EntireColumn.Hidden = True
    
11.     Rng.EntireColumn.Hidden = False
    
12.     Rng.Select
    
13.     With ActiveWindow
    
14.         .SplitColumn = 6
    
15.         .SplitRow = 2
    
16.         .FreezePanes = True
    
17.     End With
    
18. End Sub

½Æ»s¥N½X

myleoyes

¦^´_ 6# GBKEE
¨}®v!ÁÂÁÂ!!µ{¦¡­×§ï¦p¤U
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    If Target(1).Row = 2 Then
         Select Case Target(1).Column
        Case 1
           [A:E].Cells.EntireColumn.Hidden = False  '¼W¥[¦¹¦C
              With ActiveWindow
                .SplitColumn = 1  '­×§ï³B
                .SplitRow = 2  '­×§ï³B
                .FreezePanes = True
            End With
            [A:IV].Cells.EntireColumn.Hidden = True   '­×§ï³B
            [A:IV].Cells.EntireColumn.Hidden = False  '­×§ï³B
            [A1].Select
        Case 2 To 5
            ÁôÂÃ Target(1)
    End Select
    End If
    Application.EnableEvents = True
End Sub
            ¥t¦³¤@µ{¦¡(ÂI¿ï¤U©Ô)¦pªþÀÉ®×!½Ð¦AÀ°¦£¤@¤U¨¯­WÅo!!ÁÂÁ¦A¤T!

GBKEE

¦^´_ 7# myleoyes
¬O³o¼Ë¶Ü?

1. Sub ¼Æ¾Ú()
   
2.     zz = Application.InputBox("¿é¤J¼Æ¾Ú", "½Ð¿é¤J­×¥¿¼Æ¾Ú", Type:=1)
   
3.     If zz = 0 Then Exit Sub
   
4.     ActiveCell = zz: [D1] = ""
   
5.     Selection.Resize(2).AutoFill Range(Selection, Cells([B100].End(3).Row, Selection.Column)), Type:=xlFillDefault
   
6.     [D1] = "'¤ÀªR"
   
7. End Sub

½Æ»s¥N½X

myleoyes

¦^´_ 8# GBKEE
¨}®v! ÁÂÁÂ!!µ{¦¡­×§ï¦p¤U¨¯­WÅo!!ÁÂÁ¦A¤T!
Sub ¼Æ¾Ú()
    zz = Application.InputBox("¿é¤J¼Æ¾Ú", "½Ð¿é¤J­×¥¿¼Æ¾Ú", Type:=1)
    If zz = 0 Then Exit Sub
    ActiveCell = zz: [D1] = ""
    Selection.AutoFill Range(Selection, Cells([B100].End(3).Row, Selection.Column)), Type:=xlFillDefault  '­×§ï³B
    [D1] = "'¤ÀªR"
End Sub

GBKEE

¦^´_ 9# myleoyes
¦³©¯·|¸Ñµª¬O¤£¨¯­Wªº,¥u¬O¤£©ú¼I°ÝÃD«Ü¨¯­W°Õ.­n­×§ï¤°»ò,¥i¥H¥Î¤å¦r±Ô­z¶Ü?