AutoFilter Object
ในทุก Worksheet จะอนุญาติให้มี AutoFilter ได้ 1 ชุด และต้องเป็นพื้นที่ติดกันเป็นสีเหลียม ไม่สามารถแยกออกเป็นคอลัมน์ที่ไม่ติดกันได้ โดยแถวแรกจะเป็น Header เสมอ ตัวอย่างที่ใช้ จะเลือกใช้ C4:E14 เพื่อทดสอบผลการทำงานของคำสั่งกัน
 |
| กำหนด AutoFilter ครอบคลุม C4:E14 AutoFilter.FilterMode มีค่า False |
 |
| เลือก filter เฉพาะค่า 1 2 4 5 9 และ 10 |
 |
| Excel แสดงข้อมูลที่ต้องกับ filter และซ่อนแถวที่ไม่ต้องการ AutoFilter.FilterMode มีค่า True |
Dim ws as Worksheet
Set ws = Worksheets("Sheet1")
Dim oAutoFilter As AutoFilter
Set oAutoFilter = ws.AutoFilter
ข้อควรระวัง ในกรณี Worksheet ไม่ได้มีการเปิดใช้งาน AutoFilter จะทำให้ ws.AutoFilter คืนค่าเป็น Nothing อย่าลืมตรวจสอบก่อนใช้งานเสมอ
AutoFilter Member
สมาชิกใน AutoFilter object ที่น่าสนใจได้แก่
- .FilterMode คืนค่าเป็น True หากมีการเลือก filter
- .Range คืนค่าเป็น Range object ของพื้นที่ที่ถูกใช้งานเป็น AutoFilter ในตัวอย่างคือ Range("C4:E14")
จุดประสงค์ในคราวนี้คือการเข้าไปประมวลผลกับข้อมูลที่ถูก filter เท่านั้น ส่วนที่ซ่อนจะไม่ไปยุ่ง ดังนั้นเราต้องเลือกมาเฉพาะข้อมูลที่แสดงเท่านั้นดังนี้
Dim rVisible As Range Set rVisible = ws.AutoFilter.SpecialCells(xlCellTypeVisible)
จากที่เราทราบมาว่า ในกรณีที่ Range เกิดจากข้อมูลที่ไม่ได้อยู่ติดกัน จะทำให้เกิดการแบ่งพื้น ๆ ที่ไม่ติดกันออกเป็นส่วน ๆ เก็บใน Areas collection ของ Range object นั้น ๆ ทำให้ไม่สามารถเรียกใช้ rVisible.Rows ได้ถูกต้อง เพราะค่าที่คืนมา คือแถวที่เป็นแถวแรกในแต่ละ Area แทน
rVisible.Rows.Count 'it return 3
 |
| ข้อมูลที่แสดงจะถูกแบ่งออกเป็น 3 Areas คือแถว 4-6 8-9 และ 13-14 |
Sub TestIntersect()
Dim rAutoFilter As Range
Set rAutoFilter = AutoFilter.Range
Dim rVisible As Range
Set rVisible = rAutoFilter.SpecialCells(xlCellTypeVisible)
Dim rRows As Range
Set rRows = rAutoFilter.Columns(1)
Dim rVisibleRows As Range
Set rVisibleRows = Application.Intersect(rVisible, rRows)
Debug.Print "rVisibleRows.Address", rVisibleRows.Address
Dim i As Long, v As Variant
For i = 1 To rVisibleRows.Cells.Count
Debug.Print "Visible Row: ", rVisibleRows.Cells(i).Row
'access 2nd column
v = Cells(rVisibleRows.Cells(i).Row, rAutoFilter.Columns(2).Column).Value
Next i
End Sub
ผลที่ได้จากการทำงาน
rVisibleRows.Address $C$4:$C$6,$C$8:$C$9,$C$13:$C$14 Visible Row: 4 Visible Row: 5 Visible Row: 6 Visible Row: 7 Visible Row: 8 Visible Row: 9 Visible Row: 10
ในตัวอย่างจะเป็นว่า กว่าจะได้ค่า v ที่เป็นคอลัมน์ที่ 2 การเข้าถึงส่วนของข้อมูลอ้างกันหลายทอด นอกจากอ่านยากแล้วยังทำให้มีปัญหาตอนกลับมาแก้ไขโปรแกรมอีกด้วย ในเมื่อเวลาทำงานทำทีละแถวอยู่แล้ว เลยคิดว่านำเอา Row ในแต่ละ Area มาเก็บไว้ใน Collection แล้วจึงนำมาใช้น่าจะง่ายกว่า
Sub TestAutoFilter() Dim rAutoFilter As Range Set rAutoFilter = AutoFilter.Range Debug.Print "*** Display AutoFilter in current Worksheet ***" Debug.Print "rAutoFilter.Address", rAutoFilter.Address(False, False) Debug.Print "rAutoFilter.Cells.Count", rAutoFilter.Cells.Count Debug.Print "rAutoFilter.Rows.Count", rAutoFilter.Rows.Count Debug.Print "rAutoFilter.Areas.Count", rAutoFilter.Areas.Count Dim rVisible As Range Set rVisible = rAutoFilter.SpecialCells(xlCellTypeVisible) Debug.Print Debug.Print "*** Display only visible cells in AutoFilter ***" Debug.Print "rVisible.Address", rVisible.Address(False, False) Debug.Print "rVisible.Cells.Count", rVisible.Cells.Count Debug.Print "rVisible.Rows.Count", rVisible.Rows.Count Debug.Print "rVisible.Areas.Count", rVisible.Areas.Count 'Convert from Areas to Rows Collection Debug.Print Debug.Print "*** Convert from Areas to Rows Collection ***" Dim rArea As Range, rRow As Range Dim cRows As New Collection For Each rArea In rVisible.Areas For Each rRow In rArea.Rows cRows.Add rRow Debug.Print "rRow.Address", rRow.Address(False, False) Next rRow Next rArea Dim v As Variant For Each rRow In cRows 'access 2nd column v = rRow.Cells(2) Next End Sub
ผลที่ได้จากการทำงาน
*** Display AutoFilter in current Worksheet *** rAutoFilter.Address C4:E14 rAutoFilter.Cells.Count 33 rAutoFilter.Rows.Count 11 rAutoFilter.Areas.Count 1 *** Display only visible cells in AutoFilter *** rVisible.Address C4:E6,C8:E9,C13:E14 rVisible.Cells.Count 21 rVisible.Rows.Count 3 rVisible.Areas.Count 3 *** Convert from Areas to Rows Collection *** rRow.Address C4:E4 rRow.Address C5:E5 rRow.Address C6:E6 rRow.Address C8:E8 rRow.Address C9:E9 rRow.Address C13:E13 rRow.Address C14:E14
จะเห็นว่าเราไล่เก็บข้อมูลแต่ละแถวในแต่ละ Area ในตัวแปรแบบ Collection ตอนเอามาใช้งานก็ง่าย เพราะ 1 item ใน Collection เก็บแค่ 1 แถว ดังนั้นจะดึงขึ้นมูลในคอลัมน์ที่ 2 ก็อ้างอิง .Cells(2) ได้ทันที
สุดท้ายใครมีวิธีเด็ด ๆ เจ๋ง ๆ ก็เม้นมาเลย ครับ ผมเองก็เขียนแบบที่คิดได้ ไม่ได้ดีที่สุดครับ
ไม่มีความคิดเห็น:
แสดงความคิดเห็น