Ta bort tom rad i textfil - VBA - Forum Excel, VBA, VSTO, Exceltips, Excelhjälp

Sök Forumets startsida

Forum

Diskussioner

VBA

Ta bort tom rad i textfil

Föregående

Nästa

2017-01-03 14:36

svindlarn86

Medlem sedan: 2017-01-03

1 inlägg

Ta bort tom rad i textfil

Ändrad av svindlarn86 på 2017-01-03 15:37:14

I am using a macro to copy a range of cells from an Excel worksheet to a new text file. Each row in the Excel file genereates a row in the textfile.The last row of the text file is empty and I can't remove this. The code looks like this: Dim r As Long Dim arr As Variant Dim file As Variant Set file = CreateObject("Scripting.FileSystemObject").CreateTextFile("c:\temp\returförslag" + " test.exp", True, True) With ActiveSheet arr = .Range(.[A1], .UsedRange.Cells(.UsedRange.Cells.Count)) For r = 1 To UBound(arr) strfile = Join(WorksheetFunction.Index(arr, r), vbTab) strfile = Replace(strfile, Chr(13), "") strfile = Replace(strfile, Chr(10), "") strfile = Replace(strfile, vbcrlf"") 'file.WriteLine Join(WorksheetFunction.Index(arr, r), vbTab) file.WriteLine strfile Next End With I have tried to use the replace function to remove the carriage return but unfortunately with no success. Can you please help me out with this annoying problem?

2017-01-09 12:16

Christian

Medlem sedan: 2015-08-26

1209 inlägg

Re: Ta bort tom rad i textfil

You probably need to handle the last loop differently.. So after initiaing the loop:

If r = ubound(arr)

Exit for

End If

(This little snippet will exit the loop at the last iteration, thus preventing the last line to be generated). If you notice that you are missing last row of data, then you will need file.writeline join(worksheetfunction.indext(arr, r), vbTab) within the IF, before the exit for.

Best regards

Chris

Sida 1 av 1

Ta bort tom rad i textfil