Excel VBA質問箱 IV

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

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


3401 / 13645 ツリー ←次へ | 前へ→

【62453】セル範囲を指定して削除するには? AT 09/7/22(水) 19:13 質問[未読]
【62454】Re:セル範囲を指定して削除するには? かみちゃん 09/7/22(水) 19:21 発言[未読]
【62459】Re:セル範囲を指定して削除するには? AT 09/7/22(水) 22:34 お礼[未読]
【62461】Re:セル範囲を指定して削除するには? かみちゃん 09/7/22(水) 23:27 発言[未読]
【62465】Re:セル範囲を指定して削除するには? かみちゃん 09/7/22(水) 23:46 発言[未読]
【62479】Re:セル範囲を指定して削除するには? AT 09/7/24(金) 1:30 お礼[未読]
【62480】Re:セル範囲を指定して削除するには? かみちゃん 09/7/24(金) 6:22 発言[未読]
【62490】Re:セル範囲を指定して削除するには? AT 09/7/25(土) 13:44 お礼[未読]
【62478】Re:セル範囲を指定して削除するには? AT 09/7/24(金) 0:19 お礼[未読]

【62453】セル範囲を指定して削除するには?
質問  AT  - 09/7/22(水) 19:13 -

引用なし
パスワード
   Sub 不要セルを範囲選択し、削除( )
'セル[A1]の最下端行から1つ下の行にあるセルを選択する
  1.Range("A1").End(xlDown).Offset(1).Select

'セル[P1]の最下端行にあるセルを選択する
  2.Range("P1").End(xlDown).Offset(0).Select

上記の1.セル〜2.セルを範囲選択し、削除するには、どのようにマクロを
記述したら、よいでしょうか?
ACCESSで、データを抽出する度に、データの不要セル範囲が不定に
なるので、1.、2.のようにセル範囲を個別に指定して削除したいです。

すみませんが、よろしくお願いします。

【62454】Re:セル範囲を指定して削除するには?
発言  かみちゃん  - 09/7/22(水) 19:21 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>上記の1.セル〜2.セルを範囲選択し、削除するには、どのようにマクロを
>記述したら、よいでしょうか?

マクロの前に、一般操作で、特定の範囲を削除することはできますか?
できるなら、その操作を「マクロの記録」で記録してみてはいかがですか?
それでわからなければ、記録したマクロを提示してみてください。

【62459】Re:セル範囲を指定して削除するには?
お礼  AT  - 09/7/22(水) 22:34 -

引用なし
パスワード
   ▼かみちゃん さん:

早速の返信、どうもありがとうございます。


>マクロの前に、一般操作で、特定の範囲を削除することはできますか?
  ↓
通常のセル範囲は、削除することはできます。

>できるなら、その操作を「マクロの記録」で記録してみてはいかがですか?
  ↓
' 不要セルを削除する Macro

  Range("A164:P555").Select
  Selection.Delete Shift:=xlUp
  Range("A164").Select

かみちゃんさんの指摘とおり、上記のマクロを組み込んで動作確認を行ったところ
不要セルを削除できました。

でも、これは、データが<163行専用>なのです。

仕事で、ACCESS-2007を使い、データを抽出してEXCEL-2007で編集しているのです
がこのデータ量が毎回不定で困っています。

データの行数が、多い時で<530行>の時もあれば、少ない時で<80行>の時も
あり、毎回行数が一定でないのです。

毎日、仕事でデータ編集するのですが、データの行数が過去に一度も<555行>
を超えたことが無かったので、勝手に編集する行数範囲を最大で<555行>と
決めちゃいました。

>それでわからなければ、記録したマクロを提示してみてください。
  ↓
まだ、低レベルで、直接マクロを記述するスキルが無いので、EXCELのマクロ記録
機能を使い、マクロを作成しました。
下記に作成したマクロの一部分を抜粋しましたので、確認お願いします。

