VBA求助，条件太长，求写短一点效果一样，谢谢在线等

379052149

VBA小白求助，求大神帮助，跪拜！！！
Sub L()

Application.ScreenUpdating = False

Dim EndRow As Single

EndRow = Range("a65535").End(xlUp).Row

i = 2

While i <= EndRow

   If Cells(i, 1) = "Dischage" Or Cells(i, 5) = "SKR" Or Cells(i, 5) = "TSL" _
   Or Cells(i, 5) = "COS" Or Cells(i, 5) = "ZIM" Or Cells(i, 5) = "CSE" Or Cells(i, 5) = "IAL" Or Cells(i, 5) = "KMT" Or Cells(i, 5) = "HMM" _
   Or Cells(i, 5) = "CUL" Or Cells(i, 2) = "R" Or Cells(i, 5) = "ONE" Or Cells(i, 8) = "MEA3" And Cells(i, 5) = "HLC" Or Cells(i, 8) = "CGX" And Cells(i, 5) = "YML" _
   Or Cells(i, 8) = "JTS" And Cells(i, 5) = "YML" Or Cells(i, 8) = "CAT" And Cells(i, 5) = "YML" Or Cells(i, 8) = "CAT" And Cells(i, 5) = "YML" Or _
   Cells(i, 8) = "CTI" And Cells(i, 5) = "YML" Or Cells(i, 8) = "JSM2" And Cells(i, 5) = "YML" Or Cells(i, 8) = "CHX" And Cells(i, 5) = "YML" _
   Or Cells(i, 8) = "CPX" And Cells(i, 5) = "YML" Or Cells(i, 8) = "MD2" And Cells(i, 5) = "YML" Or Cells(i, 8) = "AIS" And Cells(i, 5) = "WHL" _
   Or Cells(i, 8) = "ANX" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "APG" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "JCV2S" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "JCV2N" And Cells(i, 5) = "WHL" _
   Or Cells(i, 8) = "EXT1" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "KCVS" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "KCVN" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "WSA2" And Cells(i, 5) = "WHL" _
   Or Cells(i, 8) = "MEXW" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "NE7" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "NTEN" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "TPF" And Cells(i, 5) = "WHL" _
   Or Cells(i, 8) = "NSC" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "CI3" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "JTS" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "NSA" And Cells(i, 5) = "WHL" _
   Or Cells(i, 8) = "JCVN" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "NCT" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "SCT3" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "MEA3" And Cells(i, 5) = "WHL" _
   Or Cells(i, 8) = "NS1" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "CI1" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "CI2" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "KSS" And Cells(i, 5) = "WHL" _
   Or Cells(i, 8) = "CV3" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "CS1" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "CIX" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "JST" And Cells(i, 5) = "WHL" _
   Or Cells(i, 8) = "CTK" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "JKH" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "SCM" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "SES" And Cells(i, 5) = "WHL" _
   Or Cells(i, 8) = "NSCT1" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "NTES" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "ACSAI" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "WSA4" And Cells(i, 5) = "WHL" _
   Or Cells(i, 8) = "WAX" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "NS5" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "PMX" And Cells(i, 5) = "WHL" Or Cells(i, 8) = "AR1" And Cells(i, 5) = "WHL" _
   Or Cells(i, 8) = "CCS" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "CAT" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "STA" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "CNS" And Cells(i, 5) = "PIL" _
   Or Cells(i, 8) = "SW2" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "NCS" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "CVT" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "MEA3" And Cells(i, 5) = "PIL" _
   Or Cells(i, 8) = "CM5" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "NEU3" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "FES" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "ASA" And Cells(i, 5) = "PIL" _
    Or Cells(i, 8) = "A3S" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "PIX" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "PM2" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "AGX WB" And Cells(i, 5) = "PIL" _
     Or Cells(i, 8) = "CM3" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "CI2" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "CI1" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "RSSW" And Cells(i, 5) = "PIL" _
      Or Cells(i, 8) = "CMSE" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "CMSW" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "SPS" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "WSA2" And Cells(i, 5) = "PIL" _
       Or Cells(i, 8) = "WS2" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "SSA" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "RSS" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "RES" And Cells(i, 5) = "PIL" _
        Or Cells(i, 8) = "NS1" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "PMX" And Cells(i, 5) = "PIL" Or Cells(i, 8) = "RES1" And Cells(i, 5) = "PIL" Then

      Rows(i).Delete

   Else

      i = i + 1

   End If

