|
|
| Excel VBA Macro ・ 値の操作 ・ FAQ |
|
番号が空白以外のデータを抽出して在籍者だけにし連番を振り直すには？
|
|
|
|
Question 67.2 |
|
 |
 |
|
|
190万Hitおめでとうございます。
Excelの操作感を向上させるために、仕事でVBAを組むことになりました。様々な参考書を読み、こちらのサイトでも参考にさせていただいたのですが、付け焼刃なため思うように作成することができません。お忙しいところ大変申し訳ありませんが、アドバイスよろしくお願い致します。（先着3名に入れていれば良いのですが・・・）
社員給与管理を Excel2000＋VBAで行っています。
VBAで行おうとしているのはA1列に作成した行番号の最適化です。
1年間個人の累積をとるため、退職者があるとそのまま残しておかなければなりません。また、行番号は在職している者のみに振っているため、今は退職者の行番号を消し、手動で番号を振りなおして対応しています。
行番号を最適化するには
1. A1列の文字を抽出する。（空欄以外を抽出する、もしくは値が入力されているセルを抽出する。）
※抽出にはセルごとに値の有無を見極め、値がある場合にはセル背景に色を置くなどして繰り返す方法を考えました。
2. 抽出されたセルに連続した値（1〜）を入力する。
・・・というようなことを考えています。
2の段階では特に漠然としすぎて、どのようにしていいのかわかりません。
試しにオートフィルタで >=1 と設定し（もしくは「空白以外」で設定し）抽出したのですが、連続したデータに置き換えることができないと知りました。
もしよろしければ、コードの書き方を教えてください。よろしくお願いいたします。
|
|
Answer |
Copyright (C) 2004.5.6 永井善王 |
|
|
190万ヒット達成の、お祝いのお言葉を頂戴しありがとうございます。
あなたのご希望は、下左図のように番号が消されている行を削除して下右図のようにし、番号を振り直したいということでしょうか。一応、この前提で回答させていただきます。
年末の状態 年始の状態
結論から言いますと、あなたが考えられたように、
まず、オートフィルタで空白以外の行を抽出し、
その結果を一旦、別のシートにコピーしてから、
オートフィルすれば良いのではないでしょうか。
であるならば、これらは Excelの一般操作で十分可能ですが、マクロにしたいということですので、自動記録で作っておきました。
下記をご覧ください。自動記録したものを見やすくするため適当に区切り、1から14までのコメントに操作方法を簡単に書いておきました。
Sub ○○番号が空白以外の行を抽出して別シートにコピーして連番を振り直す()
Sheets("Sheet1").Select '1 Sheet1を選択する
Range("A1").Select '2 A1セルを選択する
Selection.AutoFilter '3 [データ]−[オートフィルタ]クリック
Selection.AutoFilter Field:=1, Criteria1:="<>" '4 A1セルの▼、(空白以外のセル)クリック
'
Selection.CurrentRegion.Select '5 [編集]、[ジャンプ]、[セル選択]、[アクティブセル領
' 域]、[OK]クリック
Selection.Copy '6 [編集]、[コピー]クリック
'
Sheets("Sheet2").Select '7 Sheet2を選択する
Range("A1").Select '8 A1セルを選択する
ActiveSheet.Paste '9 [編集]、[貼り付け]クリック
'
Range("A2").Select '10 A2セルを選択する
Application.CutCopyMode = False '（勝手に自動記録されることがある）
ActiveCell.FormulaR1C1 = "1" '11 キーボードから 1 を入力する
Range("A3").Select '12 A3セルを選択する
ActiveCell.FormulaR1C1 = "2" '13 キーボードから 2 を入力する
Range("A2:A3").Select '14 A2:A3セル範囲を選択する
Selection.AutoFill Destination:=Range("A2:A5"), Type:=xlFillDefault '15 A3セルの右下隅
' の四角いマークにマウスポインタを合わせ、カーソル形
' 状が ＋ に変わったらクリックしたままでA6セルまでド
' ローする
Range("A2:A5").Select '（勝手に自動記録されることがある）
End Sub
少し根気がいるかもしれませんが、コメントを丹念に読みながら、あなたも自動記録してみてください。あなたのデータは、レイアウトなどがこれとは異なるため、工夫が必要かもしれませんが、頑張って作り上げることができれば、VBA開眼となるでしょう。
ポイントは、5.のアクティブセル領域の選択と、15.のオートフィルです。
なお、上記マクロは1年に1回だけ連番を振り直すと考えたため、退職者のデータを残していません。もし、毎月行う等で退職者データを残す必要があるならば、上記マクロに続けて、番号が空白のセルを抽出してアクティブセル領域をコピーし、上右図の末尾に貼り付けるようにすればよいかと思います。
参考ページ オートフィルの対象セル範囲を相対参照で指定したいが？
|
|
|
|
| |
|
|
