Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


10084 / 13646 ツリー ←次へ | 前へ→

【23763】今日よりも過去の日付を削除する 経理課手形大量発行担当 05/4/3(日) 16:25 質問[未読]
【23764】Re:今日よりも過去の日付を削除する IROC 05/4/3(日) 17:03 回答[未読]
【23771】Re:今日よりも過去の日付を削除する ponpon 05/4/3(日) 21:31 回答[未読]
【23774】Re:今日よりも過去の日付を削除する 経理課手形大量発行担当 05/4/3(日) 23:21 質問[未読]
【23775】Re:今日よりも過去の日付を削除する ponpon 05/4/4(月) 0:08 回答[未読]
【23805】Re:今日よりも過去の日付を削除する 経理課手形大量発行担当 05/4/4(月) 22:37 発言[未読]
【23809】Re:今日よりも過去の日付を削除する ponpon 05/4/4(月) 22:59 回答[未読]
【23810】Re:今日よりも過去の日付を削除する ウッシ 05/4/4(月) 23:09 回答[未読]
【23815】Re:今日よりも過去の日付を削除する ponpon 05/4/4(月) 23:35 質問[未読]
【23816】Re:今日よりも過去の日付を削除する ウッシ 05/4/4(月) 23:46 回答[未読]
【23817】Re:今日よりも過去の日付を削除する ponpon 05/4/4(月) 23:57 発言[未読]
【23811】Re:今日よりも過去の日付を削除する 経理課手形大量発行担当 05/4/4(月) 23:09 お礼[未読]
【23772】Re:今日よりも過去の日付を削除する ちゃっぴ 05/4/3(日) 23:00 回答[未読]
【23777】Re:今日よりも過去の日付を削除する ponpon 05/4/4(月) 1:57 質問[未読]
【23778】Re:今日よりも過去の日付を削除する ちゃっぴ 05/4/4(月) 2:21 回答[未読]
【23779】Re:今日よりも過去の日付を削除する ちゃっぴ 05/4/4(月) 2:35 回答[未読]
【23795】Re:今日よりも過去の日付を削除する ponpon 05/4/4(月) 18:20 発言[未読]
【23786】Re:今日よりも過去の日付を削除する Kein 05/4/4(月) 12:33 回答[未読]
【23808】Re:今日よりも過去の日付を削除する 経理課手形大量発行担当 05/4/4(月) 22:52 お礼[未読]
【23813】Re:今日よりも過去の日付を削除する Kein 05/4/4(月) 23:34 回答[未読]

【23763】今日よりも過去の日付を削除する
質問  経理課手形大量発行担当  - 05/4/3(日) 16:25 -

引用なし
パスワード
   Eの列に様々な日付が入っております(例:2010/3/31という形式で)。
もし、今日よりも過去の日付が存在したならば、その行を削除して上へ詰めたいとき、マクロはどうしたらよいでしょうか。
よろしくお願いします。

【23764】Re:今日よりも過去の日付を削除する
回答  IROC  - 05/4/3(日) 17:03 -

引用なし
パスワード
   オートフィルタで抽出して削除してはみては?

【23771】Re:今日よりも過去の日付を削除する
回答  ponpon  - 05/4/3(日) 21:31 -

引用なし
パスワード
    ▼経理課手形大量発行担当 さん:
 ponponです。こんばんは。
オートフィルターを使ってやってみました。
一行目には、見出しが入っているものとし、データは2行目からとします。
また、sheetは、アクティブになっているものとします。


>Eの列に様々な日付が入っております(例:2010/3/31という形式で)。
>もし、今日よりも過去の日付が存在したならば、その行を削除して上へ詰めたいとき、マクロはどうしたらよいでしょうか。
>よろしくお願いします。

Sub test()
  Dim FilterRng As Range
  
  Set FilterRng = Range("E1").CurrentRegion
    ’フィルターをかける範囲は、"E1"のCurrentRegionとします
  With FilterRng
    .AutoFilter field:=5, Criteria1:="<" & DateValue(Now)
    ’A列からデータがあるとするのでE列は5番目、今日より前
    Application.DisplayAlerts = False
    .SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
  End With
End Sub


【23772】Re:今日よりも過去の日付を削除する
回答  ちゃっぴ  - 05/4/3(日) 23:00 -

引用なし
パスワード
   >もし、今日よりも過去の日付が存在したならば、
>その行を削除して上へ詰めたいとき、マクロはどうしたらよいでしょうか。

Sortをうまく使いこなしましょう。

まず、空いている列(作業列)に現在の行番号を書いておきます。