'[指示数]=[完了実績数]ならば、フラグ⇒○、ステータス⇒試済
          '非ならば、フラグ⇒×、ステータス⇒未
  Range("O2").Select
  ActiveCell.FormulaR1C1 = "=IF(RC[-2]=RC[-1],""○"",""×"")"
  Selection.AutoFill Destination:=Range("O2:O555")
  Range("O2:O555").Select
  Range("P2").Select
  ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""○"",""試済"",""未"")"
  Selection.AutoFill Destination:=Range("P2:P555")
  Range("P2:P555").Select
  ActiveWindow.SmallScroll Down:=9
  Range("O2").Select
  ActiveCell.FormulaR1C1 = "=IF(RC[-2]=RC[-1],""○"",""×"")"
  Selection.AutoFill Destination:=Range("O2:O555")
  Range("O2:O555").Select
  Range("P2").Select
  ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""○"",""試済"",""未"")"
  Selection.AutoFill Destination:=Range("P2:P555")
  Range("P2:P555").Select
  ActiveWindow.SmallScroll Down:=9
  
セル<M164〜M555>と、セル<N164〜N555>が共に空白である為に、
セル<O164〜O555>にフラグの『○』が入ります。

また、セル<P164〜P555>にステータスの『試済』が入ってしまいます。

実際には、今回のデータは全部で<163行>しか無いのに、164行目〜555行目に
フラグの『○』と、ステータスの『試済』が全て入るので、この不要箇所を削除
したいと考えています。

従って、下記の様に(1):(2)のセル範囲を指定して削除したいのですが、
これって、無理なのでしょうか?

'セル[A1]の最下端行から1つ下の行にあるセルを選択する
  Range("A1").End(xlDown).Offset(1).Select    ←(1)

'セル[P1]の最下端行にあるセルを選択する
  Range("P1").End(xlDown).Offset(0).Select    ←(2)

別に良い方法があれば、教えてください。

【62461】Re:セル範囲を指定して削除するには?
発言  かみちゃん  - 09/7/22(水) 23:27 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>従って、下記の様に(1):(2)のセル範囲を指定して削除したい

A列の値が入っている最終行より下の行以下を削除したいということでしたら、
Range(Range("A" & Rows.Count).End(xlUp).Offset(1), Range("P" & Rows.Count)).Select
で削除対象範囲が選択されますから、

マクロの記録のコードを流用して、

Range(Range("A" & Rows.Count).End(xlUp).Offset(1), Range("P" & Rows.Count)).Select
Selection.Delete Shift:=xlUp

として、さらに

Range(Range("A" & Rows.Count).End(xlUp).Offset(1), Range("P" & Rows.Count)).Delete Shift:=xlUp
とします。

ただし、このようにしなくても、

> Range("O2").Select
> ActiveCell.FormulaR1C1 = "=IF(RC[-2]=RC[-1],""○"",""×"")"
> Selection.AutoFill Destination:=Range("O2:O555")

これらのマクロをA列の値が入っている最終行までにすればいいのではないでしょうか?

> 別に良い方法があれば、教えてください。

今回の質問とは関係ないかもしれませんが、
AccessからどのようにExcelに取得しているのでしょうか?
ExcelからAccessのデータをVBAでCopyFromRecordsetを使って、抽出できるのはご存知ですか?

【62465】Re:セル範囲を指定して削除するには?
発言  かみちゃん  - 09/7/22(水) 23:46 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>ただし、このようにしなくても、
>
>> Range("O2").Select
>> ActiveCell.FormulaR1C1 = "=IF(RC[-2]=RC[-1],""○"",""×"")"
>> Selection.AutoFill Destination:=Range("O2:O555")
>
>これらのマクロをA列の値が入っている最終行までにすればいいのではないでしょうか?

これについては、以下のようにするとできると思います。
(提示されたコードの整理もしています)
このようにすると、そもそもの質問にある行削除は必要なくなると思います。

Sub Sample()
 Range("O2:P" & Rows.Count).Clear
 Range("O2").FormulaR1C1 = "=IF(RC[-2]=RC[-1],""○"",""×"")"
 Range("P2").FormulaR1C1 = "=IF(RC[-1]=""○"",""試済"",""未"")"
 Range("O2:P2").Copy Range("O2:O" & Range("A" & Rows.Count).End(xlUp).Row)
End Sub

【62478】Re:セル範囲を指定して削除するには?
お礼  AT  - 09/7/24(金) 0:19 -

引用なし
パスワード
   ▼かみちゃん さん:
