[[20141119120408]] 『マクロを使用しての行の削除方法と時間短縮方法』(ららら) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『マクロを使用しての行の削除方法と時間短縮方法』(ららら)

CSVで出力されたデータをエクセルで検索できるようにしたいのですが、出力されたデータが下記のような形式で検索しにくいです。
わかる範囲でマクロを組んでみましたが、行き詰ってしまいましたのでご教授お願い致します。

CSVをエクセルに読み込んだままの状態

      A          B         C       D
 1  番号      会社名  工事名  区分
 2  1       あい建設  
 3                        A工事  1
 4                        B工事  1
 5   2        石井建設  
 6                        C工事  1
 7   3        上野工業 
 8                        D工事   1
 9                                 2
 10                       E工事  1
 :

ここから最終的には下記のようにしたいです

     A           B         C       D   
 1  番号      会社名  工事名  区分
 2  1       あい建設  A工事  1
 3   1        あい建設  B工事  1 
 4   2        石井建設  C工事  1
 5   3        上野工業    D工事  1
 6   3        上野工業           2
 7   3       上野工業    E工事  1

わかる範囲で作ったものは下記です

Sub Macro1()
'番号コピー

Dim 基準セル As Range

   Application.ScreenUpdating = False

   Range("D65536").End(xlUp).Offset(1, -3).Activate
   ActiveCell.Value = "ここまで"

   Set 基準セル = Range("A2")

   Do Until 基準セル.Value = "ここまで"

    If 基準セル.Offset(2, 0).Value <> "" Then
     基準セル.Copy
     基準セル.Offset(1, 0).Select
     ActiveSheet.Paste
     Application.CutCopyMode = Fals
     Set 基準セル = 基準セル.Offset(2, 0)     
    Else
     基準セル.Copy    
     基準セル.Offset(1, 3).Activate
     ActiveCell.Offset(0, -3).Name = "はじめ"
     ActiveCell.End(xlDown).Offset(0, -3).Name = "おわり"
     Range("はじめ", "おわり").Select
     ActiveSheet.Paste
     Application.CutCopyMode = False

     Set 基準セル = Range("おわり").Offset(1, 0)
     基準セル.Activate   
    End If

  Loop
    Application.ScreenUpdating = True 
End Sub

Sub Macro2()
'会社名コピー

Dim 基準セル As Range

   Application.ScreenUpdating = False

   Range("D65536").End(xlUp).Offset(1, -2).Activate
   ActiveCell.Value = "ここまで"

  Set 基準セル = Range("B2")

   Do Until 基準セル.Value = "ここまで"

    If 基準セル.Offset(2, 0).Value <> "" Then
     基準セル.Copy
     基準セル.Offset(1, 0).Select
     ActiveSheet.Paste
     Application.CutCopyMode = Fals
     Set 基準セル = 基準セル.Offset(2, 0)     
    Else
     基準セル.Copy
     基準セル.Offset(1, 2).Activate
    ActiveCell.Offset(0, -2).Name = "はじめ"
    ActiveCell.End(xlDown).Offset(0, -2).Name = "おわり"
    Range("はじめ", "おわり").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False

    Set 基準セル = Range("おわり").Offset(1, 0)
    基準セル.Activate   
   End If

  Loop
   Application.ScreenUpdating = True
 End Sub

この後に元々番号、会社名が入力されているセルを削除したいのですが、自分の作ったものだと削除したいセルを基準セルとしているため、削除がうまくできないです。
その他に問題点としては
1、番号と会社名を同時に処理できていない
2、データが2万件以上あり時間がかかる
です。

宜しくお願い致します。

