重複したデータ(空白は除く)のVBA表記について

いつもお世話になっております。

この度、下記の関数についてVBAのご教示をいただきたく質問させていただきました。
https://excel-forest.net/2017/03/22/tyouhukuwo/

こちらに記載されております重複と空白セルを除くデータをカウントする
=SUMPRODUCT(1/COUNTIF(A1:A13,A1:A13&""))-1
の数式を活用しているのですが、DB上からデータを新たに反映させた際、#REF!のエラーが表示され困っております。

Excel関数の修正で解決する方法があれば嬉しいのですが、これは私の思い付きになりますが、もしボタンを押した際にセルに上記の関数のVBA構文を反映させることが可能であればテキストからコピペする煩しさから解放されると思い質問させていただきました。

色々お調べしたのですが自力で解決することができず、どうぞよろしくお願いいたします。

質問者からの補足コメント

• 

  色々と調べVBAではなく下記の関数で問題の解決をすることができました。
  =SUMPRODUCT((INDIRECT("A1:A13")<>"")/COUNTIF(INDIRECT("A1:A13"),(INDIRECT("A1:A13")&"")))
  
  回答してくださいました方々、この場を借りて感謝申し上げます。
  ありがとうございました。

    補足日時：2022/08/18 14:04

No.4 回答者： banzaiA 回答日時： 2022/08/15 12:56

ご質問は、

「重複したデータ(空白は除く)のVBA表記」を知りたいというのか、
「#REF!のエラー」が出なくなるようにしたいのか、
「重複と空白セルを除くデータをカウントする」方法をしりたいのか、
いったいどれなのでしょうか。
重複と空白セルを除くデータをカウントする方法なら、
UNIQUE関数で即可能ですよ。Microsoft365ですが、

この回答へのお礼

banzaiA様、回答ありがとうございます。

Excelのバージョンは2019の為、UNIQUE関数は使用できない様です。
#REF!エラーの原因はお調べしまして「セルの値を削除してしまった時や参照先が無効な場合に表示されるエラーです。」
とサイトに載っております処理がExcel上で行われるため引き起こされているエラーであることは理解しました。
このエラーを回避する関数の表記方法があるのか疑問を抱いたので質問させていただきました。

私の理想としましては関数で【重複と空白セルを除くデータをカウントする】
を知りたく、かつ指定範囲が変動しても反映させる方法があれば幸いです。

よろしくお願いいたします。

お礼日時：2022/08/15 20:48

No.3 回答者： kamejiro 回答日時： 2022/08/15 09:08

>DB上からデータを新たに反映させた際

これは、DBソフトのデータをコピペしたのでしょうか。
コピペの場合だったら、DBソフトを今のエクセルワークシートと関係ないエクセルワークシートに貼る。そのあと、一度貼り付けたワークシートから値貼り付けする。

あと、範囲が、「A1:A13」ですが、行数が同じですか。範囲を確認する。

No.2 回答者： emirioQ 回答日時： 2022/08/15 09:02

1.参照しているセルの型がおかしくないか？

(日付型が混ざっているとか？)

2.一度 SUMPRODUCTと-1 を外してCOUNTIFだけで動かしてみる。
この時点で#REF!となるかどうか

3.2が正常の場合COUNTIFの範囲指定をA1:A1に変えてみるとどうか？
(引数1.2ともに変えてみてくださいね)

4.Countifの結果が0になっていないか
1/0の結果は#REF!になります

今パソコンがないのではっきりとした回答ができませんが、解決に向けた検証方法を思いつく限り書かせていただきました！

問題解決の足枷になればと思います！

No.1 回答者： banzaiA 回答日時： 2022/08/15 08:43

>#REF!のエラーが表示され困っております。

#REF!の意味をご存じですか？
web検索で調べましょう。