bskinn / excel-csvexporter

Lightweight tool to export ranges within an Excel sheet to CSV
MIT License
41 stars 7 forks source link

Handle idiosyncrasy of .UsedRange on empty sheet #26

Closed bskinn closed 4 years ago

bskinn commented 4 years ago

Instead of returning something relatively intuitive, like Nothing, when .UsedRange is called on an empty Worksheet, Excel 2019 instead returns Range("$A$1").

Thus, since the code tries to reduce the ExportRange to the bounds of .UsedRange when entire rows and/or columns are selected, if Selection encompasses $A$1 then it will erroneously set ExportRange to Range("$A$1"), instead of Nothing.

This commit fixes this behavior by running an explicit check for .UsedRange == Range("$A$1") and IsEmpty(.UsedRange). If the sheet is empty, then ExportRange will be set to the appropriate Nothing.