10 ตุลาคม 2555

[114] Rows' Collection for AutoFilter - ทำงานกับข้อมูลที่แสดงในแต่ละแถวของ AutoFilter

ส่วนตัวเวลาเขียน VBA เพื่อดึงข้อมูลที่ผ่านการ filter มาใช้งานจะใช้ Advance Filter โดยให้ Copy มาไว้ทีตำแหน่งที่ต้องการ แล้วจึงนำเอาไปใช้งานจริงอีกที ซึ่งมันก็สะดวกกับงานที่ทำแบบไม่ต้องยุ่งกับผู้ใช้งาน แต่ถ้าเกิดเราอยากจะไปยุ่งกับข้อมูลที่ถูก AutoFilter โดยผู้ใช้งานล่ะ ต้องทำอย่างไรบ้าง


AutoFilter Object
ในทุก Worksheet จะอนุญาติให้มี AutoFilter ได้ 1 ชุด และต้องเป็นพื้นที่ติดกันเป็นสีเหลียม ไม่สามารถแยกออกเป็นคอลัมน์ที่ไม่ติดกันได้ โดยแถวแรกจะเป็น Header เสมอ ตัวอย่างที่ใช้ จะเลือกใช้ C4:E14 เพื่อทดสอบผลการทำงานของคำสั่งกัน

กำหนด AutoFilter ครอบคลุม C4:E14
AutoFilter.FilterMode มีค่า False
ในการทดลองจะลอง filter ในคอลัมน์แรก โดยเลือกเฉพาะค่า 1 2 4 5 9 และ 10 ผลที่ได้คือ แถว 7 10 และ 11 ถูกซ่อนเอาไว้เรียบร้อย
เลือก filter เฉพาะค่า 1 2 4 5 9 และ 10
Excel แสดงข้อมูลที่ต้องกับ filter และซ่อนแถวที่ไม่ต้องการ
AutoFilter.FilterMode มีค่า True
ก่อนที่จะไปกันต่อ มาดูกันว่าใน VBA เราจะอ้างอิงตัว AutoFilter ใน Worksheet กันอย่างไร ใน Worksheet object จะมี .AutoFilter เพื่อใช้อ้างถึง AutoFilter Object ดังตัวอย่าง


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
เพื่อให้สะดวกในการทำงานกับข้อมูลที่แสดงเป็นรายแถว มีหลายวิธีในการที่จะตรวจสอบว่ามีแถวไหนที่แสดงผลบ้าง เช่น การใช้คำสั่ง Intersect กับคอลัมน์ทั้งหมด แล้วจึงค่อยมาไล่ทีละ Cell ว่ามีค่า .Row เท่าไหร่ และหากจะเข้าไปอ่านข้อมูลในคอลัมน์อื่น ๆ ก็ต้องมาอ้างอิงกับข้อมูลใน AutoFilter.Range อีกที


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) ได้ทันที

สุดท้ายใครมีวิธีเด็ด ๆ เจ๋ง ๆ ก็เม้นมาเลย ครับ ผมเองก็เขียนแบบที่คิดได้ ไม่ได้ดีที่สุดครับ

เกี่ยวกับเจ้าของบล๊อก

รูปภาพของฉัน

เป็นโปรแกรมเมอร์ที่ฝันว่าจะได้นอนเกาพุงไปวัน ๆ จนพุงลดกลายเป็นเอว ได้เป็นบุคคลที่มีความสุขที่สุดในโลกจนคนอื่น ๆ อิจฉา