次に日付順にSortします。
そうすれば、今日以前のDataが存在した場合、
一箇所に集まりますのでそれをごそっと削除。
(元の順番に戻す必要がないのであれば、
Clear, ClearContentsを使うほうがお勧めです。)

最後に作業列でSortして、元の並びに戻して、
作業列のDataを消去してやれば、完成です。

AutoFilterを使った場合、内部でやっていることは
Union使って行を連結させているわけなので、速度は遅いですよ。

【23774】Re:今日よりも過去の日付を削除する
質問  経理課手形大量発行担当  - 05/4/3(日) 23:21 -

引用なし
パスワード
   ▼ponpon さん:

返信ありがとうございます。
1行目に見出しが入っているとして、
ponponさんが作っていただいたので挑戦してみたのですが、見出しが消えてしまいます。
私のは、見出しが3行目に入っています。A1,A2にも値が入っており、これも消えてほしくないです。説明が足りなくて申し訳ありません。
ponponさんの作っていただいたので自分で加工に挑戦してみたのですがうまくできませんでした。初心者ですみません。どうしたらよいでしょうか?

【23775】Re:今日よりも過去の日付を削除する
回答  ponpon  - 05/4/4(月) 0:08 -

引用なし
パスワード
   ponponです。

Sub test()
  Dim FilterRng As Range
  
  Set FilterRng = Range("E2").CurrentRegion
           ↑
  ここでフィルターをかける範囲を指定していますので、
  Set FilterRng = Range("A3:終わりの右下セル番地")で指定してください。

【23777】Re:今日よりも過去の日付を削除する
質問  ponpon  - 05/4/4(月) 1:57 -

引用なし
パスワード
   ponponです。
考えてやってみました。無駄な部分があるとは思いますが。。。
test1もtest2も
一度では削除できず、2度3度目で、すべて削除されます。
理由がわかりません。なぜでしょう?
dataは、H列まで入っていて、E列に日付データがあります。

Sub test1()
  Dim myRng As Range
  Dim r As Range
  
  Set myRng = Range("E2", Cells(Rows.Count, 5))
  For Each r In myRng
   If r.Value <> "" Then
    If r.Value < DateValue(Now) Then
      Application.DisplayAlerts = False
      r.EntireRow.Delete
      Application.DisplayAlerts = True
    End If
   End If
  Next


Sub test2()
  Dim myRow As Long
  Dim i As Long
  Dim myRng As Range
  Dim mydelRng As Range
  Dim r As Range
 
 With ActiveSheet
  Application.ScreenUpdating = False
  myRow = .Cells(Rows.Count, 5).End(xlUp).Row
  Set myRng = .Range("A1").CurrentRegion
 
  For i = 1 To myRow
  .Cells(i, 9).Value = i
  Next
 
  myRng.Sort key1:=.Range("E1")
  Set mydelRng = .Range("E1", "E" & myRow)
   For Each r In mydelRng
     If r.Value < DateValue(Now) Then
       Application.DisplayAlerts = False
       r.EntireRow.Delete
       Application.DisplayAlerts = True
     End If
   Next
  Set myRng = .Range("A1").CurrentRegion
   myRng.Sort key1:=.Range("I1")
   
   For i = 1 To myRow
    .Cells(i, 9).Value = ""
   Next
  Application.ScreenUpdating = True
 End With
End Sub

【23778】Re:今日よりも過去の日付を削除する
回答  ちゃっぴ  - 05/4/4(月) 2:21 -

引用なし
パスワード
   >考えてやってみました。無駄な部分があるとは思いますが。。。
>test1もtest2も
>一度では削除できず、2度3度目で、すべて削除されます。
>理由がわかりません。なぜでしょう?

【23731】Re:For Next と For Each文の関係 
http://www.vbalab.net/vbaqa/c-board.cgi?cmd=one;no=23731;id=excel

でも書きましたが、Cell(Row, Column)はIndexで指定できますよね?
Indexで指定できるような Collection Object を For Each 〜 Next Loop で
削除するとこのような現象が発生します。

というのは、For 〜 Next Loop を Index で
昇順(加算)に回しているのと一緒だからです。

そういった場合、逆順(減算)でLoopを回してやることにより、
回避できますが、ところがどっこいRow, Columnの削除は
非常に処理が重いのです。

これに関しては、Row, ColumnにIndexがついていることからもわかるでしょう?
削除するとそれ以降のIndexを再構成しなくてはなりませんから・・・

なので、私はめったに削除・挿入は使いません。
たいていは、Clear, ClearContentsで事足りるはずです。

ちなみに私が意図したまとめて削除とは、Loop内で開始行と終了行を取得し、
Loop終了後に削除すると言う意味です。
連続した範囲の削除は、それほど時間がかかりません。
(分断された範囲とでTestして、処理時間を計測してみるとよいでしょう)
もっとも、速度を求めるなら配列を使いますし、
Sortした上でAutoFilterを使うのはありでしょう。

