+ Reply to Thread
Results 1 to 6 of 6

Dynamic Filtering of records

Hybrid View

Jovillanueva Dynamic Filtering of records 11-24-2013, 09:32 PM
FDibbins Re: Dynamic Filtering of... 11-24-2013, 09:33 PM
Jovillanueva Re: Dynamic Filtering of... 11-24-2013, 10:44 PM
Jovillanueva Re: Dynamic Filtering of... 11-25-2013, 03:15 AM
Jovillanueva Re: Dynamic Filtering of... 11-26-2013, 12:51 AM
Jovillanueva Re: Dynamic Filtering of... 11-27-2013, 01:25 AM
  1. #1
    Forum Contributor
    Join Date
    07-17-2011
    Location
    PH
    MS-Off Ver
    Excel 2007
    Posts
    183

    Dynamic Filtering of records

    Hi Guys,

    Newbie in Macro. I have a macro that get the file from CSV and dump/copy to a worksheet. my requirements concatenate a certain column from the source data and then filter those records by Line and before dumping to a template. i have to filter by line for ex. the specific line i need to filter is Line1. can you help me guys on how to make or insert this in macro. your help is very much appreciated. thanks.

    attached is my sample data.



    Sub GetFile()
    
        Dim TARFIL As String
        Dim wb, wbtemp As Workbook
        Dim ws, wstemp  As Worksheet
        Dim lrow As Long
        
        Set wbtemp = ThisWorkbook
        Set wstemp = wbtemp.Sheets("RawData")
        
        With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
        End With
        
        TARFIL = "C:\Users\KBL\Desktop\Dashboard\Inquiry.csv"
        
        'opening of data
        
        If Dir(TARFIL) <> "" Then
        
        Workbooks.OpenText Filename:=TARFIL, startRow:=1, DataType:=xlDelimited, TextQualifier:= _
                xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
                , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
                Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
                Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), _
                Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1))
                
        Set wb = ActiveWorkbook
        Set ws = wb.Sheets(1)
        
        'dumping of data to template
        With ws
            lrow = .Range("A" & Rows.Count).End(xlUp).Row
            .Range("A4:V" & lrow).Copy wstemp.Range("A1")
        End With
        
        'closing and rereshing of data
        wb.Close
        wbtemp.RefreshAll
        
        End If
    Attached Files Attached Files
    Last edited by Jovillanueva; 11-24-2013 at 10:44 PM. Reason: add code bloacking

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,048

    Re: Dynamic Filtering of records

    Thanks for the code tags
    Last edited by FDibbins; 11-25-2013 at 01:11 AM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    07-17-2011
    Location
    PH
    MS-Off Ver
    Excel 2007
    Posts
    183

    Re: Dynamic Filtering of records

    Thanks for the reminder. just modify already my post.

  4. #4
    Forum Contributor
    Join Date
    07-17-2011
    Location
    PH
    MS-Off Ver
    Excel 2007
    Posts
    183

    Re: Dynamic Filtering of records

    Guys, May I ask your help on how to copy or dump auto filter records and concatenate a column to an existing template. The code was already posted but could not integrate this requirements in the codes. thanks.

  5. #5
    Forum Contributor
    Join Date
    07-17-2011
    Location
    PH
    MS-Off Ver
    Excel 2007
    Posts
    183

    Re: Dynamic Filtering of records

    Hi Guys,
    Need your help on concatenating records.
    Just made adjustment on my Macro codes. Filtering of records before dumping/copy to template is working.
    My problem is how could i concatenate the columns. Right now i have 24 columns (A,V) in my data source. I need to
    concatenate the column A,E then insert to column F. another, concatenate column A,E,I,M and insert to column N.
    Your help is very much appreciated.

    The result should be like this format when dumping to tempate.
    Column A-B-C-D-E-Concatenated column(A+E)=F,G-H and so on.

        'dumping of data to template
        With ws
            lrow = .Range("A" & Rows.Count).End(xlUp).Row
            .Range("A4:V" & lrow).AutoFilter Field:=1, Criteria1:="Line1"  'just added to Filter records
            .Range("A4:V" & lrow).Copy wstemp.Range("A1")
        End With

  6. #6
    Forum Contributor
    Join Date
    07-17-2011
    Location
    PH
    MS-Off Ver
    Excel 2007
    Posts
    183

    Re: Dynamic Filtering of records

    Guys need your help. Why only 1 rows concatenated in Column F?
    It should be more than 400 rows that should be concatenated.
    Any idea guys. below is my macro codes. thanks.

     With wstemp
             'Insert Blank Column
            .Columns("F:F").Insert Shift:=xlToRight
            .Columns("O:O").Insert Shift:=xlToRight
             
             'Concatenate Cell
            .Range("F2").Select
             ActiveCell.FormulaR1C1 = "=RC[-5]&RC[-1]"
            .Range("F3").Select
            
            .Range("O2").Select
             ActiveCell.FormulaR1C1 = "=RC[-14]&RC[-10]&RC[-5]&RC[-1]"
            .Range("O3").Select
            
        End With

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Filtering unique records with VBA
    By Josh_123456 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-20-2007, 12:22 AM
  2. Filtering out old records.
    By david.palmer in forum Excel General
    Replies: 1
    Last Post: 03-24-2006, 12:20 PM
  3. Filtering Records
    By GregOnline in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-21-2005, 01:05 AM
  4. [SOLVED] filtering for unique records
    By KG in forum Excel General
    Replies: 8
    Last Post: 08-13-2005, 02:05 PM
  5. Filtering Records Help Please
    By Chris Hankin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-04-2005, 04:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1