How do I prevent my macro from making commas at the end of my CSV file?

dbabbitt
dbabbitt used Ask the Experts™
on
Hi Guys!

I have an Excel 2000 subroutine that looks like this:


Sub RemoveZeroedRows()
     Dim oObject     As Variant
     Dim oObject1    As Variant
     Dim oObject2    As Variant
     Dim x           As Long
     Dim y           As Long
     Dim z           As Long
     
     y = 0
     
     'Assign the sheet's used range of cells to a variant array
     oObject = ActiveSheet.UsedRange.Value
     
     'Assign the sheet's used range of cell formulas to an array
     oObject1 = ActiveSheet.UsedRange.Formula
     
     'Redimension the array to store new cell values
     ReDim oObject2(UBound(oObject, 1), UBound(oObject, 2))
     
     'Step through the object array, and determine which values are assigned
     For x = 1 To UBound(oObject, 1)
          Select Case True
               'First row - assign to array
               Case x = 1
               y = y + 1
               For z = 1 To UBound(oObject, 2)
                    oObject2(y, z - 1) = oObject1(x, z)
               Next
               
               'No value assigned
               Case oObject(x, 4) = 0 And oObject(x, 5) = 0 And (IsEmpty(oObject(x, 9)) Or oObject(x, 9) = 0)
               
               'Assign values to new array
               Case Else
               y = y + 1
               For z = 1 To UBound(oObject, 2)
                    If z <> 6 Then
                         oObject2(y, z - 1) = oObject1(x, z)
                    Else
                         oObject2(y, z - 1) = "'" & oObject1(x, z)
                    End If
               Next
               
          End Select          
     Next
     
     'Reassign the new values to the sheet
     ActiveSheet.UsedRange.Formula = oObject2
     
     'Delete the first row
     ActiveSheet.Rows(1).Delete
     
     'Sort by file number
     ActiveSheet.UsedRange.Sort Key1:=Range("C2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
     
End Sub


The problem is, when you save the worksheet (that this subroutine has removed the zeroed rows from) as a CSV and then open it up in Notepad, you get a bunch of lines like this at the bottom:

    M3Q,EPIP1114,1513,7,0,000030,,,
    M3Q,EPIP1114,1514,40,18,000070
    ,,,,,
    ,,,,,
    ,,,,,
    ,,,,,
    ,,,,,
    ,,,,,

Note that the last data row is missing its commas, and all the rows after that are nothing BUT commas! When the software that this CSV file is imported into sees a line with nothing but commas, it generates an error. How do I prevent my macro from making commas at the end of my CSV file?

Thanx

Dave
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006

Commented:
Hello Dave,

don't know how you save but assume it's done from code ... would build in a check to find the first row with nothing but ,,,,, you could check for this by going to the last row in your used range, and loop up by the offset(-1,0) function as long as a is empty you know the row can be deleted or you shrink the counter for the actual rows in your exported range

don't know if this fits ... just an idea, since you got no comments yet

HAGD:O)Bruintje

Commented:
Dear expert(s),

A request has been made to delete this Q in CS:
http://www.experts-exchange.com/Community_Support/CleanUp/Q_20412237.html

Without a response in 72 hrs, a moderator will finalize this question by:

 - Saving this Q as a PAQ and refunding the points to the questionner

When you agree or disagree, please add a comment here.

Thank you.

modulo

Community Support Moderator
Experts Exchange
Commented:
Saving this Q as a PAQ and refunding the points to the questionner

modulo

Community Support Moderator
Experts Exchange

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial