¦^¨ì­ì¨Óªº¦ì¸m

myleoyes

¦U¦ì«e½ú§A­Ì¦n!
         «e½ú!!°ÝÃD¦pªþÀÉ»¡©ú
         ½Ðª¾¹Dªº«e½ú,¤£§[½ç±ÐÁÂÁ¦A¤T!!

oobird

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
       If Not Intersect(Target(1), Range("J3:J1443")) Is Nothing And Target(1) <> "" And [A1] <> "¨ú®ø" Then
          ActiveCell.Offset(0, -3).Copy [Y3]
       Range(Target(1).Offset(1, -9), Target(1).Offset(1, -9).End(xlDown)).EntireRow.Hidden = True
       Target.Offset(, -3) = Target.Value
       Range("G:J").EntireColumn.AutoFit
       Range("AA1") = 1
     ElseIf Target.Address = "$J$1" Then
          ÁÙ­ì
    End If
End Sub
³o¼Ë¶Ü¡H

myleoyes

¦^´_ 2# oobird
oobird«e½ú§A¦n!
      «e½ú!ÁÂÁÂ!¤p§Ì¸ÑÄÀ¤£²M·¡
      ¤p§Ì­nªº¬OÁÙ­ì³o­Óµ{¦¡
      Sub ÁÙ­ì() 'J1
          [G13] = [Y3]
          Range("Y3,AA1") = ""
          Rows("3:1443").EntireRow.Hidden = False
      End Sub
      ½d¨ÒY3ªº­È¬OÂI¿ïJ13ªºµ²ªG
      «öJ1®ÉÁÙ­ìµ{¦¡±NY3ªº­ÈÁÙ­ì¦^¥hG13ªº¦ì¸m
      ¦pªGÂI¿ïJ66
      «öJ1®ÉÁÙ­ìµ{¦¡¤]¬O±NY3ªº­ÈÁÙ­ì¦^¥hG66ªº¦ì¸m
      ¥H¦¹Ãþ±À½Ð¦A¨¯­WÅo!ÁÂÁ¦A¤T!!

Hsieh

¦^´_ 3# myleoyes
¸Õ¸Õ¬Ý

    Sheet1¼Ò²Õ

1. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
2.        If Not Intersect(Target(1), Range("J3:J1443")) Is Nothing And Target(1) <> "" And [A1] <> "¨ú®ø" Then
   
3.           ActiveCell.Offset(0, -3).Copy [Y3]
   
4.           Me.Names.Add "x", ActiveCell.Offset(0, -3)
   
5.        Range(Target(1).Offset(1, -9), Target(1).Offset(1, -9).End(xlDown)).EntireRow.Hidden = True
   
6.        Target.Offset(, -3) = Target.Value
   
7.        Range("G:J").EntireColumn.AutoFit
   
8.        Range("AA1") = 1
   
9.      End If
   
10.      Select Case Target(1).Address(0, 0)
    
11.         Case "J1"
    
12.           ÁÙ­ì
    
13.     End Select
    
14. End Sub

½Æ»s¥N½X

¤@¯ë¼Ò²Õ

1. Sub ÁÙ­ì() 'J1
   
2.    Sheet1.[x] = [Y3]
   
3.     Range("Y3,AA1") = ""
   
4.     Rows("3:1443").EntireRow.Hidden = False
   
5. End Sub

½Æ»s¥N½X

myleoyes

¦^´_ 4# Hsieh
hsieh«e½ú§A¦n!
       °¸¹³«e½ú!µ{¦¡§¹¥þ¥¿½TÁÂÁÂ!!½Ð«e½ú¦A¨¯­W,
       Åýµ{¦¡§ó¶i¤Æ,°ÝÃD¦pªþÀÉ»¡©ú
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target(1), Range("J3:J1443")) Is Nothing And Target(1) <> "" Then
       If [A1] <> "¨ú®ø" Then ActiveCell.Offset(0, -3).Copy [Y3]
          Me.Names.Add "x", ActiveCell.Offset(0, -3)
       If [A1] <> "¨ú®ø" Then Range(Target(1).Offset(1, -9), Target(1).Offset(1, -9).End
(xlDown)).EntireRow.Hidden = True
       Target.Offset(, -3) = Target.Value
       Range("G:J").EntireColumn.AutoFit
       Range("AA1") = 1
       Range("AB1") = ActiveCell.Row
     End If
     If Not Intersect(Target(1), Range("K3:K1443")) Is Nothing And Target(1) <> "" Then
        If [A1] <> "¨ú®ø" Then ActiveCell.Offset(0, -4).Copy [Y3]
          Me.Names.Add "x", ActiveCell.Offset(0, -4)
        If [A1] <> "¨ú®ø" Then Range(Target(1).Offset(1, -10), Target(1).Offset(1, -10).End
(xlDown)).EntireRow.Hidden = True
        Target.Offset(, -4) = Target.Value
        Range("G:J").EntireColumn.AutoFit
        Range("AA1") = 1
        Range("AB1") = ActiveCell.Row
     End If
     If Not Intersect(Target(1), Range("L3:L1443")) Is Nothing And Target(1) <> "" Then
        If [A1] <> "¨ú®ø" Then ActiveCell.Offset(0, -5).Copy [Y3]
           Me.Names.Add "x", ActiveCell.Offset(0, -5)
        If [A1] <> "¨ú®ø" Then Range(Target(1).Offset(1, -11), Target(1).Offset(1, -11).End
(xlDown)).EntireRow.Hidden = True
        Target.Offset(, -5) = Target.Value
        Range("G:J").EntireColumn.AutoFit
        Range("AA1") = 1
        Range("AB1") = ActiveCell.Row
     End If
     Select Case Target(1).Address(0, 0)
        Case "J1"
          ÁÙ­ì
     End Select
End Sub