CODEWITHSUNDEEP
excelvba
stokesster
stokesster

Reputation: 31

Eliminate Blank Rows When Creating .CSV File

I am attempting to create a macro to create .CSV file that contains the first to last row of data entered. When I run the Macro, blank rows are showing in the .CSV file. What code do I need to eliminate the blanks rows from being written to the file? Listed below is the VBA code and a snapshot of the data in the .CSV file. Any help would be greatly appreciated. 

' DeleteHeadings
Rows("1:1").Select
Selection.DeleteShift:=xlUp


'Find thelast Row with data in a Column
DimLastRow As Long
DimFirstRow As Integer

With ActiveSheet
LastRow =.Cells(.Rows.Count, "A").End(xlUp).Row
End With


FirstRow= 1


'SelectRows to Save
Range(Cells(FirstRow,"A"), Cells(LastRow, "BH")).Select

' SaveActive Workbook As CSV
Application.DisplayAlerts= False
ActiveWorkbook.SaveAsFilename:="Z:\RDS\Towers_Watson_Trans_File1", FileFormat:=xlCSV,CreateBackup:=False
ActiveWorkbook.CloseSaveChanges:=True
Application.DisplayAlerts= True

Data from .CSV file

444444444,,HILDA,,ZOLDAN,19490611,F,{92B32AFF-7A9C-4FB0-BC98-51CBA0F642CC},20150801,1800.00,M,20150801,,,,,,,,,,,,,,,,,,,,,915 
444444444,,MARY,,SHANAHAN,19501111,M,{92B32AFF-7A9C-4FB0-BC98-51CBA0F642CC},20151101,1080.00,M,20151101,,,,,, , , , , ,,,,,,,, 
444444444,,MARY,,SZCZESNIAK,19450923,F,{92B32AFF-7A9C-4FB0-BC98-51CBA0F642CC},20150801,1800.00,M,20150801,,,, , , , , , , ,,,, 
444444444,,DORIS,,HAMMONDS,19461214,F,{92B32AFF-7A9C-4FB0-BC98-51CBA0F642CC},20150801,1800.00,M,20150801,,,,,,,,,,,,,,,,,,,,,37 
666666666,,ROSA,,GANA,19530613,F,{92B32AFF-7A9C-4FB0-BC98-51CBA0F642CC},20180601,1080.00,M,20180601,,,,,,,,,,,,,,,,,,,,,71SACK 
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, 
,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,

Upvotes: 3

Views: 5905

Answers (1)

FreeMan
FreeMan

Reputation: 5687

.SaveAs will save the worksheet, not the selection (I don't see any options to save the selection instead of the whole worksheet). There are blank rows at the end of your data, so they're getting saved into the .CSV.

Try this to delete the blank rows at the end:

' DeleteHeadings
Rows("1:1").Select
Selection.DeleteShift:=xlUp

'Find thelast Row with data in a Column
Dim LastRow As Long
'Dim FirstRow As Integer       'No longer needed - at least for this snippet

LastRow = ActiveSheet.Cells(.Rows.Count, "A").End(xlUp).Row
Rows(LastRow + 1 & ":" & ActiveSheet.UsedRange.Rows.Count).Delete

'SelectRows to Save
'you can comment these 2 lines out vvvv  They're not buying you anything
FirstRow= 1
Range(Cells(FirstRow,"A"), Cells(LastRow, "BH")).Select

' SaveActive Workbook As CSV
Application.DisplayAlerts= False
ActiveWorkbook.SaveAsFilename:="Z:\RDS\Towers_Watson_Trans_File1", _
      FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=True
Application.DisplayAlerts= True

Upvotes: 3

Related Questions