2

I have an Excel spreadsheet that generates CSV scripts used in an application. The scripts must be in a very specific format, and I save a master in XLSX format with protected sheets and data validation to save the CSVs from rather than directly edit the CSVs, as directly editing the CSVs can lead to mistakes.

The issue is that the scripts can be of nearly any length. The left column of each line can only be one of a certain set of values, and the last line has to say "END". The only way I can do this without VBA is the following formula in the A column, from row 7 (the first 6 are header information) to row 1048576 (last Excel row) and protect the sheet with column A locked:

   =IF(AND(ISBLANK(B368),NOT(ISBLANK(B367))),"END",IF(ISBLANK(B368),"",A367))

This makes the last row say "END" in column A, and all rows after blank, which is what is desired. The problem is that now when the CSV file is saved, it will always have 1048576 rows, with all the bottom rows containing the delimiters ",,,," . This won't work, the CSV file needs to stop after the "END" row. Is there a way to write the formula that will cause Excel to ignore the cells which evaluate to blank when saving to CSV or an alternate way to save to CSV in Excel that will ignore all the rows that evaluate to blank?

Note: I have a solution in VBA already that I can use on my own machine (it copies the data up to "END", pastes in a new sheet in text only format, then saves as CSV with the name of the original worksheet). I want to share this sheet, however, and getting around the security constraints to share macros at my company is a pain. So I'm looking for a way this might be done without Macros, if it's possible at all.

In looking for an answer I found this link, which is similar, but not the same: Saving Excel data as csv with VBA - removing blank rows at end of file to save

As the "blanks" I have are active rows because they contain formulas, this method will not work.

Community
  • 1
  • 1
Trashman
  • 1,424
  • 18
  • 27
  • As you have found, a zero length text cell is not the same as an empty cell (you can test the difference using the function `=ISBLANK()`). If macros are ruled out, why can't you just manually delete the cells in Col A after the "END" cell? You could even change the "" in your `=IF(..,"END",IF(..,"",..))` to something distinctive such as "@@@" or even "Delete Me" as an aid to memory. – DMM Jun 24 '15 at 17:54
  • It's currently what I do, but it would be easier on me and the people I share it with if there was something that required less manual intervention. It would also minimize mistakes such as forgetting to delete before saving and saving the whole sheet instead. Also, if we need to modify an already existing script to add more steps, it would be nice to not have to re-fill the formula. – Trashman Jun 24 '15 at 18:20

1 Answers1

0

Manually deleting the rows / columns will work to reset the size, as GSerg noted in the other question. Alternatively, also as suggested by GSserg, you can copy the data to a new sheet before saving.

Otherwise, an easy fix might be to create a small post-excel / pre-processing script - perhaps using a batch file - Batch / Find And Edit Lines in TXT file - or a similar solution in any small scripting language to remove the extra rows.

David Manheim
  • 2,553
  • 2
  • 27
  • 42