Display caption in merged range vba macro

[anriretief]

I Have the following partial representation of a vba code:

Set Sheet = WorkBook.WorkSheets(1)

Sheet.Name = "Witmielies"

Sheet.Activate

'loop

chartArray = Array("CH1311","CH1313","CH1315","CH1445","CH1320","CH1446")

usedRows=0

For Each chart In chartArray

  Set i = ActiveDocument.GetSheetObject(chart)

    chartCaption = i.GetCaption.Name.v

  Sheet.Cells(usedRows+1, 1)= chartCaption

  Sheet.Cells(usedRows+1, 1).Font.Bold=true

  Sheet.Cells(AusedRows+1, 1).Interior.Color = RGB(250,180,90)

    i.CopyTableToClipboard true

    Sheet.Cells(usedRows+3, 1).Select

    Sheet.Paste

    usedRows=Sheet.UsedRange.Rows.Count+3 '--->function to get the first unused row

Next

I want to merge the row in which the caption is displayed over the first 6 columns

Sheet.Cells(usedRows+1, 1)= chartCaption

I tried Sheet.Range(Cells(usedRows+1,1),Cells(usedRows+1,6)).Merge = True

But this doesn't work

Any help or suggestions will be greatly appreciated

[tamilarasu · Accepted solution]

Hi Anri,

Your syntax looks correct to me but I think you forget to reference the sheet.

Sheet.Range(Sheet.Cells(usedRows+1,1),Sheet.Cells(usedRows+1,6)).Merge

So you can paste the code like below and try again.

  Sheet.Cells(usedRows+1, 1)= chartCaption

Sheet.Range(Sheet.Cells(usedRows+1,1),Sheet.Cells(usedRows+1,6)).Merge

Have a nice day!

[anriretief]

Works like a charm

Thank you Tamil

[tamilarasu]

Great Anri. Please close the thread by marking correct answer. Have a nice week ahead .