こんにちは！大阪市住之江区に拠点を置く会社『縁紡ぐ』の稲垣です。
当社は、Excel、ACCESS、RPAなどのシステム開発や既存ツールを使った業務効率化の提案、また、ITスキルアップのための教育に力を入れています。効率的なビジネス運営を目指している企業様、ITスキルの向上を図りたい企業や個人の方に、最適なご提案をさせていただきます。業務プロセスの改善とITスキルアップをサポートし、共に成長するパートナーでありたいと考えています。
Excelファイルのサイズを劇的に削減！VBAマクロで不要なデータを一掃する方法
Excelファイルを使っていると、次第にファイルサイズが大きくなり、動作が重くなってしまうことがあります。特に大量のデータや複雑なシート構成を持つファイルでは、その傾向が顕著です。今回は、そんな悩みを解決するためのVBAマクロを作成したので紹介します。このマクロを使えば、ACCESSの最適化のように、Excelもファイルサイズを最適化できますよー！
このマクロが行っていること
ご紹介するマクロが行うことをご紹介します。
データが入力されている最後のセルを基準に、使われていない行や列を削除し、無駄なセルのデータを一掃します。
行や列の削除後に、シェイプ（図形）の位置がずれないように自動調整します。
コードの詳細解説
以下が実際のVBAコードです。（2024/8/22 最終使用列と行の判断を変更しました）
Sub ReduceExcelFileSize()
Dim ws As Worksheet
Dim lastUsedRow As Long
Dim lastUsedCol As Long
Dim shape As shape
Dim shapePositions As Collection
Dim usedRange As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each ws In ThisWorkbook.Worksheets
With ws
' 使用されている範囲を取得
Set usedRange = .UsedRange
lastUsedRow = usedRange.Rows(usedRange.Rows.count).Row
lastUsedCol = usedRange.Columns(usedRange.Columns.count).Column
' シェイプの位置情報を記録
Set shapePositions = New Collection
For Each shape In .Shapes
shapePositions.Add Array(shape.Name, shape.TopLeftCell.Row, shape.TopLeftCell.Column, shape.Left, shape.Top)
Next shape
' 使用されていない行の削除
If lastUsedRow < .Rows.count And lastUsedRow > 0 Then
.Rows(lastUsedRow + 1 & ":" & .Rows.count).Delete
End If
' 使用されていない列の削除
If lastUsedCol < .Columns.count And lastUsedCol > 0 Then
On Error Resume Next ' エラーを無視
.Columns(lastUsedCol + 1 & ":" & .Columns.count).Delete
On Error GoTo 0 ' エラーハンドリングを元に戻す
End If
' シェイプの位置を再配置
Dim pos As Variant
For Each pos In shapePositions
On Error Resume Next ' エラーを無視
If Not .Shapes(pos(0)) Is Nothing Then
With .Shapes(pos(0))
.Left = ws.Cells(pos(1), pos(2)).Left + (pos(3) - ws.Cells(pos(1), pos(2)).Left)
.Top = ws.Cells(pos(1), pos(2)).Top + (pos(4) - ws.Cells(pos(1), pos(2)).Top)
End With
End If
On Error GoTo 0 ' エラーハンドリングを元に戻す
Next pos
End With
Next ws
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox "不要なデータを削除してファイルサイズを削減しました。", vbInformation
End Sub
シート内の最終使用セルを特定
マクロは各シートで最終使用セルを特定し、そこから先にある不要な行や列を削除します。これにより、Excelの無駄なデータ領域が削除され、ファイルサイズが小さくなります。大量データの扱いをしていると、10分の１以下まで小さくなることがありまよ！
シェイプの位置情報を保持
削除後にシェイプが移動してしまわないよう、位置情報を事前に記録しておきます。削除作業が完了した後に、この情報をもとにシェイプを元の位置に再配置します。これは、テスト時に配置していたボタンがどっかに行ってしまい、気づいたことです。
パフォーマンスの最適化
処理中は画面の更新を止め、計算モードも手動に切り替えることで、処理速度を向上させています。最後に自動計算と画面更新を元に戻しています。
3実際に使用するには？
このVBAマクロを実際に使うには、以下の手順をお試しください。
Sub ReduceExcelFileSize()
Dim ws As Worksheet
Dim lastUsedRow As Long
Dim lastUsedCol As Long
Dim shape As shape
Dim shapePositions As Collection
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each ws In ThisWorkbook.Worksheets
With ws
' 最終使用セルの取得
On Error Resume Next ' 空のシートの場合にエラーを無視
lastUsedRow = .Cells.Find(What:="*", After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastUsedCol = .Cells.Find(What:="*", After:=.Cells(1, 1), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
On Error GoTo 0 ' エラーハンドリングを元に戻す
' シェイプの位置情報を記録
Set shapePositions = New Collection
For Each shape In .Shapes
shapePositions.Add Array(shape.Name, shape.TopLeftCell.Row, shape.TopLeftCell.Column, shape.Left, shape.Top)
Next shape
' 使用されていない行の削除
If lastUsedRow < .Rows.Count And lastUsedRow > 0 Then
.Rows(lastUsedRow + 1 & ":" & .Rows.Count).Delete
End If
' 使用されていない列の削除
If lastUsedCol < .Columns.Count And lastUsedCol > 0 Then
.Range(.Columns(lastUsedCol + 1), .Columns(.Columns.Count)).Delete
End If
' シェイプの位置を再配置
Dim pos As Variant
For Each pos In shapePositions
With .Shapes(pos(0))
.Left = ws.Cells(pos(1), pos(2)).Left + (pos(3) - ws.Cells(pos(1), pos(2)).Left)
.Top = ws.Cells(pos(1), pos(2)).Top + (pos(4) - ws.Cells(pos(1), pos(2)).Top)
End With
Next pos
End With
Next ws
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox "不要なデータを削除してファイルサイズを削減しました。", vbInformation
End Subマクロを実行する方法はいくつかあります。
・コード上にカーソルがある状態にしておき、F5で実行
・マクロの実行をする方法を下記の記事にあります
4. おわりに
このマクロは、Excelファイルのサイズを効果的に削減するためのお役にたつ方法です。日々の業務でExcelを多用する方には、ぜひお試しいただきたいと思います。もしこのマクロが便利だと感じたら、他の業務効率化ツールやカスタムマクロの開発やご相談、ご依頼していただければ幸いです。
稲垣
- Excel、ACCESSでのシステム開発が得意
- ITスキルを共有し実践的に学びながら成長する人を見るのが幸せ
- 自家焙煎するほどのコーヒー好き
- 使用言語 VBA、Python、Javascript、Java、HTML、CSS etc.
- 保有資格 Kintoneアソシエイト、日商簿記検定２級、マンション管理士、管理業務主任者、情報セキュリティマネジメント、ExcelVBA etc.
- 業務フロー図の作成や業務時間分析を通して、効率化ポイントを探る人
- お客様にとって本当に良いことかを第一に考える人
コメント