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

½Ð°Ý¡GÃö©ó­×§ïVBA»yªk

½Ð°Ý¡GÃö©ó­×§ïVBA»yªk

Dear ¥ý¶i,

VBA»yªk¦p¤U¡A»yªk¤º¦³§â"ªÅ¥Õ"³£µ¹§R°£
§Ú·Q§â"­Y¹J¨ìAÄæ¦CªÅ¥Õ®É¡A¤£­n§R°£"¡A½Ð°Ý¬O­n­×¥¿­þ¤@¬q»yªk©O¡H

·PÁ¦U¦ì¡I

Sub Step1()
'§R°£ key word : ¤½¥q / ³æ§O: / ¤é´Á* / ²£«~¤jÃþ: / ®Ö­ã / ¦X­p* / ²Î­p*
Dim reg As Object, a, rng As Range, z&
a = Range("a1:a" & Cells(Rows.Count, 1).End(3).Row).Value
z = UBound(a)
Set rng = Rows(z + 1)
Set reg = CreateObject("vbscript.regexp")
With reg
    .Pattern = "ªÅ¥Õ|¤½¥q|³æ§O:|¤é´Á|²£«~¤jÃþ:|®Ö­ã|¦X­p|Á`­p"
    For i = 1 To z
        If Len(a(i, 1)) = 0 Or .test(a(i, 1)) Then
            Set rng = Union(rng, Rows(i))
        End If
    Next
End With
rng.Delete
Set rng = Nothing
End Sub
Just do it.

try this:
  1. Sub Step1()
  2. '§R°£ key word : ¤½¥q / ³æ§O: / ¤é´Á* / ²£«~¤jÃþ: / ®Ö­ã / ¦X­p* / ²Î­p*
  3. Dim reg As Object, a, rng As Range, z&
  4. a = Range("a1:a" & Cells(Rows.Count, 1).End(3).Row).Value
  5. z = UBound(a)
  6. Set rng = Rows(z + 1)
  7. Set reg = CreateObject("vbscript.regexp")
  8. With reg
  9.     .Pattern = "ªÅ¥Õ|¤½¥q|³æ§O:|¤é´Á|²£«~¤jÃþ:|®Ö­ã|¦X­p|Á`­p"
  10.     For i = 1 To z
  11.         If .test(a(i, 1)) Then
  12.             Set rng = Union(rng, Rows(i))
  13.         End If
  14.     Next
  15. End With
  16. rng.Delete
  17. Set rng = Nothing
  18. End Sub
½Æ»s¥N½X

TOP

¦^´_ 2# ikboy
Dear ÁÂÁ±zªº¦^ÂÐ,
¤U¦C»yªk¬O±NAÄæ¦C­Y¦³¥X²{ÃöÁä¦r®É¡A´N§R°£¸Ó¦C
¦ý§Ú·Q§â¨ä¤¤¤@­ÓÃöÁä¦r"ªÅ¥Õ"-->­Y¹JAÄæ¦C¬°ªÅ¥Õ®É¡A´N¤£­n§R°£
§Ú§ï¤F
­ì¥»¡G    .Pattern = "ªÅ¥Õ|¤½¥q|³æ§O:|¤é´Á|²£«~¤jÃþ:|®Ö­ã|¦X­p|Á`­p"
§ó§ï¡G    .Pattern = "¤½¥q|³æ§O:|¤é´Á|²£«~¤jÃþ:|®Ö­ã|¦X­p|Á`­p"
¦ýAÄæ¦CªÅ¥Õ¸Ó¦C¤]·|¤@¨Ö§R°£
¬O§_­þ¤@¬qVBA»yªk»Ý°µ­×§ï©O¡H








Sub Step1()
'§R°£ key word : ¤½¥q / ³æ§O: / ¤é´Á* / ²£«~¤jÃþ: / ®Ö­ã / ¦X­p* / ²Î­p*
Dim reg As Object, a, rng As Range, z&
a = Range("a1:a" & Cells(Rows.Count, 1).End(3).Row).Value
z = UBound(a)
Set rng = Rows(z + 1)
Set reg = CreateObject("vbscript.regexp")
With reg
    .Pattern = "ªÅ¥Õ|¤½¥q|³æ§O:|¤é´Á|²£«~¤jÃþ:|®Ö­ã|¦X­p|Á`­p"
    For i = 1 To z
        If Len(a(i, 1)) = 0 Or .test(a(i, 1)) Then
            Set rng = Union(rng, Rows(i))
        End If
    Next
End With
rng.Delete
Set rng = Nothing
End Sub
Just do it.

TOP

¦^´_ 3# jsc0518
§R°£ key word : ¤½¥q / ³æ§O: / ¤é´Á* / ²£«~¤jÃþ: / ®Ö­ã / ¦X­p* / ²Î­p*
.Pattern = "ªÅ¥Õ|¤½¥q|³æ§O:|¤é´Á|²£«~¤jÃþ:|®Ö­ã|¦X­p|Á`­p"
¨S¦³¤@­P ¦³"ªÅ¥Õ" ·íµM·|§R°£
  1. Option Explicit
  2. Sub Step1()
  3.     Dim key_word$, E As Range, Rng As Range
  4.     key_word = "/¤½¥q/³æ§O:/¤é´Á/²£«~¤jÃþ:/®Ö­ã/¦X­p/²Î­p/"
  5.     For Each E In Range("a1:a" & Cells(Rows.Count, 1).End(3).Row)
  6.         If InStr(key_word, "/" & E & "/") Then
  7.             If Rng Is Nothing Then
  8.                 Set Rng = E
  9.             Else
  10.                 Set Rng = Union(Rng, E)
  11.             End If
  12.         End If
  13.     Next
  14.     If Not Rng Is Nothing Then Rng.EntireRow.Delete

  15. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

