エクセル関数で行を削除したい

質問

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

マクロではなく、関数で指定した行を削除したいのです。具体的には下記です。表の横の列を使って、部門コード99の列を削除したいのです。

部門コード,部門名,・・
2,--------------　　　　　2,--------------　　　
99,--------------　　　　 3,--------------
3,-------------- → 4,--------------
4,-------------- 5,--------------
99,-------------- 6,--------------
5,--------------
6,--------------

個人的な見解としては、countifでその行までの99の数をカウントし
て、そのカウントの数だけ下の行を持ってくるのではと思うのですが、
関数でそのようなことが可能でしょうか。

回答No.4

merlionXX · ベストアンサー

どのような配置かわからないのですが、仮にA1から下へA7までに
2,--------------
99,--------------
以下略
と並んでいるとします。

C1に
=IF(LEFT(A1,2)<>"99",A1,"")　といれてC7まで式をコピー
これで対象データのみ表示されますね。

次に、B1に
=IF(C1="","",COUNTA($C$1:C1)-COUNTBLANK($C$1:C1))　といれてB7まで式をコピー
これで、表示データに順番がつきました。

最後に、D1に
=IF(ISNA(VLOOKUP(ROW(A1),$B$1:$C$7,2,FALSE)),"",VLOOKUP(ROW(A1),$B$1:$C$7,2,FALSE))　といれてD7まで式をコピー

これで部門コード99の行が削除されたデータとなります。

お礼

いつもお世話になります。
このような方法は思いつきませんでした。
ありがとうございました。

回答No.3

nobu555

>個人的な見解としては、countifでその行までの99の数をカウントし
>て、そのカウントの数だけ下の行を持ってくるのではと思うのですが
ここまで解っているのでしたら、
後は、IF関数とOFFSET関数との組合せで
ある程度は可能だと思います。

しかし、部門コード“99”が連続したときに
ネストが増えて、限界が来るのではないでしょうか。

お礼

いつもお世話になります。
OFFSET関数、初めて知りましたが、うまくいきました。
ありがとうございました。

しかし、やはりおっしゃる通り、３つ以上99が連なると無理ですし、IFで修正するのも限界がありますね。

回答No.2

hana-hana3

＞マクロではなく、関数で指定した行を削除したいのです。

関数では削除等のシートの操作はできません。
ＶＢＡも使わないとすれば、オートフィルタで”99”を除外して表示し、別のシートにコピーする方法が有ります。

補足

その方法でも良いのですが、あくまで削除を行わずに
表の右側に、部門コード９９を除外した表を作りたいのです。

イメージとしては、イコールで右のセルにコピーしていく
のですが、"99"が現れた行からは１つ下のセルをコピーして
いき、また"99"が現れたらそこからは２つ下のセルをコピー
していく・・・・というような形です。

列 A列,右の列
1 1,"=A1"
2 2,"=A2"
3 99,"=A4"
4 5,"=A5"
5 99,"=A7"
という風にしたいのですが、それを関数で自動的にしたいのですが、
COUNTIFで数えた数だけ下の行を参照する方法が分からないのです。

回答No.1

ok5181103

そこまでお分かりでしたら、削除ですが、
セル範囲Ｃ３：Ｃ５の行＝行３～５を削除
Range("C3:E5").EntireRow.Delete
みたいにするということ？
変数に Set で If を使用して、（For でループしたりして）
MyRange.EntireRow.Delete
みたいにすることを聞きたいのですよね？

補足

いつもお世話になります。
質問させていただきました主旨は、VBAではなく、関数で
作りたいのです。

イメージとしては、イコールで右のセルにコピーしていく
のですが、"99"が現れた行からは１つ下のセルをコピーして
いき、また"99"が現れたらそこからは２つ下のセルをコピー
していく・・・・というような形です。

列 A列,右の列
1 1,"=A1"
2 2,"=A2"
3 99,"=A4"
4 5,"=A5"
5 99,"=A7"
という風にしたいのですが、それを関数で自動的にしたいのですが、
COUNTIFで数えた数だけ下の行を参照する方法が分からないのです。