Chart's dynamic range formula

[Akbarov]

Hello community,

I use Named Range to make chart range dynamic. But for some reason I am getting wrong result in some columns.

I use same formula: =OFFSET(Sheet1!$N$2,0,0,COUNTA(Sheet1!$N:$N)-1,1)
It works for most of categories, but for 1-2 category it give wrong result as shown in below picture.
It shows 1 row extra. So I get 1 blank bar in my chart.

Any idea why it is acting like these?
Thanks in advance.

 

[Domenic]

Cell N7 appears blank, but is it really empty or does it actually contain a formula that returns an empty string ( "" ) ?

 

[Akbarov]

Yes, it is formula which returning ""

 

[Akbarov]

Btw, cells getting their values from VBA

Range("b2").Formula = "=IF(A2<>"""",COUNTIF(Main!$A$3:$A$" & LR & ",Backend!A2)" & ","""")"
  Range("c2").Formula = "=SUMIF(Main!$A$3:$A$" & LR & ",Backend!A2,Main!$G$3:$G$" & LR & ")"
 
          With Sheets("Backend")
  .Range("B2").AutoFill .Range("B2:B" & .Cells(.Rows.Count, "A").End(xlUp).Row)
          End With
         
          With Sheets("Backend")
  .Range("C2").AutoFill .Range("C2:C" & .Cells(.Rows.Count, "A").End(xlUp).Row)
          End With

Last Row of .Cells(.Rows.Count, "A").End(xlUp).Row = 6
It should fill formula down until last empty cell. But it fills 1 row more.

 

[Akbarov]

Problem is solved. I just added this line.

    With Sheets("Backend")
    .Range("B3:C12").ClearContents
    End With