Delete blank rows

[ratman]

I know VBA in Excel but am a total beginner with Access. A good portion of my VBA programs start with the parsing or opening of a text file in Excel and saving it as a .xls Excel file.

My problem is this huge 8meg 85,000 row text file(many blank rows) will not fit in the 65,000 max Excel rows so it truncates. I guess I need the VBA code so that in my Excel macro I could open an instance of Access then open the huge text file, then delete the blank rows, then save it as an Excel .xls file.

Once it fits in Excel I will be all set.
Any help would be greatly appreciated.

ratman

 

[Dreamboat]

Ratman:

Even better, though you may have thunk it for yourself, is to find a criteria that might pull in 1/2 your records. Is there no criteria you could use that would break it down into, say 3 or 4 cycles?

Also, maybe pull it into Word, delete the blank rows and then open it in Excel?

Is there no way for you to tell it you don't want the blank rows? Checked that, too, huh? Ok.

Good luck.
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[ratman]

hmm.....good thought....I VBA alot but have no clue how to either select only half of the file while in Word or better yet delete the blank rows while in Word......(these blank rows which are not only unnecessary but when they are removed the file will then fit into Excel)

thank you very much for your advise



Dave ratman Rattigan

 

[Dreamboat]

Well, in Word, you could select all of the data (but not the last paragraph return at the bottom--only complete lines of data), hit Table-Convert Text to Table. Then use Table-Sort and sort by one of the columns that you know will be blank if the whole row is blank. (I don't recall if blanks are thrown to the top or bottom in Word.) Copy non-blanks, paste into Excel directly if you want. Or! Delete blank rows, save as TXT file, and open from Excel using Text Import Wizard.

Am I confusing you, Dave? I'm confusing me!
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com

 

[ratman]

I think I'm close at getting the VBA in Excel macro to open the text file in Word, delete the blank rows, save as a text file......them I'm golden because I can fully take it from there due to the fact that the text file will fit into Excel's 65,000 row max.

thx Dreamboat

ratman

 

[Dreamboat]

You let me know if you need any other Word info, Dave. Or if you need to test...
techsupportgirl@home.com

http://www.WeDoManuals.com

Brainbench MVP for Microsoft Word at

http://www.brainbench.com