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

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

¥»©«³Ì«á¥Ñ ML089 ©ó 2017-2-8 16:03 ½s¿è

¦^´_ 9# jsc0518


AÄæªÅ¥Õ®É­YHÄ榳¸ê®Æ ¤£§R°£¶Ü?

Sub ex()
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
        For Each x In Array("", "TPD", "TPD-both", "TPD-viewing", "GSA")
            If Cells(i, 1) = "" And Cells(i, "H") <> "" Then Exit For
            If Cells(i, 1) = x Then Cells(i, 1).EntireRow.Delete
        Next
    Next
End Sub
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

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


Dear ikboy / ­ã´£³¡ªL / ML089

§Ú¦bHÄ榳³]©w¸ê®Æ¡A¦ýAÄæ¬OªÅ¥Õªº¡A­YHÄæ¸ê®Æ¬O¦ì©ó³Ì¤U¤è®É¡Aµ{¦¡µLªk§R°£¸Ó¦C

01.jpg
2017-2-8 15:58


02.jpg
2017-2-8 15:58


028-01.zip (10.39 KB)
Just do it.

TOP

¦^´_ 11# ML089


AÄæªÅ¥Õ®É­YHÄ榳¸ê®Æ ¤£§R°£¶Ü?   --> AÄæªÅ¥Õ¡A¤@«ß­n§R°£
Just do it.

TOP

Sub zz2()
Dim T$, xR As Range, xE As Range, R&
T = "_TPD_TPD-both_TPD-viewing_GSA_"
R = Range([A1], ActiveSheet.UsedRange).Rows.Count
Set xE = Range("A" & R + 1)
For Each xR In Range([A1], xE(0))
¡@¡@If xR = "" Or InStr(T, "_" & xR & "_") Then Set xE = Union(xE, xR)
Next
xE.EntireRow.Delete
End Sub

TOP

¨ä¹ê­ã´£³¡ª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

TOP

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


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

TOP

¦^´_ 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) ¨Ó¨ç»\¾ã­Ó°Ï¶ô!

TOP

¤£¥Î½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
[b]Kubi[/b]

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2017-2-9 14:30 ½s¿è

¦^´_ 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
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

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


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

TOP

        ÀR«ä¦Û¦b : ¤Hªº¤ß¦a¬O¤@²¥¥Ð¡A¤g¦a¨S¦³¼½¤U¦nºØ¤l¡A¤]ªø¤£¥X¦nªºªG¹ê¡C -
ªð¦^¦Cªí ¤W¤@¥DÃD