Wend

Application.ScreenUpdating = True

End Sub
IF后面的条件写短一点，在线等！！谢谢各位大神。

wzsy2_mrf

上附件看看

lsc900707

晕菜，还是直接上附件，说要求吧。

379052149

lsc900707 发表于 2019-4-21 18:59
晕菜，还是直接上附件，说要求吧。

Cells(i, 5) = "YML" _
   Or Cells(i, 8) = "JTS" And Cells(i, 5) = "YML" Or Cells(i, 8) = "CAT" And Cells(i, 5) = "YML" Or Cells(i, 8) = "CAT" And Cells(i, 5) = "YML" Or _
   Cells(i, 8) = "CTI" And Cells(i, 5) = "YML" Or Cells(i, 8) = "JSM2" And Cells(i, 5) = "YML" Or Cells(i, 8) = "CHX" And Cells(i, 5) = "YML" _
上面的多个条件指向YML   如   JTS   ,CAT,   CTI, JSM2,CHX.把能不能把这条件写一在起指向YML。

唐朝123

佩服你，写这么多or和and，已经超过了我的耐心。
建议你将需要删除的条件列出来，就是单独列一个表，说明满足哪些条件就删除，你这个吓人的代码放出来，没办法帮你。

qingc0221

说实话,没看完

咱村

留个足迹，回头来学习

wzsy2_mrf

1. Sub L()
   
2. Application.ScreenUpdating = False
   
3. Dim EndRow&, i&, str51$, str81$, str82$, str83$
   
4. EndRow = Range("a65535").End(xlUp).Row
   
5. i = EndRow
   
6. str51 = "SKR,TSL,COS,ZIM,CSE,IAL,KMT,HMM,CUL,ONE"
   
7. str81 = "CGX, JTS, CAT, CTI, JSM2, CHX, CPX, MD2"
   
8. str82 = "AIS,ANX,APG,JCV2S,JCV2N,EXT1,KCVS,KCVN,WSA2,MEXW,NE7,NTEN,TPF,NSC,CI3,JTS,NSA,JCVN,NCT," _
   
9.         & "SCT3,MEA3,NS1,CI1,CI2,KSS,CV3,CS1,CIX,JST,CTK,JKH,SCM,SES,NSCT1,NTES,ACSAI,WSA4,WAX,NS5,PMX,AR1"
   
10. str83 = "CCS,CAT,STA,CNS,SW2,NCS,CVT,MEA3,CM5,NEU3,FES,ASA,A3S,PIX,PM2,AGX WB,CM3,CI2,CI1,RSSW,CMSE,CMSW,SPS,WSA2,WS2,SSA,RSS,RES,NS1,PMX,RES1"
    
11. While i > 2
    
12.    If Cells(i, 1) = "Dischage" _
    
13.         Or Cells(i, 2) = "R" _
    
14.         Or InStr(str51, Cells(i, 5)) _
    
15.         Or Cells(i, 8) = "MEA3" And Cells(i, 5) = "HLC" _
    
16.         Or InStr(str81, Cells(i, 8)) And Cells(i, 5) = "YML" _
    
17.         Or InStr(str82, Cells(i, 8)) And Cells(i, 5) = "WHL" _
    
18.         Or InStr(str83, Cells(i, 8)) And Cells(i, 5) = "PIL" _
    
19.    Then Rows(i).Delete
    
20.    i = i - 1
    
21. Wend
    
22. Application.ScreenUpdating = True
    
23. End Sub
    
24. 
    

复制代码

wzsy2_mrf

代码审核中。
其实用instr函数就可简化。

fop1688

真是奇葩，这么多条件，头都昏了；你把真正的要求列个表，用数组和循坏的方式做出判断