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

vba ¦¹³B»Ý­nª«¥ó (¿ù»~ 424)

vba ¦¹³B»Ý­nª«¥ó (¿ù»~ 424)

½Ð°Ý
§Ú³]©wdata°}¦C¤¤ªº¯Á¤Þ­È¬°±ý§R°£ªº¤u§@ªí¦WºÙ
¨Ã§Æ±æ¥Îfor°j°é»Pif¨Ó§PÂ_©Ò«ü©wªº¤u§@ªí¬O§_¦s¦b
­Y¦s¦b«h§R°£
­Y¤£¦s¦b«h¸õ¥Xif¨ìfor ¨Ï¨ä­«·s§PÂ_
°õ¦æ®É¦b  If s1.Name = data Then   ³o¤@¦æµo¥Í¿ù»~
¥X²{  ¦¹³B»Ý­nª«¥ó (¿ù»~ 424)
¸Ó¦p¦ó­×§ïµ{¦¡½X©O
µ{¦¡½X¦p¤U©Ò¥Ü
¤Q¤À·PÁÂ

Public Sub §R°£¦h¾l¤u§@ªí()
    data = Array("¸ê®Æ1", "¸ê®Æ2", "¸ê®Æ3", "¸ê®Æ4", "¸ê®Æ5", "¸ê®Æ6", "¸ê®Æ7", "¸ê®Æ8", "³sµ²0", "³sµ²1", "³sµ²2", _
    "³sµ²3", "³sµ²4", "³sµ²5", "³sµ²6", "³sµ²7", "³sµ²8", "³sµ²9", "SERIES¤ÎPF¤ñ¸û¥i¦C¦L", "SERIES ¤ñ¸û¶K¤W", _
         "°O¿ýªíÂà´«¼ÐÅÒ", "¨Ó¦~¬ö¿ý¥i¶K¤W")
    For s1 = 0 To 21
    If s1.Name = data Then
    Application.DisplayAlerts = False
    s1.Delete '§R°£s1
    Application.DisplayAlerts = True
    End If
    Next
End Sub

½Ð°Ý
§Ú³]©wdata°}¦C¤¤ªº¯Á¤Þ­È¬°±ý§R°£ªº¤u§@ªí¦WºÙ
¨Ã§Æ±æ¥Îfor°j°é»Pif¨Ó§PÂ_©Ò«ü©wªº¤u§@ªí¬O§_¦s¦b
...
t591nm µoªí©ó 2015-8-7 17:10


¥H¤U½d¨Ò¨Ñ±z°Ñ¦Ò¡B­×§ï
  1.     Sub IsSheetExist()
  2.     Dim ws As Worksheet
  3.     Dim sName As String
  4.     sName = "¤@¤ë"  '«ü©w¤u§@ªí
  5.     On Error Resume Next
  6.     Set ws = Sheets(sName)
  7.     If ws Is Nothing Then '«ü©wªº¤u§@ªí¤£¦s¦b
  8.       Sheets.Add.Name = sName
  9.     Else '«ü©wªº¤u§@ªí¤w¦s¦b
  10.       MsgBox "¡§" & sName & "¡¨¤u§@ªí¤w¦s¦b¡C"
  11.       ws.Activate
  12.     End If
  13.     End Sub
½Æ»s¥N½X

  ¦h°µ¦h·Q¦h¾Ç²ß¡A¤Ö¬Ý¤Ö¿ù¤Ö°g³~

  ¦h°µ=¦h¦h½m²ß¡A¦h¦h½s¼g¡C
  ¦h·Q=·Q·Q¬°¤°»ò¤H®aµ{¦¡­n¨º¼Ë¼g¡A¦pªG´«¦¨¦Û¤v¡A¤S·|«ç¼g¡C
  ¦h¾Ç²ß=¾Ç²ß¤H®aªºµo°Ý¨Ã¸Ñµª¡A¾Ç²ß¤H®aªº¼gªk

  ¤Ö¬Ý=¥u¬Ý¤£°µ¤]ªPµM

TOP

¥»©«³Ì«á¥Ñ koo ©ó 2015-8-7 21:53 ½s¿è

¥\¤O¤£°÷¥u¯à¼g¨ì¦¹ÁöµM¥i¥H§R°£¦ýÁ`ı±o­þ¸Ì¦³°ÝÃD
  1. Sub ¥¨¶°4()
  2. On Error Resume Next
  3. Data = Array("¤u§@ªí2", "¤u§@ªí3", "¤u§@ªí4")
  4. For i = 1 To Sheets.Count
  5.     For J = LBound(Data) To UBound(Data)
  6.         If Sheets(i).Name = Data(J) Then
  7.             Application.DisplayAlerts = False
  8.             Sheets(i).Delete
  9.             Application.DisplayAlerts = True
  10.         End If
  11.     Next
  12. Next
  13. End Sub
½Æ»s¥N½X

TOP

­ËµÛ¶]
  1. Sub ¥¨¶°4()
  2. On Error Resume Next
  3. Data = Array("¤u§@ªí2", "¤u§@ªí3", "¤u§@ªí4")
  4. For i = Sheets.Count To 1 Step -1
  5.     For J = LBound(Data) To UBound(Data)
  6.         If Sheets(i).Name = Data(J) Then
  7.             Application.DisplayAlerts = False
  8.             Sheets(i).Delete
  9.             Application.DisplayAlerts = True
  10.         End If
  11.     Next
  12. Next
  13. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# t591nm
  1. For s1 = 0 To 21
  2.     If s1.Name = data Then
  3.         .
  4.         .
  5.     End If
  6. Next
½Æ»s¥N½X
s1 ¨Ã«D¬Oª«¥ó¡A¥¦¦b³o¸Ì§êºtªº¬O 0 ¨ì 21 ªº¾ã¼Æ­ÈÅܼơA
©Ò¥H¦b°õ¦æ®É¡A·|²£¥Í "¦¹³B»Ý­nª«¥ó (¿ù»~ 424)" ªº°T®§¡C
±µ¤U¨Ó§óÂ÷ÃЪº¬O  s1.Delete ' §R°£s1 ¡H¡H¡H

À³¥Î§A¼gªºµ{¦¡½Xµy¥[­×§ï¡A½Ð¦Û¦æ´ú¸Õ¡G
  1. Sub §R°£¦h¾l¤u§@ªí()
  2.     Dim ts As Integer, aList$
  3.    
  4.     Data = Array("¸ê®Æ1", "¸ê®Æ2", "¸ê®Æ3", "¸ê®Æ4", "¸ê®Æ5", "¸ê®Æ6", "¸ê®Æ7", "¸ê®Æ8", "³sµ²0", "³sµ²1", "³sµ²2", _
  5.           "³sµ²3", "³sµ²4", "³sµ²5", "³sµ²6", "³sµ²7", "³sµ²8", "³sµ²9", "SERIES¤ÎPF¤ñ¸û¥i¦C¦L", "SERIES ¤ñ¸û¶K¤W", _
  6.           "°O¿ýªíÂà´«¼ÐÅÒ", "¨Ó¦~¬ö¿ý¥i¶K¤W")
  7.     aList = Join(Data, ",")

  8.     Application.DisplayAlerts = False

  9.     For ts = Sheets.Count To 1 Step -1
  10.         If InStr(aList, Sheets(ts).Name) Then
  11.            Sheets(ts).Delete                 '  §R°£±ý§R°£ªº¤u§@ªí
  12.         End If
  13.     Next
  14.    
  15.     Application.DisplayAlerts = True
  16. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# mark15jill

±z¦n
³o¦n¹³¬O°w¹ï§R°£³æ¤@¤u§@ªí

TOP

¦^´_ 4# koo


For i = Sheets.Count To 1 Step -1  '½Ð°Ý¦¹¦æ¬O§_±N¤u§@ªí³Ì¤j­È-1
    For J = LBound(data) To UBound(data) '¦¹¦æ¬O§_±N©Ò³]ªº°}¦CÅܼƱa¤J §ä¥X³Ì¤j¨ì³Ì¤p
        If Sheets(i).Name = data(J) Then '¦A¤ñ¹ï¤u§@ªí»P°}¦CÅܼÆ
            Application.DisplayAlerts = False '­Y¤@¼Ë«hÃö³¬Äµ§iµøµ¡
            Sheets(i).Delete'¨Ã§R°£
            Application.DisplayAlerts = True '¦A±Nĵ§iµøµ¡¥´¶}

½Ð°Ý§Ú³o¼Ë²z¸Ñ¹ï¶Ü

TOP

¦^´_ 5# c_c_lai


¤£¦n·N«ä
¤p©f·s¤â¤W¸ô
¹ï©óÅ޿褣¬O¨º»ò¯à¥H²z¸Ñ

    aList$ '½Ð°Ý³o¬O«Å§i­n©ñ¦r¦êªº¦a¤è¶Ü
    aList = Join(Data, ",")'¦n¹³¬O³z¹L¦Û§Ú©w¸qªº°}¦CÅܼƤ¤¶Ç¦^¦h­Ó¦r¦ê
    For ts = Sheets.Count To 1 Step -1 '±N¤u§@ªíªº³Ì¤j­È-1
        If InStr(aList, Sheets(ts).Name) Then '·í°}¦CÅܼƤ¤ªº¦r¦êµ¥©ó¤u§@ªíªº¦WºÙ®É
           Sheets(ts).Delete                 '  §R°£¸Ó¤u§@ªí
        End If
    Next
    Application.DisplayAlerts = True'for°j°é¶]§¹§Y§âĵ§iµøµ¡¥´¶}


½Ð°Ý§Ú³o¼Ëªº¸ÑÄÀ¹ï¶Ü

TOP

¦^´_ 8# t591nm
  1. Sub §R°£¦h¾l¤u§@ªí()
  2.     Dim ts As Integer, aList$  
  3.     ' aList$ ³o¬O¦r¦ê«Å§i¡A µ¥©ó«Å§i¦¨  aList As String
  4.    
  5.     Data = Array("¸ê®Æ1", "¸ê®Æ2", "¸ê®Æ3", "¸ê®Æ4", "¸ê®Æ5", "¸ê®Æ6", "¸ê®Æ7", "¸ê®Æ8", "³sµ²0", "³sµ²1", "³sµ²2", _
  6.           "³sµ²3", "³sµ²4", "³sµ²5", "³sµ²6", "³sµ²7", "³sµ²8", "³sµ²9", "SERIES¤ÎPF¤ñ¸û¥i¦C¦L", "SERIES ¤ñ¸û¶K¤W", _
  7.           "°O¿ýªíÂà´«¼ÐÅÒ", "¨Ó¦~¬ö¿ý¥i¶K¤W")
  8.     '  Example :  Data °}¦CÅÜ¼Æ Data(0) ~ Data(21) ªº¦s©ñ¤º®e­È¡C
  9.     '  Data :  :  Variant/Variant(0 to 21)
  10.     '  Data(0) :  "¸ê®Æ1" : Variant/String
  11.     '  Data(1) :  "¸ê®Æ2" : Variant/String
  12.     '  Data(2) :  "¸ê®Æ3" : Variant/String
  13.     '  .
  14.     '  .
  15.     '  .
  16.     '  Data(20) : "°O¿ýªíÂà´«¼ÐÅÒ" : Variant/String
  17.     '  Data(21) : "¨Ó¦~¬ö¿ý¥i¶K¤W" : Variant/String
  18.    
  19.     aList = Join(Data, ",") '  ¶°¦X°}¦C Data ¤º®e¡A¨Ã¥H "," §@¬°¤À¹j²Å¸¹¡Cµ²ªG¦p¤U¡G
  20.     '  aList = "¸ê®Æ1,¸ê®Æ2,¸ê®Æ3,¸ê®Æ4,¸ê®Æ5,¸ê®Æ6,¸ê®Æ7,¸ê®Æ8,³sµ²0,³sµ²1,³sµ²2,³sµ²3,³sµ²4,³sµ²5,³sµ²6,³sµ²7,³sµ²8,³sµ²9,SERIES¤ÎPF¤ñ¸û¥i¦C¦L,SERIES ¤ñ¸û¶K¤W,°O¿ýªíÂà´«¼ÐÅÒ,¨Ó¦~¬ö¿ý¥i¶K¤W" : String
  21.    
  22.     Application.DisplayAlerts = False
  23.      
  24.     For ts = Sheets.Count To 1 Step -1
  25.         '  ¦pªG aList ¦r¦ê¤¤¦³²Å¦X¸Óªí³æ¦WºÙªÌ¡A«h¤©¥H§R°£¡C
  26.         '  Ä´¦p¡G¸Óªí³æ¦WºÙ¬° "°O¿ýªíÂà´«¼ÐÅÒ"¡A«h¤ñ¹ïµ²ªGµo²{¸Ó aList ¦r¦ê¤¤¦³¦¹¦r¦ê¦WºÙ¡C
  27.         '        «hª½±µ³w¦æ§R°£¡F§_«h¤ñ¹ï¤£¦¨¥\«h¤£³B²z¡C
  28.         If InStr(aList, Sheets(ts).Name) Then
  29.            Sheets(ts).Delete                 '  §R°£±ý§R°£ªº¤u§@ªí
  30.         End If
  31.     Next
  32.    
  33.     Application.DisplayAlerts = True
  34. End Sub
½Æ»s¥N½X

TOP

¦^´_ 9# c_c_lai

²M·¡¥B©úÁA

¤Q¤À·P®¦c¤j

TOP

        ÀR«ä¦Û¦b : ¦³Ä@©ñ¦b¤ß¸Ì¡A¨S¦³¨­Åé¤O¦æ¡A¥¿¦p¯Ñ¥Ð¤£¼½ºØ¡A¬Ò¬OªÅ¹L¦]½t¡C
ªð¦^¦Cªí ¤W¤@¥DÃD