Snasui Excel Forum

Posted: **Tue May 06, 2014 11:06 pm**

ติดปัญหา vba ที่ซ่อน 000000 ไม่ต้องพิมรหัสในคอลัม C ทุกร้านครับ ผมแนบไฟล์ไว้ กับรหัสให้ให้ในกระทู้นะครับ เผอไม่เจ้าใจที่ผมพูด

Sub Macro3()
'
' Macro3 แมโคร
'
' Keyboard Shortcut: Ctrl+x
'
Range("A1:D1").Select
Range("D1").Activate
Selection.AutoFilter
ActiveSheet.Range("$A$1:$D$548").AutoFilter Field:=3, Criteria1:=Array( _
"0773", "0776", "0778", "0898", "0912", "0921", "0945", "0997", "1015", "1076", "1101", _
"1105", "1155", "1158", "1161", "1163", "1166", "1167", "1168", "1171", "1351", "1379", _
"1555", "1607", "1626", "1678", "1749", "1798", "1799", "1889", "1938", "2099", "2109", _
"2166", "2239", "2266", "2417", "2569", "2588", "2649", "2650", "2682", "2754", "2782", _
"2827", "2866", "2867", "2920", "2922", "2999", "3010", "3039", "3058", "3209", "3253", _
"3266", "3299", "3379", "3401", "3449", "3516", "3517", "3548", "3565", "3659", "3679", _
"3682", "3686", "3769", "3804", "3810", "3850", "3880", "3899", "3900", "3990", "4029", _
"4059", "4095", "4109", "4135", "4137", "4187", "4189", "4229", "4296", "4301", "4423", _
"4438", "4471", "4491", "4539", "4541", "4542", "4543", "4614", "4645", "4646", "4653", _
"4695", "4765", "4769", "4789", "4832", "4855", "4856", "4911", "4915", "4927", "4948", _
"4965", "4988", "4989", "6396", "6418", "6419", "6434", "6462", "6491", "6558", "6560", _
"6661", "6670", "6692", "6738", "6740", "6829", "6877", "6920", "6927", "6928", "6942", _
"6973", "6976", "6977", "7047", "7123", "7192", "7195", "7206", "7306", "7348", "7349", _
"7360", "7457", "7470", "7487", "7493", "7497", "7542", "7619", "7752", "7766", "7816", _
"7817", "7819", "7840", "7843", "7857", "7931", "8017", "8018", "8062", "8069", "8083", _
"8093", "8095", "8098", "8151", "8162", "8216", "8282", "8409", "8461", "8468", "8490", _
"8570", "8584", "8597", "8598", "8613", "8710", "8792", "8795", "8857", "8958", "8961", _
"8962", "9022", "9160", "9161", "9162", "9175", "9180", "9209", "9219", "9277", "9279", _
"9284", "9307", "9308", "9350", "9359", "9384", "9468", "9537", "9546", "9634", "9636", _
"9637", "9672", "9673", "9674", "9675", "9722", "9751", "9771", "9813", "9852", "9861"), _
Operator:=xlFilterValues
Range("A2:D7").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
End Sub

จากสูตรจะมี รหัส 4 หลักอยู่ละลานตา ซึ่งรหัส 4 หลักนั้นแต่ละวันจะไม่เหมือนกัน ผมจึงหาทางออกโดยการเลือกfiter ซ่อนเฉพาะ 000000 แทน อาจารย์พอมีวิธีไหมครับ

Posted: **Wed May 07, 2014 1:44 pm**

ถ้าแค่ต้องการฟิลเตอร์ ก็ใช้โค๊ดตามนี้ได้ครับ บันทึกมาโครได้เลย

