CODEWITHSUNDEEP
vbaexcel
Makah
Makah

Reputation: 4523

How to consolidate multiple non-continuous ranges

I tried to consolidate values in multuple non-continuous range, but I fail to do this. Like my example below

  Set myRange = Range("E6:F8,E12:F14")

  [J10].Consolidate Sources:=myRange,  _
    Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False

I know I can copy all ranges in one place and then consolidate in a continuous range, like

  [J10].Consolidate Sources:=TempSheet.Range(A1:B10).Address,  _
    Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False

Upvotes: 1

Views: 535

Answers (1)

Ravi Yenugu
Ravi Yenugu

Reputation: 3898

Consolidate requires full path of Sheets and Sources in R1C1 style

Sub MyConsolidate()
 [J10].Consolidate _
 Sources:=Array("Sheet1!R6C5:R8C6", "Sheet1!R12C5:R14C6"), _
 Function:=xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False
End Sub

enter image description here

Also to convert A1 Notation to R1C1 use

Sub RangeToR1c1()
MsgBox Range("E6").Address(ReferenceStyle:=xlR1C1)
End Sub

Upvotes: 2

Related Questions