خطای Too many line continuations هنگام ذخیره ماکرو در اکسل 2013

[yogishiip]

سلام
من هنگام ذخیره ماکرو وقتی یک ستون رو فیلتر میکنم و یکی از تیک ها رو برمیدارم این ارور رو میبینم و رکورد ماکرو هم قطع میشه.

---------------------------
Microsoft Visual Basic for Applications
---------------------------
Too many line continuations
---------------------------
OK   Help   
---------------------------

خود ماکرو هم اینه

Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
'
    Selection.AutoFilter
    ActiveSheet.Range("$L$1:$L$786093").AutoFilter Field := 1, Criteria1 := Array( _
        "1","10","100","101","102","103","104","105","106","107","108","109","11","110" _
        ,"111","112","113","114","115","116","117","118","119","12","120","121","122", _
        "123","124","125","126","127","128","129","13","130","131","132","133","134", _
        "135","136","137","138","139","14","140","141","142","143","144","145","146", _
        "147","148","149","15","150","151","152","153","154","155","156","157","158", _
        "159","16","160","161","162","163","164","165","166","167","168","169","17", _
        "170","171","172","173","174","175","176","177","178","179","18","180","181", _
        "182","183","184","185","186","187","188","189","19","190","191","192","193", _
        "194","195","196","197","198","199","2","20","200","201","202","203","204", _
        "205","206","207","208","209","21","210","211","212","213","214","215","216", _
        "217","218","219","22","220","221","222","223","224","225","226","227","228", _
        "229","23","230","231","232","233","234","235","236","237","238","239","24", _
        "240","241","242","243","244","245","246","247","248","249","25","250","251", _
        "252","253","254","255","256","257","258","259","26","260","261","262","263", _
        "264","265","266","267","268","269","27","270","271","272","273","274","275", _
        "276","277","278","279","28","280","281","282","283","284","285","286","287", _
        "288","289","29","290","291","292","293","294","295","296","297","298","299", _
        "30","300","301","302","303","304","305","306","307","308","309","31","310", _
        "311","312","313","314","315","316","317","318","319","32","320","321","322", _
        "323","324","325","326","327","328","329","33","330","331","332","333","334", _
        "335","336","337","338","339","34","340","341","342","343","344","345","346", _
        "347","348","349","35","350","351","352","353","354","355","356","357","358", _
        "359","36","360","361","362","363","364","365","366","367","368","369","37", _
End Sub

اگر بخوام به صورت شرطی بنویسم باید چیکار کنم؟
نمیدونم چطور منظورمو برسونم یعنی میخوام بهش بگم در این ستون این مقدار رو فیلتر کن و نشون نده .

اخطار: این یک موضوع قدیمی است به دلیل قدیمی بودن موضوع، ممکن است برخی فایل های ضمیمه به درستی کار نکنند. لطفا در صورت عدم ضرورت، از بالا آوردن موضوعات قدیمی خودداری نمایید.

[iranweld]

با سلام

اگر میخواهید اعمال فیلتر برای عدم نمایش اعداد کوچکتر از 370 باشد از ماکروی ذیل استفاده کنید

Sub Macro1()

    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$A$1001").AutoFilter Field:=1, Criteria1:=">370", _
        Operator:=xlAnd
End Sub 

[yogishiip]

ممنون از پاسختون .
من میخوام ماکرو برام فیلتر روانجام بده

در حال حاضر وقتی این کد رو اجرا میکنم

    Columns("K:K").Select
    Selection.AutoFilter
    ActiveSheet.Range("$K$1:$K$273667").AutoFilter Field:=1, Criteria1:=Range("K5").Value, _
        Operator:=xlAnd
End Sub

بر اساس کلمه موجود در سلول K5 فیلتر میشه و فقط سلول K5 و سایر سلول هایی که کلمه مشابه سلول K5 در اون هست رو میبینم
حالا میخوام برعکسش اتفاق بیفته

یعنی تمام سلول های ستون K رو ببینم جز سلول های مشابه K5
این کد جواب نمیده:

    Columns("K:K").Select
    Selection.AutoFilter
    ActiveSheet.Range("$K$1:$K$273667").AutoFilter Field:=1, Criteria1:=Range("<>K5").Value, _
        Operator:=xlAnd
End Sub

[iranweld]

فایل پیوست را ملاحظه کنید

[yogishiip]

ممنون لطف کردید.
من میخوام این کد شما رو عینا برای یه فایل اکسل دیگه کپی کنم
ارور میده؟
دقیقا همینها رو نوشتم

Sub TEST()

Z1 = Sheet1.Cells(Sheet1.Rows.Count, "K").End(xlUp).Row
 Columns("K:K").Select
    Selection.AutoFilter
    ActiveSheet.Range("$K$1:$K$" & Z1).AutoFilter Field:=1, Criteria1:=Range("L1") & "*", _
        Operator:=xlAnd
    Range("K1").Select
End Sub



Sub TEST1()
Z1 = Sheet1.Cells(Sheet1.Rows.Count, "K").End(xlUp).Row
Columns("K:K").Select
    Selection.AutoFilter
    ActiveSheet.Range("$K$1:$K$" & Z1).AutoFilter Field:=1, Criteria1:="<>" & Range("L1") & "*", _
        Operator:=xlAnd
        Range("K1").Select
End Sub

Sub TEST3()

    ActiveSheet.Range("$K:$K").AutoFilter Field:=1
End Su

میشه بفرمائید کار این z1 چیه ؟

[iranweld]

شماره سطر آخرین ردیف ستون K که حاوی دیتا میباشد رو بدست میاره
شاید شما برای ستون دیگری بجز k دارید عمل فیلتر رو انجام میدید؟

[yogishiip]

   Columns("A:A").Select
    Selection.Copy
    Selection.Insert Shift:=xlToRight
    Application.CutCopyMode = False
    Selection.Copy
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.Insert Shift:=xlToRight
    Columns("H:H").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.Insert Shift:=xlToRight
    Columns("K:K").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.Insert Shift:=xlToRight
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("L:L").Select
    Selection.Copy
    Columns("K:K").Select
    ActiveSheet.Paste
    Columns("K:K").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.Insert Shift:=xlToRight
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "ÊÇÑíÎ"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "ÔÑÍ ÍæÇáå "
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "ÞíãÊ ˜á"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "äÇã ÇäÈÇÑ "
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "ÏÑíÇÝÊ ˜ääÏå "
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "ÝíáÊÑ ÊÇÑíÎ"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "ÔãÇÑå ÍæÇáå"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "ãÈáÛ æÇÍÏ "
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "æÇÍÏ ÓäÌÔ"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "ÝíáÊÑ ÔãÇÑå ÍæÇáå"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "ÝíáÊÑ äÇã ÇäÈÇÑ"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "ÝíáÊÑ ÏÑíÇÝÊ ˜ääÏå "
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "ÊÚÏÇÏ "
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "äÇã ˜ÇáÇ "
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "ÝíáÊÑ ÔÑÍ ÍæÇáå "
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "äæÚ ÍæÇáå"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "ÝíáÊÑ äæÚ ÍæÇáå "
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "˜Ï ˜ÇáÇ"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "ÏÓÊå ˜ÇáÇ "
    Rows("1:1").Select
    Range("B1").Activate
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Font.Bold = True
    Columns("g:g").Select
    Selection.AutoFilter
    ActiveSheet.Range("$g$1:$g$273667").AutoFilter Field:=1, Criteria1:="<>" & ":ÊÇÑíÎ", _
        Operator:=xlAnd
    Columns("a:a").Select
    Selection.ClearContents
    Selection.AutoFilter
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "ÊÇÑíÎ"

    Columns("p:p").Select
    Selection.AutoFilter
    ActiveSheet.Range("$p$1:$p$273667").AutoFilter Field:=1, Criteria1:="<>" & "ÔÑÍ ÍæÇáå:", _
        Operator:=xlAnd
    Columns("b:b").Select
    Selection.ClearContents
    Selection.AutoFilter
    Range("b1").Select
    ActiveCell.FormulaR1C1 = "ÔÑÍ ÍæÇáå"

     Columns("l:l").Select
    Selection.AutoFilter
    ActiveSheet.Range("$l$1:$l$273667").AutoFilter Field:=1, Criteria1:="<>" & ":ÇäÈÇÑ", _
        Operator:=xlAnd
    Columns("e:e").Select
    Selection.ClearContents
    Selection.AutoFilter
    Range("e1").Select
    ActiveCell.FormulaR1C1 = "äÇã ÇäÈÇÑ"

    Columns("f:f").Select
    Selection.AutoFilter
    ActiveSheet.Range("$f$1:$f$273667").AutoFilter Field:=1, Criteria1:="<>" & "ÏÑíÇÝÊ ˜ääÏå:", _
        Operator:=xlAnd
    Columns("m:m").Select
    Selection.ClearContents
    Selection.AutoFilter
    Range("m1").Select
    ActiveCell.FormulaR1C1 = "ÏÑíÇÝÊ ˜ääÏå"

    Columns("k:k").Select
    Selection.AutoFilter
    ActiveSheet.Range("$k$1:$k$273667").AutoFilter Field:=1, Criteria1:="<>" & ":ÔãÇÑå", _
        Operator:=xlAnd
    Columns("h:h").Select
    Selection.ClearContents
    Selection.AutoFilter
    Range("h1").Select
    ActiveCell.FormulaR1C1 = "ÔãÇÑå ÍæÇáå"

    Columns("s:s").Select
    Selection.AutoFilter
    ActiveSheet.Range("$s$1:$s$273667").AutoFilter Field:=1, Criteria1:="<>" & "ÍæÇáå", _
        Operator:=xlAnd
    Columns("q:q").Select
    Selection.ClearContents
    Selection.AutoFilter
    Range("q1").Select
    ActiveCell.FormulaR1C1 = "äæÚ ÍæÇáå"
    
    Columns("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
    
    Columns("b:b").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
   
    Columns("e:e").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
    
    Columns("f:f").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
    
    Columns("h:h").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
    
    Columns("q:q").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
End Sub

دستتون درد نکنه مشکل تقریبا حل شد .خیلی ممنونم.
فقط یه سوال :راهی نیست که این کد رو خلاصه کنم یا سرعت اجراشو بهبود بدم ؟
این الان خوب و درست اجرا میشه منتها یه چند دقیقه (3 دقیقه) طول میکشه تا اجرا بشه.
هنوزم کامل نیست و یک سری کد دیگه هم باید بهش اضافه کنم !
فایلم حجمش یکم زیاده (حدود 60 مگ ) نمیتونم آپلودش کنم.
ضمنا سیستمم 16 گیگ رم و 4 گیگ gpu ویه cpui7 داره ولی هنگام اجرا کلا دیگه هیچ کاری نمیکنه .
هیچ راهی نیست یکم سرعت محاسبه اکسل رو بالاتر ببرم ؟

[iranweld]

با سلام

کدهای ردیف آخر را برای ستونهای A,B,E,F,H,Q

Columns("A:A").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
    
    Columns("b:b").Select
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C" 

را بدین صورت اصلاح کنید

On Error Resume Next

z1 = Cells(Rows.Count, "A").End(xlUp).Row
            
    Range("A2:A" & z1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
       
     
    z2 = Cells(Rows.Count, "B").End(xlUp).Row
              
    Range("B2:B" & z2).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    
       
    z3 = Cells(Rows.Count, "E").End(xlUp).Row
              
    Range("E2:E" & z3).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"