< 使用 Excel:Excel2013、使用 OS:Windows8 >


 こんな感じでいかがでしょう?
 提示されたデータでは出来ました。
    Sub rarara()
        ActiveSheet.Copy after:=Sheets(Sheets.Count)
        Dim a: a = ActiveSheet.UsedRange.Value
        Dim i As Long
        Dim n As Long: n = 2
        Dim w(1)
        Dim res: ReDim res(1 To UBound(a, 1), 1 To UBound(a, 2))
        For i = 2 To UBound(a, 1)
            If a(i, 1) <> "" Then
                w(0) = a(i, 1)
                w(1) = a(i, 2)
            Else
                res(n, 1) = w(0)
                res(n, 2) = w(1)
                res(n, 3) = a(i, 3)
                res(n, 4) = a(i, 4)
                n = n + 1
            End If
        Next i
        ActiveSheet.UsedRange.Value = res
        Range("A1").Resize(, UBound(a, 2)).Value = a
    End Sub

(稲葉) 2014/11/19(水) 13:14


 追記
 >この後に元々番号、会社名が入力されているセルを削除したいのですが、
 >自分の作ったものだと削除したいセルを基準セルとしているため、削除がうまくできないです。 
 これの意味が分からなかったので何もしていません・・・

(稲葉) 2014/11/19(水) 13:15


稲葉様

出来ました!
しかもスパッと!!
ありがとうございます。

ただ教えて頂いたものが私の理解力ではなかなか理解できない状況です・・・。
配列を使っていることはわかるのですがRedimやUBoundがよくわかりません。
特に
Dim res: ReDim res(1 To UBound(a, 1), 1 To UBound(a, 2))
はどういう意味なのでしょうか。
恥ずかしながら dim の後ろに:つくものを見たことがありませんでした。
お時間のあるときにでも、少し解説していただけないでしょうか。
ずうずうしいお願いをしているのは承知していますが、宜しくお願い致します。
(ららら) 2014/11/19(水) 15:05


 >dim の後ろに:つくものを見たことがありませんでした。 
 dimに限らず、:(コロン)をつけると、改行(といっていいのか)したとみなされます。
 割とググればわかるもんですよ。
http://www.officepro.jp/excelvba/basic/index2.html

 Constで宣言したいけど、変数の中身に別の変数を使いたいときや、
 オブジェクトを入れる時に、あとで実態入れるよりその場で入れれば見やすいかな、というだけの話です。

 どういう場面で使うのかというと、私はエラー処理で複数行だと読みにくくなってしまいそうなときに
 使っています。
 test1〜3はすべて同じ分岐をしています。
 test1は、if文に忠実に従って記述しています。
 これをtest2のように1行に記述するときにコロンを使ったりします。
 またはtest3のように、変数msgにデータを入れて、その結果で分岐させたりします。

    Sub test1()
        If Range("A1").Value > 0 Then
            MsgBox "A1は1以上の値を入れてください"
            Exit Sub
        End If
        If Not IsDate(Range("B1").Value) Then
            MsgBox "B1は日付を入力してください"
            Exit Sub
        End If
        If Not Range("C1").Value Like "第*" Then
            MsgBox "C1は「第」から始まる文字を入力してください。"
            Exit Sub
        End If
        '実際の処理の記述
    End Sub

    Sub test2()
        If Range("A1").Value > 0 Then:            MsgBox "A1は1以上の値を入れてください": Exit Sub
        If Not IsDate(Range("B1").Value) Then:    MsgBox "B1は日付を入力してください": Exit Sub
        If Not Range("C1").Value Like "第*" Then: MsgBox "C1は第○○と入力してください。": Exit Sub
        '実際の処理の記述
    End Sub

    Sub test3()
        Dim msg As String
        If Range("A1").Value > 0 Then msg = "A1は1以上の値を入れてください" & vbNewLine
        If Not IsDate(Range("B1").Value) Then msg = msg & "B1は日付を入力してください" & vbNewLine
        If Not Range("C1").Value Like "第*" Then MsgBox "C1は第○○と入力してください。" & vbNewLine
        If msg = "" Then
            '実際の処理の記述
        Else
            MsgBox msg
        End If
    End Sub

 人によって違いますが、覚えておけば諸先生方の記述を読む手助けにはなると思います!

 で、ReDim res(1 To UBound(a, 1), 1 To UBound(a, 2)) についてです。
 ReDimは配列の要素数を変更するときに使用します。
 今回は2万件「程度」なので、決まった要素数がわかりません。
 (多めに見て3万を静的にしてもいいですが)
 ですので、データのサイズがわかった時点で要素数を入れてあげます。
