Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 15

Thread: Combine Columns with Filter - Existing Macro

  1. #1
    Join Date
    17th June 2011
    Posts
    238

    Combine Columns with Filter - Existing Macro

    Hello,

    I have this macro that will combine a column based on matching data in specified columns. However, the macro (below) misbehaves when the data has an auto filter on it. Is there a way to modify this macro so that it will recognize the filtered data?

    Thank you for your help!

    VB:
    Sub CombineLikeExams() 
        Dim a, txt As String, i As Long, ii As Long, n As Long 
        With Range("b2").CurrentRegion 
            a = .Value 
            .ClearContents 
            With CreateObject("Scripting.Dictionary") 
                .CompareMode = 1 
                For i = 1 To UBound(a, 1) 
                    txt = Join$(Array(a(i, 2), a(i, 3), a(i, 4)), ";;") 
                    If Not .exists(txt) Then 
                        n = n + 1 
                        For ii = 1 To UBound(a, 2) 
                            a(n, ii) = a(i, ii) 
                        Next 
                        .Item(txt) = n 
                    Else 
                        a(.Item(txt), 5) = a(.Item(txt), 5) & ", " & a(i, 5) 
                    End If 
                Next 
            End With 
            .Resize(n).Value = a 
        End With 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th July 2011
    Posts
    532

    Re: Combine Columns with Filter - Existing Macro

    Quickest way would be to clear the filter first like so:
    VB:
    If Not ActiveSheet.AutoFilter Is Nothing Then 
        ActiveSheet.AutoFilter.ShowAllData 
    End If 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,574

    Re: Combine Columns with Filter - Existing Macro

    That code ignores Filters.

    What are you trying to do?

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    17th June 2011
    Posts
    238

    Re: Combine Columns with Filter - Existing Macro

    The macro will combine the data in column F if column D, E and H matches. I need to be able to filter the data, then run the macro without it pulling any data from the hidden rows that aren't needed in the filter.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,574

    Re: Combine Columns with Filter - Existing Macro

    Not tested

    If this doesn't work, I need to see the file
    VB:
    Sub CombineLikeExams() 
        Dim r As Range, txt As String, a,ii As Long 
        With Range("b2").CurrentRegion.Offset(1) 
            Redim a(1 To .Rows.Count, 1 To Columns.Count) 
            With CreateObject("Scripting.Dictionary") 
                .CompareMode = 1 
                For Each r In .Columns(1).SpecialCells(12) 
                    txt = Join$(Array(r(,2).value, r(,3).value, r(,4).value), ";;") 
                    If Not .exists(txt) Then 
                        n = n + 1 
                        For ii = 1 To UBound(a, 2) 
                            a(n, ii) = r(,ii).Value 
                        Next 
                        .Item(txt) = n 
                    Else 
                        a(.Item(txt), 5) = a(.Item(txt), 5) & ", " & r(,5).value 
                    End If 
                Next 
            End With 
            .Offset(,.Columns.Count + 2).resize(n,UBound(a,2)).value = a 
        End With 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    17th June 2011
    Posts
    238

    Re: Combine Columns with Filter - Existing Macro

    It gave an error Run Time Error '7' Out of Memory on this line...

    VB:
    Redim a(1 To .Rows.Count, 1 To Columns.Count) 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,574

    Re: Combine Columns with Filter - Existing Macro

    Missed a period
    VB:
    Redim a(1 To .Rows.Count, 1 To .Columns.Count) 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    17th June 2011
    Posts
    238

    Re: Combine Columns with Filter - Existing Macro

    Hah, yes, I noticed that and added the period. Then it proceeded with an error (Object doesn't support this project or method) on this line.

    VB:
    For Each r In .Columns(1).SpecialCells(12) 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,574

    Re: Combine Columns with Filter - Existing Macro

    Of course...
    VB:
    Sub CombineLikeExams() 
        Dim r As Range, txt As String, a 
        Dim dic As Object 
        Set dic = CreateObject("Scripting.Dictionary") 
        dic.CompreMOde = 1 
        With Range("b2").CurrentRegion.Offset(1) 
            Redim a(1 To .Rows.Count, 1 To .Columns.Count) 
            For Each r In .Columns(1).SpecialCells(12) 
                txt = Join$(Array(r(,2).value, r(,3).value, r(,4).value), ";;") 
                If Not dic.exists(txt) Then 
                    n = n + 1 
                    For ii = 1 To .Columns.Count 
                        a(n, ii) = r(,ii).Value 
                    Next 
                    dic(txt) = n 
                Else 
                    a(dic(txt), 5) = a(dic(txt), 5) & ", " & r(,5).value 
                End If 
            Next 
            .Offset(,.Columns.Count + 2).resize(n,UBound(a,2)).value = a 
        End With 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    17th June 2011
    Posts
    238

    Re: Combine Columns with Filter - Existing Macro

    Object doesn't support this project or method on this line

    VB:
    dic.CompreMOde = 1 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 12
    Last Post: November 5th, 2011, 03:44
  2. Replies: 4
    Last Post: April 14th, 2011, 01:56
  3. Adding new columns to existing excel macro
    By IBSUB in forum EXCEL HELP
    Replies: 1
    Last Post: April 5th, 2011, 02:39
  4. Replies: 4
    Last Post: March 31st, 2011, 03:21
  5. Filter Out Values Existing In 2 Columns
    By gioB in forum EXCEL HELP
    Replies: 1
    Last Post: September 28th, 2007, 12:42

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
  •  
porno