エクセルVBA及び関数

質問

初心者です。お教えお願いします

エクセル2003です

空白行及び0をなくしたいのですが（C列を入力された時点で自動でE列のようにしたい）
関数の場合及びVBAのシートコードを両方教えていただけないでしょうか
VBAのコードは勉強の為です

Sheet1
　　　A　　　　B　　　　　C　　　　　D　　　　　E　　　　　F
16　　　　　　　　　　文字A　　　　　　　　文字A
17　　　　　　　　　　文字B　　　　　　　　文字B
18　　　　　　　　　　　　0　　　　　　　　　文字C
19　　　　　　　　　　文字C　　　　　　　　文字D
20　　　　　　　　　　　　0　　　　　　　　　文字E
21　　　　　　　　　　文字D　　　　　　　　文字F　　　　　
22　　　　　
23　　　　　　　　　　文字E
24
25　　　　　　　　　　文字F

文字はC16～C80まで入っております
B16～E80までのセルのみで行いたいのですが
他の場所は関数やグラフ等がすでに入ってますのでいじりたくありません
E16～をフォームのコンボボックスのリストにしようと思ってます
よろしくお願いましす

回答No.1

tom04 · ベストアンサー

こんばんは！

単純に空白セル及び「0」のセルを削除して上詰めではダメなのですよね？

一例です。
Sheetモジュールにしていますので、
画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。

Sub Sample1() 'この行から
Dim i As Long, j As Long
Application.ScreenUpdating = False
For j = 2 To 5
For i = 80 To 16 Step -1
If Cells(i, j) = "" Or Cells(i, j) = 0 Then
Cells(i, j).Delete Shift:=xlUp
Cells(80, j).Insert Shift:=xlDown
End If
Next i
Next j
Application.ScreenUpdating = True
End Sub 'この行まで

こんな感じではどうでしょうか？m(_ _)m

お礼

ありがとうございます
VBAでする事に致しました
あとこのマクロをシートがアクティブなあいだ繰り返し実行しときたいのですが
教えていただけませんか
もう一つ別シートのC16：C80に表記するコードも教えて頂けますか?

お願いします

補足

ありがとうございました

なんとか自力で解決できそうです

回答No.3

MackyNo1

＞文字はC16～C80まで入っております
B16～E80までのセルのみで行いたいのですが
他の場所は関数やグラフ等がすでに入ってますのでいじりたくありません
E16～をフォームのコンボボックスのリストにしようと思ってます

「B16～E80までのセルのみで行いたい」の意味が良くわからないのですが、E１６セルにC列の０と空白を除いたリストを作成するならE16セルに以下の数式を入力し下方向にオートフィルして下さい。

=INDEX(C:C,SMALL(IF(($C$16:$C$80<>"")*($C$16:$C$80>0),ROW($C$16:$C$80),1000),ROW(A1)))&""

配列数式ですので、入力後Ctrl＋Shift+Enterで確定してください。

このリストを入力規則のリストにしたいなら、「挿入」「名前」「定義」で例えば「リスト」と名前を付けて以下の式を入力します。

=OFFSET($F$16,0,0,SUMPRODUCT((LEN($F$16:$F$80)>0)*1),1)

最後に「データ」「入力規則」で「リスト」を選択し、元の値の欄に「=リスト」と入力してください。

マクロを使いたいなら、以下のような手順で新しいマクロの記録をしてみてください。

「ツール」「マクロ」「新しいマクロの記録」で適当なマクロ名を付けて、C16～C80セルを選択し「データ」「フィルタ」「オートフィルタ」で「オプション」を選択し第一条件を「0」と「等しくない」AND「*」と「等しい」でOKし、そのまま右クリック「コピー」し、E16セルを選択し右クリック「貼り付け」して、最後に「データ」「フィルタ」「オートフィルタ」でフィルタを解除します。

回答No.2

tom04

No.1です！
＞関数の場合及びVBAの・・・
を見逃していました。

関数の場合、一気に！となると難しいと思われます。
とりあえず別Sheet（Sheet2）に表示する方法です。
Sheet3を作業用のSheetとして使用します。

Sheet3のB16セルに
=IF(OR(Sheet1!B16="",Sheet1!B16=0),"",ROW())
という数式を入れ、列方向・行方向にE80セルまでオートフィルでコピー！

Sheet2のB16セル（A1セルでも構いません）に
=IF(COUNT(Sheet3!B:B)<ROW(A1),"",INDEX(Sheet1!B:B,SMALL(Sheet3!B:B,ROW(A1))))
という数式を入れE80セルまで列方向・行方向にオートフィルでコピー！
（A1セルに数式を入れた場合はA1～D65セルですかね？）←行数は合わせてください。

これでB16～E80セルの空白・「0」を削除したものが表示されますので、
この範囲を範囲指定　→　右クリック　→　コピー　→　Sheet1のB16セルを選択
→　右クリック　→　「形式を選択して貼り付け」　→　「値」を選択しOK

この程度しか思いつきませんが、
参考になりますかね？m(_ _)m