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

jsc0518

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

ikboy

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

jsc0518

¦^´_ 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

GBKEE

¦^´_ 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. 
    
16. End Sub

½Æ»s¥N½X

jsc0518

¦^´_ 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

jsc0518

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

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

¤U¸ü¦¸¼Æ: 5

2018-2-3 17:14

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

¤U¸ü (23.26 KB)

2018-2-3 17:15

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

¤U¸ü (152.77 KB)

2018-2-3 17:18

ÁÂÁ±z

GBKEE

¦^´_ 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

jsc0518

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

jsc0518

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

VBA°õ¦æ«á¥[Á`JÄæ¦ì®w¦s¼Æ¾Ú

¤U¸ü (4 KB)

2018-2-4 10:26

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

¤U¸ü (4.22 KB)

2018-2-4 10:26

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

¤U¸ü¦¸¼Æ: 3

2018-2-4 10:28

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

GBKEE

¦^´_ 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