Code: Select all

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.Range("$A$1:$D$555").AutoFilter Field:=3, Criteria1:=Array( _
        "0773", "0776", "0778", "0898", "0912", "0921", "0945", "0997", "1015", "1076", "1101", _
        "1105", "1111", "1155", "1158", "1161", "1163", "1166", "1167", "1168", "1171", "1351", _
        "1379", "1555", "1607", "1626", "1678", "1749", "1798", "1799", "1889", "1938", "2099", _
        "2109", "2166", "2222", "2239", "2266", "2417", "2569", "2588", "2649", "2650", "2682", _
        "2754", "2782", "2827", "2866", "2867", "2920", "2922", "2999", "3010", "3039", "3058", _
        "3209", "3253", "3266", "3299", "3333", "3379", "3401", "3449", "3516", "3517", "3548", _
        "3565", "3659", "3679", "3682", "3686", "3769", "3804", "3810", "3850", "3880", "3899", _
        "3900", "3990", "4029", "4059", "4095", "4109", "4135", "4137", "4187", "4189", "4229", _
        "4296", "4301", "4423", "4438", "4471", "4491", "4539", "4541", "4542", "4543", "4614", _
        "4645", "4646", "4653", "4695", "4765", "4769", "4789", "4832", "4855", "4856", "4911", _
        "4915", "4927", "4948", "4965", "4988", "4989", "6396", "6418", "6419", "6434", "6462", _
        "6491", "6558", "6560", "6661", "6670", "6692", "6738", "6740", "6829", "6877", "6920", _
        "6927", "6928", "6942", "6973", "6976", "6977", "7047", "7123", "7192", "7195", "7206", _
        "7306", "7348", "7349", "7360", "7457", "7470", "7487", "7493", "7497", "7542", "7619", _
        "7752", "7766", "7816", "7817", "7819", "7840", "7843", "7857", "7931", "8017", "8018", _
        "8062", "8069", "8083", "8093", "8095", "8098", "8151", "8162", "8216", "8282", "8409", _
        "8461", "8468", "8490", "8570", "8584", "8597", "8598", "8613", "8710", "8792", "8795", _
        "8857", "8958", "8961", "8962", "9022", "9160", "9161", "9162", "9175", "9180", "9209", _
        "9219", "9277", "9279", "9284", "9307", "9308", "9350", "9359", "9384", "9468", "9537", _
        "9546", "9634", "9636", "9637", "9672", "9673", "9674", "9675", "9722", "9751", "9771", _
        "9813", "9852", "9861"), Operator:=xlFilterValues
End Sub

Posted: **Wed May 07, 2014 7:58 pm**

bank9597 เข้าใจผิด ข้อมูลรหัสตามนั้นครับแต่ในแต่ละวัน รหัสมันไม่ได้เหมือนตามนั้นทุกกกวัน อาจมีรหัสเพิ่ม หรือ ลดลง หรือมีรหัสใหม่เพิ่มเข้ามา รหัส XXXX ทั้ง 4 ทั้ง 4 ตัวนั้นมันไม่ได้ ต้องรหัสนี้ทุกๆวัน
แต่ filter รหัส 000000 มันจะมีมาทุกวัน แล้วผมไม่ต้องการเลยจะให้มันซ่อน มันจะได้แสดงรหัสที่ไม่แน่นอนทุกวัน ผมเลยจะถามว่า มีวิธีเลือก รหัส 000000 ให้ซ่อนไปไหม แค่ให้ 0000000ซ่อนไปเท่านั้น

Posted: **Wed May 07, 2014 8:20 pm**

ลองปรับเป็นด้านล่างครับ

Code: Select all

Sub Macro3()
    ActiveSheet.Range("$A$1:$D$555").AutoFilter Field:=3, Criteria1:= _
        "<>*0000000*", Operator:=xlAnd
End Sub

Posted: **Wed May 07, 2014 10:34 pm**

ใช้การได้แล้วครับขอบคุณครับ

Posted: **Sun Nov 23, 2014 2:05 am**

Code: Select all

   ActiveSheet.Range("$A$1:$J$407").AutoFilter Field:=10, Criteria1:="<>#N/A", Operator:=xlAnd

ขอฝากโค๊ดในเว็บไว้ดูเผอลืมครับ

Posted: **Sun Nov 23, 2014 2:09 am**

สูตรเคลียร fiter ขอฝากครับ

If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData

Snasui Excel Forum

ติดปัญหา vba ที่ซ่อน 000000 ไม่ต้องพิมรหัสในคอลัม C ครับ

ติดปัญหา vba ที่ซ่อน 000000 ไม่ต้องพิมรหัสในคอลัม C ครับ

Re: ติดปัญหา vba ที่ซ่อน 000000 ไม่ต้องพิมรหัสในคอลัม C ครับ

Re: ติดปัญหา vba ที่ซ่อน 000000 ไม่ต้องพิมรหัสในคอลัม C ครับ

Re: ติดปัญหา vba ที่ซ่อน 000000 ไม่ต้องพิมรหัสในคอลัม C ครับ

Re: ติดปัญหา vba ที่ซ่อน 000000 ไม่ต้องพิมรหัสในคอลัม C ครับ

Re: ติดปัญหา vba ที่ซ่อน 000000 ไม่ต้องพิมรหัสในคอลัม C ครับ

Re: ติดปัญหา vba ที่ซ่อน 000000 ไม่ต้องพิมรหัสในคอลัม C ครับ