http://www.excel-wing.com/study/tips/1100

 Uboundは要素の最後の添え字を返します。(リンクはVB6の解説ですが、同じなので・・・)
http://note.phyllo.net/?eid=1106081

 注意する点は
 Dim a(0 To 5)
 Debug.Print Ubound(a)
 とすると、5が表示されます。

 Dim a(1 To 5)
 Debug.Print Ubound(a)
 これでも5が表示されます。

 最初の始まりは指定できるため固定ではありません、0からの場合はUbound()+1が要素の数になります
    Sub test4()
        Dim a
        a = Array("りんご", "みかん", "ばなな", "きうい", "とまと")
        MsgBox "種類は" & UBound(a) & "種類です"
    End Sub
 中身は5個なのに、4個って言われたら困りますよね!

 もう少し実践向きな例では
    Sub test5()
        Dim dic As Object: Set dic = CreateObject("Scripting.Dictionary")
        Dim a
        dic.Add "りんご", ""
        dic.Add "みかん", ""
        dic.Add "ばなな", ""
        dic.Add "きうい", ""
        dic.Add "とまと", ""
        a = dic.keys
        MsgBox "dicでは" & dic.Count & "種類だけど" & vbNewLine & _
               "aでは" & UBound(a) & "種類"
    End Sub

 とこんな具合になってしまうので注意が必要です。
 あくまで「最後の添え字がいくつになるか」で、頭の数値はいくつからでもスタートできてしまうので
 注意してください。
(稲葉) 2014/11/20(木) 00:27

稲葉様

遅い時間にも関わらずアドバイスしていただきありがとうございます。
リンクも貼ってあり、かつ説明もわかりやすく、わからなかったコードが何となくですがわかってきました。
ただまだ勉強不足の為、完全に理解できていません・・・。
あと少し自分で調べてから、わからない箇所があればまた質問させて頂きたいです。
取り急ぎお礼をさせて頂きたかったのでコメントしました。
ありがとうございました。
(ららら) 2014/11/20(木) 14:59


コロンで区切って、1行に複数の記述を連結することを、マルチステートメントと言います。
これは、初期のBasic言語等でメモリが貧弱だった時代に、行番号の数バイトすら削る目的で使われました。
現在ではメモリの制限はほとんどなくなっていますが、VBAでは残されている機能のひとつですね。

似たパターンの連続する複数の処理を1行で表現、のように、コードを見やすくする目的で今でも使用される場合がありますが、
コロンより後ろの処理を見落とす等、デバッグの妨げになる場合が多いので、多用しないほうが良いテクニックになります。

稲葉さんの使用例では、C言語のように、変数宣言と同時に値を代入する感じで使用していますね。
これを真似して、なんでもかんでもマルチステートメントでまとめてしまうと、逆に見にくくなりますので、注意。
Go To 文は使うな、というのと同じかんじで、マルチステートメントは使わないことをお薦めします。

ちなみに、私はイミディエイトウィンドウで、デバッグ時に配列内容を一気表示する場合に使ったりします。
たとえば以下のようにイミディエイトウィンドウで記述し、ENTER。

for z=0 to 9:? z & " ";:next

(???) 2014/11/20(木) 15:29


 ???さん
 >Go To 文は使うな、というのと同じかんじで、マルチステートメントは使わないことをお薦めします。 
 多言語や存在理由について全く存じて居りませんでした。
 極力使わないよう、今後参考にさせていただきます。
 ありがとうございました。
(稲葉) 2014/11/20(木) 15:51

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.