-2

I created a macro which basically does what I want. However, for some reason it always adds 10 additional empty lines in the CSV (shown as commas). When the end user offers the CSV to the external portal, this always creates 10 empty records. What should be removed or adjusted in the macro to avoid this?

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("\\chr-fss001\DataJapan\Sagawa\Sagawa_CSV\")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)

'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A1:DZ1" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate

'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close

Next

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
    Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,     1), Array(6, 1), _
    Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
    )), TrailingMinusNumbers:=True
ActiveSheet.Shapes.Range(Array("Button 1")).Select
Rows("1:1").Select
Selection.Delete Shift:=xlUp
Range("A1").Select
Range("I1").Select
Sheets("Sheet1").Select
Columns("I:L").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Blad1").Select
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Range("A1").Select
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(Lastrow, "A").Select
ActiveCell.Offset(1).Select
ActiveCell.EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range("A1").Select
ChDir "J:\Sagawa\DailyPortalFile"
ActiveWorkbook.SaveAs Filename:= _
    "J:\Sagawa\DailyPortalFile\" & Format(Now, "yyyy-mm-dd hh mm") & "       Sagawa", FileFormat:=xlCSV, _
    CreateBackup:=False



End Sub

CSV outcome with 10 lines as commas

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Eelco
  • 1
  • 1
  • 3
    Post the macro as copyable text and not as an image. Do you expect someone to type it back in to debug it? – Aganju May 16 '18 at 12:17
  • 1
    Please read and apply [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). – Pᴇʜ May 16 '18 at 13:05
  • Please learn identing your code, or use http://www.oaltd.co.uk/indenter/ !! – iDevlop May 16 '18 at 13:07

1 Answers1

0

It is really a good idea to refactor (rewrite) the whole code, following the ideas here: How to avoid using Select in Excel VBA.

However, for a very quick & ugly, very dirty solution, change this line:

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

to this:

Lastrow = Cells(Rows.Count, "A").End(xlUp).Row - 10

It will remove the last 10 rows, which are obviously not needed.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Unfortunately the -10 trick does not work. It will then give an error on the line below this one. – Eelco May 17 '18 at 09:53
  • Good to mention is that when we run the macro on an English server, it goes fine. The 10 lines are added when we run the macro on a Japanese server. Anyone any clue? – Eelco May 17 '18 at 09:53
  • It is not very clear to me what to adjust in regards to 'avoid using Select in Excel VBA'. – Eelco May 17 '18 at 09:54
  • @Eelco - read the [question and the answers here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Then start looking into your code for words like `Select` and `Activate` and try to rewrite the code, until these words are no more there. – Vityata May 17 '18 at 09:57