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

[yogishiip]

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

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

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

[LTR]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

[/LTR]

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

[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("[U][B]<>K5[/B][/U]").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"