I am trying to set up a payroll spreadsheet for our HR department. We have
roughly 30 locations within the company. I created a template time sheet for
all 30 and I included about 10-20 blank columns for each company (for
growth). All of these spreadsheets are linked to a master sheet. The master
sheet is set to update whenever it is opened. The problem is that to be able
to import this document to the payroll company, it has to be a csv file
without any 0's. The problem I'm having is finding a way to make this master
sheet eliminate all the empty columns and eliminate 0's........and then save
in a csv format.............any suggestions?

Sorry all that does is save the file as CSV......I need a macro taht will
delete the rolls with 0's and then save as CSV...but its close :)

"MotoD" wrote:

I am trying to set up a payroll spreadsheet for our HR department. We have
roughly 30 locations within the company. I created a template time sheet for
all 30 and I included about 10-20 blank columns for each company (for
growth). All of these spreadsheets are linked to a master sheet. The master
sheet is set to update whenever it is opened. The problem is that to be able
to import this document to the payroll company, it has to be a csv file
without any 0's. The problem I'm having is finding a way to make this master
sheet eliminate all the empty columns and eliminate 0's........and then save
in a csv format.............any suggestions?

This will SOLVE your problem

Sub savecsv()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f

Const CSVfilename = "c:\temp\abc.csv"

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile CSVfilename
Set f = fs.GetFile(CSVfilename)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set ColARange = Range(Cells(1, "A"), Cells(LastRow, "A"))

For Each cell In ColARange
LastColumn = Cells(cell.Row, Columns.Count). _
End(xlToLeft).Column
Set RowRange = Range(Cells(cell.Row, "A"), _
Cells(cell.Row, LastColumn))

first = True
For Each ColCell In RowRange

If first = True Then
first = False
Else
ts.write ","
End If

If Not IsEmpty(ColCell) Then
ts.write ColCell.Value
End If
Next ColCell
ts.writeline
Next cell

ts.Close
End Sub



"MotoD" wrote:

I am trying to set up a payroll spreadsheet for our HR department. We have
roughly 30 locations within the company. I created a template time sheet for
all 30 and I included about 10-20 blank columns for each company (for
growth). All of these spreadsheets are linked to a master sheet. The master
sheet is set to update whenever it is opened. The problem is that to be able
to import this document to the payroll company, it has to be a csv file
without any 0's. The problem I'm having is finding a way to make this master
sheet eliminate all the empty columns and eliminate 0's........and then save
in a csv format.............any suggestions?

All that macro does is save the file as a csv. It doesn't eliminate the 0's :(

"Joel" wrote:

This will SOLVE your problem

Sub savecsv()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f

Const CSVfilename = "c:\temp\abc.csv"

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile CSVfilename
Set f = fs.GetFile(CSVfilename)
Set ts = f.OpenAsTextStream(ForWriting, TristateUseDefault)

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set ColARange = Range(Cells(1, "A"), Cells(LastRow, "A"))

For Each cell In ColARange
LastColumn = Cells(cell.Row, Columns.Count). _
End(xlToLeft).Column
Set RowRange = Range(Cells(cell.Row, "A"), _
Cells(cell.Row, LastColumn))

first = True
For Each ColCell In RowRange

If first = True Then
first = False
Else
ts.write ","
End If

If Not IsEmpty(ColCell) Then
ts.write ColCell.Value
End If
Next ColCell
ts.writeline
Next cell

ts.Close
End Sub



"MotoD" wrote:

I am trying to set up a payroll spreadsheet for our HR department. We have
roughly 30 locations within the company. I created a template time sheet for
all 30 and I included about 10-20 blank columns for each company (for
growth). All of these spreadsheets are linked to a master sheet. The master
sheet is set to update whenever it is opened. The problem is that to be able
to import this document to the payroll company, it has to be a csv file
without any 0's. The problem I'm having is finding a way to make this master
sheet eliminate all the empty columns and eliminate 0's........and then save
in a csv format.............any suggestions?