Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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!

    Code:
    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
    542

    Re: Combine Columns with Filter - Existing Macro

    Quickest way would be to clear the filter first like so:
    Code:
    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
    9,423

    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
    9,423

    Re: Combine Columns with Filter - Existing Macro

    Not tested

    If this doesn't work, I need to see the file
    Code:
    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...

    Code:
            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
    9,423

    Re: Combine Columns with Filter - Existing Macro

    Missed a period
    Code:
    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.

    Code:
                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
    9,423

    Re: Combine Columns with Filter - Existing Macro

    Of course...
    Code:
    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

    Code:
    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