こんにちは。AT です。

回答を頂きまして、どうもありがとうございます。

>A列の値が入っている最終行より下の行以下を削除したい
Range(Range("A" & Rows.Count).End(xlUp).Offset(1), Range("P" & Rows.Count)).Select
Selection.Delete Shift:=xlUp

Range(Range("A" & Rows.Count).End(xlUp).Offset(1), Range("P" & Rows.Count)).Delete Shift:=xlUp

↑上記のマクロを組み込んで動作確認を行ったところ、
不要セルを見事に削除することが出来ました。


>今回の質問とは関係ないかもしれませんが、
>AccessからどのようにExcelに取得しているのでしょうか?

外部データのエクスポート先をEXCELファイル(xlsx)に指定して
データの取り込みしています。


>ExcelからAccessのデータをVBAでCopyFromRecordsetを使って、抽出できるのはご存知ですか?

すみません。
知りませんでした。(まだまだ、未熟です。。。)

CopyFromRecordsetとは、どのような機能なのですか?

どのような場面で使用するのか、教えて頂けませんか?

【62479】Re:セル範囲を指定して削除するには?
お礼  AT  - 09/7/24(金) 1:30 -

引用なし
パスワード
   ▼かみちゃん さん:
こんにちは。AT です。

>このようにすると、そもそもの質問にある行削除は必要なくなると思います。
>
>Sub Sample()
> Range("O2:P" & Rows.Count).Clear
> Range("O2").FormulaR1C1 = "=IF(RC[-2]=RC[-1],""○"",""×"")"
> Range("P2").FormulaR1C1 = "=IF(RC[-1]=""○"",""試済"",""未"")"
> Range("O2:P2").Copy Range("O2:O" & Range("A" & Rows.Count).End(xlUp).Row)
>End Sub

↑上記のマクロを組み込んだところ、うまくいきました。

確かに、かみちゃんさんのご指摘のとおり、
わざわざ、データの編集範囲を"555行"に設定して、後から削除するのは、
効率が悪いですね。

また、この方法だと、今後、データが"555行"を超えたとしても、対処できる
ので、とても素晴らしいです。感謝です。

すみませんが、あと、1点教えてください。

[P列]のステータスが"試済"の場合、ACCESSで、データを抽出した時点で既に
[H列]の完了実績日の各欄に、試済完了実績日が入力されているのですが、
[P列]のステータスが"未"の場合、[H列]の完了実績日の各欄は、データが何も
入力されていないタダの空欄になっています。

この空欄に"―"を入力したいのですが、どのようにマクロを記述すればよろしい
でしょうか?

下記のマクロもExcelの自動記録機能を使い、作成しました。

'ステータス_未のデータを表示させる Macro
  ActiveSheet.Range("$A$1:$P$55").AutoFilter Field:=16, Criteria1:="未"
  ActiveWindow.SmallScroll ToRight:=-3
  Range("H1:H555").Select
  With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
  End With
  With Selection.Font
    .Name = "MS Pゴシック"
    .Size = 11
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
    .TintAndShade = 0
    .ThemeFont = xlThemeFontNone
  End With
  Range("H1").Select
  
'[P列]のステータスが[未]の場合、[H列]の完了実績日の欄に[―]を入れる Macro
  Range("H2:H555").Select
 'Range("H3:H555").Select 'H2が試済だった場合は、H3から―を入力する
  Range("H2").Activate
 'Range("H3").Activate   'H2が試済だった場合は、H3から―を入力する
  ActiveCell.FormulaR1C1 = "―"
  Selection.FillDown
  
'フィルターを掛け、ステータス[試済]&[未]を表示させる Macro'
  Range("P1").Select
  ActiveSheet.Range("$A$1:$P$555").AutoFilter Field:=16
  Range("A2").Select


    

【62480】Re:セル範囲を指定して削除するには?
発言  かみちゃん  - 09/7/24(金) 6:22 -

引用なし
パスワード
   こんにちは。かみちゃん です。

> CopyFromRecordsetとは、どのような機能なのですか?

CopyFromRecordsetメソッドのヘルプを参照していただいたほうがいいのですが、

ADO または DAO の Recordset オブジェクトの内容をワークシートにコピーします。
指定されたセル範囲の左上端を起点として、データを貼り付けます

