problem with counting Merge area cells

[ajaypal_sp]

HI



I wrote a VBA code by using Sumifs function. in this code i find out starting column number (for each category box), ending column number based on merged cells. by using #selection.columns.count# . sometimes this formula returns 1 even though there are 6 cells in Mergearea. When we run the code by using F5, this line of code is not working properly. Please help me.

in my code the below part is not working properly. i used below code 10 times in VBA module.

With ThisWorkbook.Worksheets("ks sales").Range("a2:az2")
Set sc = .Find("BYOD TRANSACTIONS")
If Not sc Is Nothing Then
sc = sc.Column


n = [COLOR=#0000cd][B]Cells(2, sc).MergeArea.Columns.Count[/B][/COLOR]


ec = n + sc - 1
End If
End With

Here,
sc - start column
n - no of cells in merge area.
ec - end column

Thank you in advance.

Regards
shiva

 

[steve the fish]

It counts columns not cells.

 

[ajaypal_sp]

It counts columns not cells.

thank you for reply.

What ever, either columns or cells. NNo issue. result is same. But that line of code not working properly. when we use f5 , code skipping few lines / that line of code (count merge cells) returns value 1. i can't understand where the code was wrong.

 

[steve the fish]

Are these merged cells in row 2? If not thats why.

 

[ajaypal_sp]

2nd row and 11th row contains merged cells. based on these merged cells i find out start & end columns to get result.

 

[steve the fish]

If you want it to be on the worksheet within the with statement use a dot before cells ie .Cells(2,sc) etc