consolidate function is not working in vba

Question

What I need is really simple. for instance:

        Br         56

        Bc          6

        Bt          20

        Br          23

        bc          15

I want these duplicate bc/br/bt consolidate and sum their numbers This is the part of code I wrote. However it keeps giving me "application-defined or object- defined error". Can anybody tell what is the problem?

         Sub lo()
         Dim sr As Variant
         Range("a1", Range("a1").End(xlDown).End(xlToRight)).Select

           Set sr = Selection

          Range("n1").Consolidate Sources:=sr,
            _Function:=xlSum,TopRow:=False, LeftColumn:=True, 
                _CreateLinks:=False
             End Sub

I actually learned how to use this function while answering the question. Thank you for asking it! — DiegoAndresJAY, Aug 06 '15 at 23:03

score 0 · Answer 1 · answered Aug 06 '15 at 23:03

Apparently this function requires ranges to be in the R1C1 reference style. The below code should work for you.

I also had to move the underscores to the end of each line instead of the beginning of the next line.

         Sub lo()
         Dim sr As Variant
         Range("a1", Range("a1").End(xlDown).End(xlToRight)).Select

           Set sr = Selection

          Range("n1").Consolidate Sources:=sr.address(ReferenceStyle:=xlR1C1), _
            Function:=xlSum,TopRow:=False, LeftColumn:=True, _
                CreateLinks:=False
             End Sub

consolidate function is not working in vba

1 Answers1