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

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

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

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

LeoV-65.gif (471.56 KB)

LeoV-65.gif

LeoV65.rar (13.51 KB)

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-8-30 14:36 ½s¿è

¦^´_ 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. Sub ÁôÂÃ(xLRng As Range)
  20.     Dim Rng As Range, xF As Range
  21.     Set Rng = Range("A2, F2" )
  22.     Cells.EntireColumn.Hidden = False
  23.     Set xF = Rows(2).Find(xLRng, LOOKAT:=xlPart, LookIn:=xlValues)   '´M§ä
  24.     Do
  25.         Set Rng = Union(Rng, xF)
  26.         Set xF = Rows(2).FindNext(xF)
  27.     Loop While xLRng.Address <> xF.Address  '¤@ª½¨ì¬O²Ä¤@´M§ä¨ìªº¦ì¸m
  28.     Cells.EntireColumn.Hidden = True
  29.     Rng.EntireColumn.Hidden = False
  30.     Rng.Select
  31.     With ActiveWindow
  32.         .SplitColumn = 6
  33.         .SplitRow = 2
  34.         .FreezePanes = True
  35.     End With
  36. End Sub
½Æ»s¥N½X

TOP

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

TOP

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

TOP

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

Leov65-B.gif (973.03 KB)

Leov65-B.gif

TOP

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

TOP

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

LeoV65-1.rar (19.51 KB)

TOP

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

TOP

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

TOP

¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-9-1 14:37 ½s¿è

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

TOP

        ÀR«ä¦Û¦b : ¥@¤W¦³¨â¥ó¨Æ¤£¯àµ¥¡G¤@¡B§µ¶¶ ¤G¡B¦æµ½¡C
ªð¦^¦Cªí ¤W¤@¥DÃD