Macro Formatting a csv file

Question

I am using a macro to generate a file with 3 columns ( A,B,C) and save it to a csv. I have everything working fine except when I save it. The file saves correct but it saves all Lines in columns after the data with a " " " in the rows of each line. I need to clean this up. The ending line will different everyday, and I need to delete from the first open line to end of workbook.

I tried this:

Selection.End(xlDown).Select
Range("A100:C100").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Delete Shift:=xlUp

But the cell reference will change everyday, so I tried:

Selection.End(xlDown).Select
Range("A:C").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Delete Shift:=xlUp

and now it deletes all data. Any help will be great Below is a example of my data and how it looks. I need to get rid of all the " " " and these only show up when I view it in notepad, but the program I use looks at the " " " as data

 6/27/2012 0:00,aaaa,-0.011111
 6/27/2012 0:00,bbbb,-0.22222
 6/27/2012 0:00,cccc,-0.03333
 6/27/2012 0:00,ddddd,-0.0044
 6/27/2012 0:00,xxxxxx,-0.0555
 ' '
 ' '
 ' '
 ' '
 ' '

Can you post a sample workbook? Or a screenshot of the data? Your description is a bit confusing. — Scott Holtzman
– Scott Holtzman, Commented Jun 28, 2012 at 13:44
To help clarify The ending line will different everyday, and I need to delete from the first open line to end of workbook. Why do you need to delete blank (open) rows, or is there data underneath a blank row that you want to eliminate? Also, can you be clearer on this The file saves correct but it saves all Lines in columns after the data with a " " " in the rows of each line.? — Scott Holtzman
– Scott Holtzman, Commented Jun 28, 2012 at 13:48
yes. Under the data I need when load this later in a notepad I have """ in all cells under the data I need. That is why I was deleting all of it, and that cleans it up. I need to get rid of all the """ in my notepad view the program I run looks at them as data. — Trying_hard
– Trying_hard, Commented Jun 28, 2012 at 13:59

Scott Holtzman · Accepted Answer · 2012-06-28 14:11:04Z

1

Try this code to clean out unnecessary lines with ""

Dim rng As Range
Set rng = Columns(1).Find("""")
Range(rng, rng.End(xlDown).End(xlToRight)).EntireRow.Delete

This will work so long as there is always more than 1 " in a line, if there is only 1, it gets a bit trickier.

answered Jun 28, 2012 at 14:11

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Trying_hard Over a year ago

I thought it was """ it is ' ', will this still work? Set rng = Columns(1).Find("""") I get this showing as a red error in my vba editor?

Trying_hard Over a year ago

also the ' ' isn't visible in excel, just in notepad.

Scott Holtzman Over a year ago

Copy the text from your file, and paste in between the outer quotes in Find("""") That way you get exactly what you need.

Scott Holtzman Over a year ago

Good. The tricky thing sometimes is to find exactly how to get at the data element that is screwing you up!

Collectives™ on Stack Overflow

Macro Formatting a csv file

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related