あと、この私の発言は、もともとのDataがばらばらに並んでいて、
削除するものが非常に多いことが前提です。

1行しか削除しない場合とか、すでにきれいに並んでいるDataでは
こっちのほうが遅くなる場合もあります。

【23779】Re:今日よりも過去の日付を削除する
回答  ちゃっぴ  - 05/4/4(月) 2:35 -

引用なし
パスワード
   > というのは、For 〜 Next Loop を Index で
> 昇順(加算)に回しているのと一緒だからです。

これをStep実行してみてください。

Sub Test1()
  Dim i As Long

  For i = 10 to 100
    Rows(i).Delete
  Next i
End Sub

Sub Test2()
  Dim i As Long

  For i = 100 to 10 Step -1
    Rows(i).Delete
  Next i
End Sub

Sub Test3()
  Rows(10).Resize(90).Delete
End Sub

原因が一目瞭然になるはずです。
ついでに処理時間を計測してみるとよいかも・・・

【23786】Re:今日よりも過去の日付を削除する
回答  Kein  - 05/4/4(月) 12:33 -

引用なし
パスワード
   Sub Test()
  Dim LR As Long, LC As Long

  LR = Range("E65536").End(xlUp).Row - 3
  LC = Range("IV3").End(xlToLeft).Column + 1
  Application.ScreenUpdating = False
  Cells(3, LC).Value = "Check"
  On Error GoTo ELine
  With Cells(4, LC).Resize(LR)
   .Formula = "=IF($E4<TODAY(),""NO"",ROW())"
   .SpecialCells(3, 2).EntireRow.ClearContents
  End With
  On Error GoTo 0
  Range(Cells(3, 1), Cells(3, LC)).Resize(LR) _
  .Sort Key1:=Cells(3, LC), Order1:=xlAscending, _
  Header:=xlYes, Orientation:=xlSortColumns
ELine:
  Columns(LC).ClearContents
  Application.ScreenUpdating = True
End Sub
 
で、どうかな ? 
 

【23795】Re:今日よりも過去の日付を削除する
発言  ponpon  - 05/4/4(月) 18:20 -

引用なし
パスワード
   ▼ちゃっぴ さん:
ponponです。こんばんは。

>ついでに処理時間を計測してみるとよいかも・・・

前にも乱数で処理時間の計測をしましたが、これで2回目です。
>
>Sub Test1()
 の結果 240ms
>
>Sub Test2()
 の結果 231ms
>
>Sub Test3()
 の結果 10ms

という結果でした。
全然速さが違います。また、一つ脱初心者です。(忘れなければ)

ちゃっぴさんありがとうございました。
経理課手形大量発行担当 さん 横からいろいろすみませんでした。

【23805】Re:今日よりも過去の日付を削除する
発言  経理課手形大量発行担当  - 05/4/4(月) 22:37 -

引用なし
パスワード
   ありがとうございます!
でも、まだA3からE3にある見出しが消えてしまいます。
A4からE29まで情報がはいっているとして、以下のように書いたのですが・・・。

Set FilterRng = Range("a3:e29")
  With FilterRng
    .AutoFilter field:=5, Criteria1:="<" & DateValue(Now)
    Application.DisplayAlerts = False
     .SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
End With

【23808】Re:今日よりも過去の日付を削除する
お礼  経理課手形大量発行担当  - 05/4/4(月) 22:52 -

引用なし
パスワード
   ▼Kein さん:
返信ありがとうございます!こちらも試してみましたが、処理が速い感じがしますね。
ただ、過去の日付が存在したものは削除されたのですが、一番最後の行に入っているデータと、下から2行目に入っていたデータの間に空白行ができてしまいます。
それを解決するためにまた別にマクロを追加すればいいのですけどね。

【23809】Re:今日よりも過去の日付を削除する
回答  ponpon  - 05/4/4(月) 22:59 -

引用なし
パスワード
   ponponです。こんばんは。

一応これでできると思いますが、この下のレスを十分お読みください。

Sub Test()
  Dim FilterRng As Range
  
  Set FilterRng = Range("A3:E29")
  
  
  With FilterRng
    .AutoFilter field:=5, Criteria1:="<" & DateValue(Now)
    Application.DisplayAlerts = False
    .Offset(1, 0).SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    .AutoFilter
  End With
End Sub

【23810】Re:今日よりも過去の日付を削除する
回答  ウッシ  - 05/4/4(月) 23:09 -

引用なし
パスワード
   こんばんは

データ範囲だけFilterRngにセットしておいて、こんな感じでも

