How to eliminate trailing empty row/columns

Question

I am using Microsoft Interop to convert excel files into csv files. I use sheet.SaveAs function.

My initial excel sheet has data from A1 to AZ columns for 100 rows. I need in the CSV just the data from A1 to AP and only for 50 rows.

Using the Range function, I delete the row51-100, I clear the contents for the same rows, still when I save as CSV, I find rows 51-100 as below: (just commas). I do not want to see these commas in CSV.

,,,,,,,,,,,,,,,,,,,,,,,,, ,,,,,,,,,,,,,,,,,,,,,,,,

The same for column AQ-AZ as well. I do not want these data in CSV. I delete, clear contents using Range function, yet these AQ-AZ columns appears in CSV files as “,,,,,,,,,,,,,,,,,,,,,” .

Is there a way to save XLS as CSV with only Range that I want to see in the CSV file. Is there a way to control the range that goes into CSV file?

In short, I want to see in CSV file just the data for column A1 to AP for 50 rows. No empty trailing “,”s. Is there a way?

Answer 1

The issue you are describing seems like a "Last Cell" issue. The last cell is the original end of your data, even after you delete rows/columns.

Here is what Microsoft has to say about it: How to reset the last cell in Excel

I seem to remember a programmatic way of doing this, but for the life of me, I cannot recall how.

Having looked at that info, maybe you could rethink how you can do this.

Perhaps you could just read the data you need and write it out yourself.

i.e. For each row in range, get the row as a value which will be an array of object, convert to array of string, string.join with the delimiter as a comma and append to a .csv file.