topic Display caption in merged range vba macro in QlikView

Display caption in merged range vba macro

anriretief — Mon, 19 Mar 2018 07:16:02 GMT

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

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 — Mon, 19 Mar 2018 08:52:35 GMT

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 — Mon, 19 Mar 2018 08:57:50 GMT

Works like a charm

Thank you Tamil

tamilarasu — Mon, 19 Mar 2018 09:00:12 GMT

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