Sub test1()
  Dim FilterRng As Range
  Set FilterRng = Range("A4:E29")
  Application.ScreenUpdating = False
  With FilterRng
    .Cells(1)(0).AutoFilter field:=5, Criteria1:="<" & DateValue(Now)
    Application.DisplayAlerts = False
    .SpecialCells(xlCellTypeVisible).Delete
    Application.DisplayAlerts = True
    .Cells(1)(0).AutoFilter
  End With
  Application.ScreenUpdating = True
End Sub

計算式で処理する方がなんとなく好きですけど。

【23811】Re:今日よりも過去の日付を削除する
お礼  経理課手形大量発行担当  - 05/4/4(月) 23:09 -

引用なし
パスワード
   ▼ponpon さん:

確かに、できました。完璧です!
回答してくださってありがとうございました。

【23813】Re:今日よりも過去の日付を削除する
回答  Kein  - 05/4/4(月) 23:34 -

引用なし
パスワード
   んー・・たぶん
>Range(Cells(3, 1), Cells(3, LC)).Resize(LR)

Range(Cells(3, 1), Cells(3, LC)).Resize(LR + 1)

という修正が必要だったのでしょう。
あと数式を入れたままでソートなどすると、結果がおかしくなる可能性があるので、
計算の直後に値のみにしてしまう処理も組み込んでみます。

Sub Test()
  Dim LR As Long, LC As Long

  LR = Range("E65536").End(xlUp).Row - 3
  LC = Range("IV3").End(xlToLeft).Column + 1
  Application.ScreenUpdating = False
  Cells(3, LC).Value = "Check"
  On Error GoTo ELine
  With Cells(4, LC).Resize(LR)
   .Formula = "=IF($E4<TODAY(),""NO"",ROW())"
   .Copy
   .PasteSpecial xlPasteValues 
   .SpecialCells(2, 2).EntireRow.ClearContents
  End With
  Application.CutCopyMode = False
  On Error GoTo 0
  Range(Cells(3, 1), Cells(3, LC)).Resize(LR + 1) _
  .Sort Key1:=Cells(3, LC), Order1:=xlAscending, _
  Header:=xlYes, Orientation:=xlSortColumns
ELine:
  Columns(LC).ClearContents
  Application.ScreenUpdating = True
End Sub

【23815】Re:今日よりも過去の日付を削除する
質問  ponpon  - 05/4/4(月) 23:35 -

引用なし
パスワード
   ponponです。こんばんは。

.Cells(1)(0)の書き方なのですが、
Cells(1)は、FilterRngの中の一番目(インデックス1?)のセルという意味は知っているのですが、
Cells(1)(0)の場合その一つ上を参照しているようですが、このような書き方もあるのですね。(0)の意味は?・・・・

>計算式で処理する方がなんとなく好きですけど。
とは、具体的にどのように、もしよろしければ教えて頂きたいのですが?

【23816】Re:今日よりも過去の日付を削除する
回答  ウッシ  - 05/4/4(月) 23:46 -

引用なし
パスワード
   こんばんは、ponpon さん

>.Cells(1)(0)の書き方なのですが、
>Cells(1)は、FilterRngの中の一番目(インデックス1?)のセルという意味は知っているのですが、
>Cells(1)(0)の場合その一つ上を参照しているようですが、このような書き方もあるのですね。(0)の意味は?・・・・
省略した不精な書き方です。m(__)m
.Cells(1, 1).Item(0, 1)
.Cells(0, 1)ではエラーになりますので、Itemを使いますです。

>>計算式で処理する方がなんとなく好きですけど。
>とは、具体的にどのように、もしよろしければ教えて頂きたいのですが?
Keinさんのレスがそうなんですけど、行削除は遅いのでクリアしてソートされてますよね。

自分も、行削除の場合は作業列に数式セットしてSpecialCellsで該当行を削除します。
あまり処理速度も気にしないのでクリアしてソートとかもしないでDeleteしちゃいます。
(遅いとクレーム付いたら直しますけど。)

【23817】Re:今日よりも過去の日付を削除する
発言  ponpon  - 05/4/4(月) 23:57 -

引用なし
パスワード
   ▼ウッシ さん:

>省略した不精な書き方です。m(__)m
>.Cells(1, 1).Item(0, 1)
>.Cells(0, 1)ではエラーになりますので、Itemを使いますです。
この省略形は、初めて知りました。


>Keinさんのレスがそうなんですけど、行削除は遅いのでクリアしてソートされてますよね。

ケインさんのコードを試してみて、ClearContentsした後ソートすることで、
行削除と同じになることを初めて知りました。
まだまだ勉強不足です。
ありがとうございました。

10084 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free