Curious, .SaveAs with xlText ignores initial empty rows

In some cell:
= COUNT(A1:Z8)

I haven't tried it, but I assume any REFERENCED cells will be saved.

(I am so pissed. I typed a long response WITHOUT doing it in Notepad and the board lost it, so I'm reconstructing something I cobbled together and tweaked and tweaked and tweaked over a 15 minute period. "Token has expired" issues.)

That does appear to be an effective workaround for the first problem of empty leftmost columns and/or uppermost rows. Jamming ="" into A1 doesn't produce anything but delimiters in the output file, which is good.

As to "interior" empty rows, this process could also be effective. With row 5 empty, jamming ="" into A5 causes the normal delimiters to appear on the output for row 5 (N-1 of them). Unfortunately I'd have to walk the whole sheet to do this, and cleanup (.ClearContents or .Clear) would be a mess to manage. (Actually, since the file is saved, I suppose that I could just close the newly created .TXT and not worry about cleanup.)

It seems ambitious but here's a thought: goto specialcells of blanks
selection.specialcells(xlCellTypeBlanks).select
and For Each rng in Selection, change every one to be ="", do the save, and if necessary, change them all back. One theoretical downside would be if there was a preexisting cell that actually had the content ="", it would be erroneously cleared in the cleanup.

It seems that would produce a file with the correct number of rows, and N-1 delimiters on each row.

That was the same as my previous idea before I have edited the code...

Please Login or Register  to view this content.

Very nice, and thank you - again. It's all sensible code and works.

A couple of general technical questions follow. (Other coding zen masters feel free to pitch in )
(Note for anyone reading along, xlBlanks=4; xlByRows=1, xlByColumns=2)

1. Why .Copy?

2. Why did you choose to use .Range("a1", .Cells(LR, LC)) to get to the xlBlanks instead of
selection.specialcells(xlCellTypeBlanks) or [a1].specialcells(xlCellTypeBlanks) or activecell.specialcells(xlCellTypeBlanks) ?

3. What is the benefit of using Find(s) to locate last occupied cell as opposed to .UsedRange.rows.count and .UsedRange.columns.count, or as opposed to xlLastCell(after "correcting it" by issuing a .UsedRange first)? (A general question that I'm going to be refreshing myself on right after I post this. I'm about to google it as I post this)

4. I might reverse the sequence of the two finds so that SearchOrder ends up at By Rows which I normally keep it at. Actually it would be correct to save it off and restore it. So this is another general question that came to mind.

For LookIn, LookAt, SearchOrder, and MatchByte (the ones that I understand are set and saved every time you change them),
a. Can I inquire what the existing (prior) values are? I.e. can I ask Excel what SearchOrder was saved off before I began execution?
b. Once I've done my finds, is the way to set them back to run a meaningless search, just to restore the parameters; or can I set them directly?

(Maybe AMOQ, I think that they're stored in Application.FindFormat, and I can just save them off into int variables from there, and restore to there)

5. I switch around between some of these, almost whimsically: cells(1), cells(1,1), [a1], range("a1"). It seems that you vary among those too. When I have freedom to choose from them, my "rule of thumb" is probably to choose whichever is most visually compact, though if I already have (of can cheaply get) a numeric column I'm inclined to use that instead of an alphabetic letter (that is, I avoid quote marks on the assumption that they cost time to dereference). I wonder if you have a more practical or efficiency-driven point of view.

TIA

1) Nobody knows the situation that you may have multiple worksheets in the workbook.

2, 3) Alternatively Cells.SpecialCells(xlCellTypleLastCell) might be used, however, it would refers to the cell that you don't want.
Last cell via SpecialCells will refer to the cell that have been ever changed before save, including format.
So, find method would give you the last row/column that have actual data.

4)
a) it holds the setting last changed.
b) it is always better to set them back to default for next use.

5) I have no rule about it, only choose whichever suits to the situation.

HTH.

Yes, those are helpful explanations. Thank you for your help on this.