Dynamic range in VBA formula multiple worksheets-CodePudding

Let consider the following piece of code, where I inserted an Excel formula to perform a very simple count.

dim wb as Workbook
dim ws as Worksheet
dim ws_a as Worksheet

set wb = ThisWorkbook
set ws = wb.Worksheets("D")
set ws_a = wb.Worksheets("A")

With ws_a

.Range("c2").Formula = _
"ROWS(UNIQUE(FILTER(" & "D"! & Range("c2:c400").Address & "," & _
"IF(" & .Range("a2").Address(0,0) & " =""Empty"","""",""*""& " & .Range("a2").Address(0,0) & " &""*""))"
'following part of code

End With

As you can see, the Range in the Sheet "D" is not dynamic. I'd like to make it dynamic, taking into account the empty cells in the range. Basically, I would need to replace

Range("c2:c400")

with something like

D.Range("c2"),D.Cells(D.Range("c2").SpecialCells(xlCellTypeLastCell).Row, 3))

In the attempt of concatenating strings, I definitely get lost with & and "", so I think this is the problem: my poor knowledge of this type of syntax. I've also tried to dim and set the range needed (outside the formula), but the code refers to the wrong sheet, as if it could not identify different sheets in the formula.

CodePudding user response：

dim wb as Workbook, ws as Worksheet, ws_a as Worksheet

set wb = ThisWorkbook
set ws = wb.Worksheets("D")
set ws_a = wb.Worksheets("A")

dim rngFilter as Range
Set rngFilter = ws_a.Range("c2:C400")
Set rngFilter = Range(rngFilter.Cells(1), rngFilter.SpecialCells(xlCellTypeLastCell))

With ws_a
    .Range("c2").Formula = _
    "ROWS(UNIQUE(FILTER(" & rngFilter.Address & "," & _
    "IF(A2 =""Empty"","""",""*""& A2 &""*""))"
    'following part of code

End With