[ª©¥DºÞ²z¯d¨¥]
  • GBKEE(2018/2/3 13:45): ªþÀɬݬÝ

¦^´_ 4# GBKEE
·PÁª©¥Dªº¦^ÂÐ
TEST OK

§Ú¦A§â±z¤À¨ÉªºVBA»yªk§ï¦¨
¥u­nBÄæ¦C¥X²{"»â®Æ"©Î¬O"»â¥Î"¡A´N¾ãÄæ¦Cµ¹¤©§R°£
°õ¦æ«á¡ABÄæ¦C·|§R°£"»â®Æ"©Î¬O"»â¥Î"
¦ý¤´·|¦³¤@¨Ç"»â®Æ"©Î¬O"»â¥Î"ªºBÄæ¦C¤´¦s¦b¡A§R°£¤£°®²b
¬O§_­þ¸Ì»Ý­×­q¡H
·P®¦


Sub Step2()
'§R°£ÃöÁä¦r-BÄæ¦C--> »â®Æ »â¥Î
    Dim key_word$, E As Range, Rng As Range
    key_word = "/»â¥Î/»â®Æ/"
    For Each E In Range("b1:b" & Cells(Rows.Count, 1).End(3).Row)
        If InStr(key_word, "/" & E & "/") Then
            If Rng Is Nothing Then
                Set Rng = E
            Else
                Set Rng = Union(Rng, E)
            End If
        End If
    Next
    If Not Rng Is Nothing Then Rng.EntireRow.Delete

End Sub
Just do it.

TOP

¦^´_ 4# GBKEE
Dear ª©¥D

ªþÀÉ®× TT.rar (82.91 KB)

°õ¦æstep2¡A·|¦³²¾°£¤£°®²bªºµe­±


³Ì²×ªº¸ê®Æ¬O§Æ±æ
1.¥i¥H§R°£¤£¥²­nªºÄæ¦C
2.¥t¥~¦bAÄæ¤U±N¦³ªÅ¥Õ¤§Äæ¦ì¨ä¤é´Á¦Û°Ê»P¤W¤@Äæ¦ì¤@¼Ë


ÁÂÁ±z
Just do it.

TOP

¦^´_ 6# jsc0518
Step2  À³¬° 2 ->BÄæ .1 ¬°AÄæ
For Each E In Range("b1:b" & Cells(Rows.Count, 1).End(3).Row)
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Step1()
  3. '§R°£ÃöÁä¦r-AÄæ¦C
  4. Dim key_word$, E As Range, Rng As Range
  5. 'key_word = "/¤½¥q/³æ§O:/¤é´Á:/¤é´Á/²£«~¤jÃþ:/®Ö­ã ÂÐ®Ö ³æ¦ì¥DºÞ °]·|¼f®Ö »sªí¤H /¦X­p/¦X­p:/²Î­p/Á`­p:/"
  6. For Each E In ActiveSheet.UsedRange.Columns(1).Cells
  7. If (Not IsDate(E) And E <> "") Or E.Cells(1, 2) <> "" Then
  8. If Rng Is Nothing Then
  9. Set Rng = E
  10. Else
  11. Set Rng = Union(Rng, E)
  12. End If
  13. End If
  14. Next
  15. If Not Rng Is Nothing Then Rng.EntireRow.Delete
  16. For Each E In Range("a1:a" & Cells(Rows.Count, 2).End(xlUp).Row)
  17. If E(1, 3) <> "" And E = "" Then E = E.Cells(0)
  18. Next
  19. If [A1].End(xlDown).End(xlDown).Row <> Rows.Count Then
  20. Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  21. End If
  22. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 7# GBKEE
Deae ª©¥D,
TEST OK¡A±z¯uªº¦n¯«¼Ú
¯uªº«Ü·PÁ±zªºÀ°¦£¡I
Just do it.

TOP

¦^´_ 7# GBKEE
Dear ª©¥D
§Ú¥Î±z±Ð¾ÉVBA¥h°õ¦æ¡A»P§Ú¦Û¤v¥Î¤â°Ê§R°£«á¤§µ²ªG¼Æ¾Ú¦³®t²§

VBA°õ¦æ«á¥[Á`JÄæ¦ì®w¦s¼Æ¾Ú
VBA.jpg
   
¤â°Ê§R°£Äæ¦C¡A¥[Á`JÄæ¦ì®w¦s¼Æ¾Ú
¤â.jpg

ªþÀÉ®×
TT.rar (144.61 KB)

½ÐÀ°¦£¬Ý¤@¤U¡I·P®¦¡I
Just do it.

TOP

¦^´_ 9# jsc0518
§Aªºªí®æ¦³²Ä¤@µ§¸ê®Æ¶}©lAÄæ¨S¦³¤é´Á©Ò­P
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Step1()
  3.     Dim E As Range, Rng As Range
  4.     For Each E In ActiveSheet.UsedRange.Columns("A:C").Rows
  5.        If (Not IsDate(E.Cells(1)) And E.Cells(1) <> "") Or E.Cells(2) <> "" Or Application.CountA(E.Cells) = 0 Then
  6.             If Rng Is Nothing Then
  7.                 Set Rng = E
  8.             Else
  9.                 Set Rng = Union(Rng, E)
  10.             End If
  11.         End If
  12.     Next
  13.     If Not Rng Is Nothing Then Rng.EntireRow.Delete
  14.     For Each E In Range("a1:a" & Cells(Rows.Count, 2).End(xlUp).Row)
  15.         If E(1, 3) <> "" And E = "" Then E = E.Cells(0)
  16.     Next
  17. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¦h°µ¦h±o¡C¤Ö°µ¦h¥¢¡C
ªð¦^¦Cªí ¤W¤@¥DÃD