Announcement

Collapse
No announcement yet.

Combine Columns with Filter - Existing Macro

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    Comment


    • #3
      Re: Combine Columns with Filter - Existing Macro

      That code ignores Filters.

      What are you trying to do?

      Comment


      • #4
        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.

        Comment


        • #5
          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

          Comment


          • #6
            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)

            Comment


            • #7
              Re: Combine Columns with Filter - Existing Macro

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

              Comment


              • #8
                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)

                Comment


                • #9
                  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

                  Comment


                  • #10
                    Re: Combine Columns with Filter - Existing Macro

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

                    Code:
                    dic.CompreMOde = 1

                    Comment


                    • #11
                      Re: Combine Columns with Filter - Existing Macro

                      typo
                      CompareMode

                      Comment


                      • #12
                        Re: Combine Columns with Filter - Existing Macro

                        Ok, fixed the typo. Upon running the macro, it takes the data and pastes it into column W.

                        Comment


                        • #13
                          Re: Combine Columns with Filter - Existing Macro

                          Then what?

                          Comment


                          • #14
                            Re: Combine Columns with Filter - Existing Macro

                            It pastes data into range W:AO, nothing combined, just randomly pasted into fields. I'd love to add an attachment, but the expanse of data is too large to make a dummy copy of or to share the original.

                            Comment


                            • #15


                              Re: Combine Columns with Filter - Existing Macro

                              The I can't help you more.

                              Comment

                              Working...
                              X