¥i§_²¤Æ VBA »yªk

jsc0518

¤j®a¦n¡A§Ú¼g¤@¦ê»yªk
¦ý¦Ûı±o¹Lµ{¤Ó¤¾ªø¡A¬O§_¥i¥H²¤Æµ{¦¡»yªk


±ø¥ó1.¦bAÄæ¦ì¡A¥u­n¥X²{¬O"ªÅ¥Õ"¡A´N¾ã¦C²¾°£
±ø¥ó2.¦bAÄæ¦ì¡A¥u­n¥X²{³o¨Ç­^¤å¡A´N¾ã¦C²¾°£ : TPD . TPD-both . TPD-viewing . GSA

Sub Step1()

On Error Resume Next
[A:A].SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

Sub Step2()

Dim yy
yy = "TPD"
For I = [a65536].End(xlUp).Row To 1 Step -1
If WorksheetFunction.CountIf(Rows(I), yy) > 0 Then Rows(I).Delete
Next I
End Sub

Sub Step3()

Dim yy
yy = "TPD-both"
For I = [a65536].End(xlUp).Row To 1 Step -1
If WorksheetFunction.CountIf(Rows(I), yy) > 0 Then Rows(I).Delete
Next I
End Sub

Sub Step4()

Dim yy
yy = "TPD-viewing"
For I = [a65536].End(xlUp).Row To 1 Step -1
If WorksheetFunction.CountIf(Rows(I), yy) > 0 Then Rows(I).Delete
Next I
End Sub

Sub Step5()

Dim yy
yy = "GSA"
For I = [a65536].End(xlUp).Row To 1 Step -1
If WorksheetFunction.CountIf(Rows(I), yy) > 0 Then Rows(I).Delete
Next I
End Sub

¦b³Â·Ð¦U¦ì¥ý¶i!

jsc0518

¦^´_ 22# Kubi

·PÁ§Aªº±Ð¾É!

Kubi

¦^´_ 21# jsc0518
. ¬O¥Î©ó¼ÐÃѤl¦r¦êÃä¬Éªº¦r¦ê¦r¤¸¡A¥H²£¥Í¦U¦r¦ê©Ò²Õ¦¨ªº¤@ºû°}¦C¡C
§A¤]¥i¥HÀH·N©w¸q°Ï¹j¦r¦ê¦r¤¸¡A¨Ò¦p§ï¥Î³r¸¹(,)¤]¥i¥H¡C
­Y§ï¦¨³r¸¹·|¹³¦p¤U³¯­z¡G
Split("TPD,TPD-both,TPD-viewing,GSA", ",")

jsc0518

¦^´_ 18# Kubi


½Ð°Ý
1.³o¬q»yªk --> For Each x In Split("TPD.TPD-both.TPD-viewing.GSA", ".")
  ¤ºªº "." ¬Oªí¬O¬Æ»ò? ¬O¤£¬O²¾°£AÄæªÅ¥Õ¦C?

Thanks!

jsc0518

¦^´_ 17# ­ã´£³¡ªL


   ¤F¸Ñ¡A·PÁ±zªº¦^ÂÐ!

ML089

¦^´_ 13# jsc0518

³Ì«á¤@¦C Range([A1], ActiveSheet.UsedRange).Rows.Count °Ñ¦Ò14¼Ó

Sub ex()
    For i = Range([A1], ActiveSheet.UsedRange).Rows.Count To 1 Step -1
        For Each x In Array("", "TPD", "TPD-both", "TPD-viewing", "GSA")
            If Cells(i, 1) = x Then Cells(i, 1).EntireRow.Delete
        Next
    Next
End Sub

Kubi

¤£¥Î½d³òª«¥óÅܼơAÀ³¥i¥[§Ö°õ¦æ³t«×¡C

Sub test()
    Dim d As Object
    Dim arr
    st = Timer
    Set d = CreateObject("Scripting.Dictionary")
    For Each x In Split("TPD.TPD-both.TPD-viewing.GSA", ".")
        d(x) = ""
    Next x
    arr = Range("A1:A" & ActiveSheet.UsedRange.Rows.Count)
    For i = 1 To UBound(arr)
        If d.exists(arr(i, 1)) Then arr(i, 1) = ""
    Next i
    [A1].Resize(UBound(arr), 1) = arr
    Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Set d = Nothing
    arr = ""
    MsgBox Format(Timer - st, "0.0¬í")
End Sub

­ã´£³¡ªL

¦^´_ 16# jsc0518


If xR = "" Or InStr(T, "_" & xR & "_") Then Set xE = Union(xE, xR)
´N¤w¥]§t"ªÅ¥Õ"¤Î"ÃöÁä¦r",
¯u¥¿°ÝÃD¬O, HÄæ³Ì«á¤@®æ»PAÄ椣¤@­P(¸û¦h),
©Ò¥H¥u¥ÎAÄæ¥hÀË´ú³Ì«á¤@®æ, ´Nº|¤FHÄæ,
¥ÎUsedRange¥i¥H¥]§t©Ò¦³¤w¨Ï¥Î°Ï¶ô,
¦ý­Y«e´X¦C¬O§¹¥þªÅ¥Õ, ±Nº|§ì³o«e´X¦C,
¬G, ¥Î Range([A1], UsedRange) ¨Ó¨ç»\¾ã­Ó°Ï¶ô!

jsc0518

¦^´_ 14# ­ã´£³¡ªL


Dear ­ã´£³¡ªL
´ú¸Õ¦¨¥\
½Ð°Ý­þ¤@­Ó»yªk¬O§R°£AÄæªÅ¥Õ¦Cªü?

ikboy

¨ä¹ê­ã´£³¡ªL¤jªº¤w¸Ñ¨M¤F, ³o°N¬O¥t¤@¤èªk:

1. Sub zz()
   
2. Application.ScreenUpdating = 0
   
3. Dim x(), rng As Range, ar, Myr&
   
4. x = Array("TPD", "TPD-both", "TPD-viewing", "GSA")
   
5. Myr = IIf([a65536].End(3).Row > [h65536].End(3).Row, [a65536].End(3).Row, [a65536].End(3).Row)
   
6. ar = Range("a1:a" & Myr)
   
7. Set rng = Cells(Myr + 1, 1)
   
8. For i = 1 To UBound(ar)
   
9.     If Len(ar(i, 1)) = 0 Then
   
10.         Set rng = Union(rng, Cells(i, 1))
    
11.     Else
    
12.         For j = 0 To UBound(x)
    
13.             If InStr(ar(i, 1), x(j)) Then Set rng = Union(rng, Cells(i, 1))
    
14.         Next
    
15.     End If
    
16. Next
    
17. rng.EntireRow.Delete
    
18. Application.ScreenUpdating = 1
    
19. End Sub

½Æ»s¥N½X