とあります。

なお、以下のURLなどが参考になりますでしょうか?
http://www.asahi-net.or.jp/~ef2o-inue/vba_o/sub05_130_030.html
過去ログや、Webを検索するといろいろ出てきます。

さらに簡単なサンプルとしては、
http://billyboy.blog81.fc2.com/blog-entry-191.html#extended
に出ているのですが、以下のような感じです。

ActiveCellを左上とした範囲に出力されます。

Sub Sample()
 Dim con, rec, i As Integer
 
 'ADOのConnectionオブジェクトを作成
 Set con = CreateObject("ADODB.Connection")
 With con
  '接続文字列:接続するデータベースによって異なる
  .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" _
   & ";Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
  .Open
 End With
 
 'ADOのRecordsetオブジェクトを作成
 Set rec = CreateObject("ADODB.Recordset")
 'Northwind.mdbの社員テーブルを開く
 rec.Open "社員", con
 
 For i = 0 To rec.Fields.Count - 1
  ActiveCell.Offset(0, i).Value = rec(i).Name
 Next i
 
 ActiveCell.Offset(1, 0).CopyFromRecordset rec
End Sub

今回は、必要なさそうですが、
CopyFromRecordsetを使うと、Excelの最大行を超える場合は、シートに分割することなどが簡単なので、便利です。

> [P列]のステータスが"試済"の場合、ACCESSで、データを抽出した時点で既に
> [H列]の完了実績日の各欄に、試済完了実績日が入力されているのですが、
> [P列]のステータスが"未"の場合、[H列]の完了実績日の各欄は、データが何も
> 入力されていないタダの空欄になっています。
>
> この空欄に"―"を入力したいのですが、どのようにマクロを記述すればよろしいでしょうか?

空欄というのは、一般操作で
A列の値が入っている最終行が163行とした場合、
H2:H163を選択して、「編集」−「ジャンプ」の[セル選択]をクリックし、
「空白」にチェックをして、[OK]をクリックしたときに選択される範囲
でいいのでしょうか?
それでいいのであれば、その操作を「マクロの記録」で記録してみて、さらに修正を加えます。
修正の際、セル範囲については、[62479]で解決した範囲設定方法を応用します。

On Error Resume Next
Range("H2:H" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Value = "-"
On Error GoTo 0

元々のデータベースの内容としたいことがわかれば、もっと効率のよい方法はアドバイスできるかもしれません。

【62490】Re:セル範囲を指定して削除するには?
お礼  AT  - 09/7/25(土) 13:44 -

引用なし
パスワード
   ▼かみちゃん さん:
こんにちは。AT です。

CopyFromRecordset について、詳細に教えてくださり、ありがとうございます。

CopyFromRecordsetメソッドのヘルプを参照したのですが、現在の私のレベルでは
理解することが出来ませんでした。
指定Webも、ちょっと、拝見させていただきました。

せっかく、教えていただいたのに、理解できず、申し訳ないです。


空欄に[―]を入れる件については、かみちゃんさんの記述したマクロを組み込んだところ、ステータス:試済未の欄だけ、見事に[―]を入力出来ました。
ありがとうございます。

<On Error Resume Next> というエラーを無視して次の処理に進むという便利なステートメントがあったんですね。

私は、マクロを勉強し始めたのは、今から1カ月前です。

マクロを作成する時、EXCELの自動記録機能を使っています。
その自動記述されたマクロを『VBA逆引き辞典』を参照して、編集するのですが
過去にエラーが出たことが何回もありました。このエラーを飛ばして次に進みたい時があったのですが、分からず、エラー箇所を編集して、無理やり処理が進む様に
何通りものマクロを作成して、使い分けしていました。

この時、この<On Error Resume Next>という、ステートメントを知っていたら、
こんな無駄なことをする必要が無かったんですね。

かみちゃんさんの御陰で、また、一つ勉強させていただくことが出来ました。
感謝です^^

かみちゃんさんは、まさに、マクロの達人"ですね。

もし、よろしければ、マクロを勉強する上で、為になる書籍等や方法、何でも
いいので、お聞かせいただけたら、とても嬉しく思います。

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