Pages

Sunday, February 19, 2012

Save Filtered Data in a Array

If you want to save the filtered data in an array using VBA.

Here is the code-

Option Explicit
Sub store_filtered_data_in_array()
Application.DisplayAlerts = False
Dim ap As Workbook, i As Long
Dim z
' copy the filtered data
Sheets(1).Range("a1:c100").SpecialCells(xlCellTypeVisible).Copy
' add a new workbook
Set ap = Workbooks.Add
' paste data to new workbook
Range("a1").Select
ActiveSheet.Paste
' store data to an array
z = Range("a1:c" & Range("a1").End(xlDown).Row)
' close the new workbook without saving as we don't require it.
ap.Close
' use the data stored in an array
For i = LBound(z) To UBound(z)
MsgBox z(i, 1)
MsgBox z(i, 2)
MsgBox z(i, 3)
Next
Application.DisplayAlerts = True
End Sub

2 comments:

  1. Thant's owsome sharing. Like it.
    Dear kindly share some Examples on IE Automation as well.
    Regards,Excelvba

    ReplyDelete
  2. check these links for ie automation

    http://www.jpsoftwaretech.com/excel-vba/automate-internet-explorer/

    http://www.tushar-mehta.com/publish_train/xl_vba_cases/vba_web_pages_services/index.htm

    ReplyDelete