エクセルマクロでcsvファイル出力する際、空白行を出力しないようにしたい。

Question

エクセルのシートをcsvファイルに出力する際、値の入った500行程度だけを出力したいです。
しかし、値の入ってない8000行が出力されてしまいます。
どうすれば良いでしょうか？

出力する手順は以下の通りです。
Sheet2のA列にはあらかじめ8000行まで文字列連結の計算式が入っている。
ボタンを押すとSheet1のA列とB列をSheet2のB列とC列にコピペする。Sheet2のA列はB列とC列の文字列を連結した文字列になる。続けて自動的にSheet2のA列をコピーしてSheet3に値として貼り付ける。続けて自動的にSheet3をcsvファイルとして出力する。すると、Sheet3の500～8000行は空なのに、csvファイルは8000行まで出力されてしまう。
Sheet1のA列には500行程度しか値は入っていない、将来、拡張したときのためにSheet2のA列には8000行まで計算式を入れてある。

よろしくお願い致します。

確認用にやっていることを簡略化したソースコードを掲載します。

Sheet1に入ってるデータです。

Sheet2に入ってる文字列を連結する計算式です。（A1～A10に入ってます。）

マクロのソースコードです。

Sub Macro1()
    Columns("A:B").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Columns("B:B").Select
    ActiveSheet.Paste
    Columns("A:A").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("C14:D16").Select
    Sheets("Sheet3").Select
    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Sheet1").Select
    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet3").Select
    Columns("B:B").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.SaveAs Filename:="C:\test\Sheet3.csv", FileFormat:=xlCSV, _
        CreateBackup:=False
    ActiveWorkbook.SaveAs Filename:="C:\test\test.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub

出力されたcsvファイルの中身です。
（1～5行まで出力したいのに10行出力されてしまう。）

a1b1,c1
a2b2,c2
a3b3,c3
a4b4,c4
a5b5,c5
,
,
,
,
,

Comments

[退会済みユーザー]

VBAのソースを貼ってください。

[退会済みユーザー]

業務のソースコードなのでそのままでは貼れません。
明日、変数名とかを変えて貼ります。

[退会済みユーザー]

ソースを貼りました。

[退会済みユーザー]

このマクロはvbaとして書いたものですか？マクロの記録で作成したものですか？

[退会済みユーザー]

ほぼマクロの記録です。

[退会済みユーザー]

セルの範囲 指定 等で調べましたか？

[退会済みユーザー]

範囲を指定するにも空白行かどうかを瞬時に調べる方法がわかりません。
１行ずつ調べてたら処理が遅くなります。

[Usirow]

ご自分で書いて検証しましたか？
このコードに合わせるなら、8000行を全部見ていってもあってないようなレベルの時間しかかからないはずです。

Answer 1

コピー元のセルを指定する際に、
Columnsで列全体を指定するのではなく、
ActiveSheet.Range()を使って表示されているセルを指定すれば良いです。

空白かどうかを1行ずつ調べると遅くなるとのことですが、
実際の業務のマクロがどうなっているのかはわかりませんが、
質問にある例ですと、Sheet2の式の有無によらず、Sheet1のA列以上の行数をコピペする必要がないため
Sheet1のA列に対して空白かどうかを検査すれば、コピペ範囲は確定するので、
いうほど遅くはならないと思います。

それでも遅いようでしたら、一度配列に入れれば処理速度は早くなります。
「VBA 高速化」で調べると速度計測まで行った解説ページが出てきます。
http://officetanaka.net/excel/vba/speed/

もしくは空白を調べずに使用しているセルの範囲を調べる方法もあります。

これも「VBA セル範囲」調べると良い解説ページが出てきます。
http://officetanaka.net/excel/vba/cell/cell10.htm

使えそうな機能を抜粋すると以下のようなものがあります。

• 使用しているセル範囲をジャンプするキーボード操作(Ctrl + ↓)に相当する処理でA1から下に移動する。

ActiveSheet.Range("A1").End(xlDown).Address()

• UsedRangeを使用する

Dim cellAddress As String
cellAddress = "A1:A" & ActiveSheet.UsedRange().Rows().count()

Answer 2

どうせVBAを使っているですから、最初からセルに計算式を入れずに、
計算させた結果のみをセルに入れてはどうでしょうか？

どうしても、セルに式を入れたままがいいのでしたら、
CSV出力の前に、値が入っている最大の行数を取得し、
その行数分だけデータを抽出、CSVに出力してはいかがでしょう？

Comments

[退会済みユーザー]

空白行かどうかを瞬時に調べる方法がわかりません。１行ずつ調べてたら処理が遅くなります。

[yo_u]

https://tonari-it.com/rows-count-hidden/#toc2
これとかはどうでしょう？

「VBA　データ数　取得」とか検索すればすぐに出てきます。
検索くらいはしてから聞いたほうがよいかと思います。