<div>
Can you provide a sample of a workbook that has examples of the data you need removed?<br />
<br />

<hr />
»<b><i>bp</i></b>
</div>


<div>
I have no idea how I can obfuscate the data and provide a workbook with macros that would come close to running.<br />
<br />
Everything seems to be so close....on the export to a .txt file, it randomly adds quotation marks. 48 of them as a matter of fact.<br />
<br />
And I was just wanting to copy all the data in Paste_Notepad to sheet1. I am currently specifying a range and its bringing over a few thousand extra line breaks. But I wanted to leave room for future growth.
</div>


wrt1mea

<div>
So are you saying there are text cells in the data sheet that have either single quotes in them, or double quotes in them, and you want to delete those characters from those cells?<br />
<br />

<hr />
»<b><i>bp</i></b>
</div>


<div>
OK, Bill...I will try that now. As far as my &quot;random&quot; quotes go...I think I have it traced down to a comma (,).<br />
<br />
For example when I have commas in a row of data in excel, it wraps the row in open and closed quotes when it exports to a txt file.<br />
<br />
Only lines that have commas...
</div>


<div>
Yes, that is desired behavior though, since comma is the field delimiter. &nbsp;If you don't allow that to happen then the output file will not align properly when used / processed.<br />
<br />

<hr />
»<b><i>bp</i></b>
</div>


<div>
If you want the commas in the data fields, then they will need to be quoted, or you would have to change to a different delimiter that can never be in the data.<br />
<br />
It depends on the usage of the exported data, what that program is expecting...<br />
<br />

<hr />
»<b><i>bp</i></b>
</div>


<div>
I tried the<br />
<br />
Sub COPY_PASTE()<br />
&nbsp; &nbsp; With Sheets(&quot;PASTE_NOTEPAD&quot;)<br />
&nbsp; &nbsp; &nbsp; &nbsp; .Range(&quot;A1:A&quot; &amp;&nbsp;.Cells(.Rows.Count, 1).End(xlUp).Row).Copy Destination:=Sheets(&quot;sheet<wbr />1&quot;).Range(<wbr />&quot;A1&quot;)<br />
&nbsp; &nbsp; End With<br />
End Sub<br />
<br />
Still bringing over 5000 lines
</div>


<div>
Then you must have data in column A for those 5000 rows. &nbsp;Is there another column that we should check for the end of data in?<br />
<br />

<hr />
»<b><i>bp</i></b>
</div>


<div>
This copies the last row on PASTE_NOTEPAD to the same row on Sheet1<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42808240-1"><span>Dim lngLastRow As Long</span>
<span></span>
<span>With Sheets(&quot;PASTE_NOTEPAD&quot;)</span>
<span>    lngLastRow = .Cells.Find(&quot;*&quot;, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row</span>
<span>    .Rows(lngLastRow).Copy Destination:=Sheets(&quot;sheet1&quot;).Rows(lngLastRow)</span>
<span>End With</span>
</code></pre>
</div>


<div>
All data is pasted into A1. I verified that there is no data in A1100 to A5000.
</div>


<div>
How do I use this with a sub?<br />
<br />
Dim lngLastRow As Long<br />
<br />
With Sheets(&quot;PASTE_NOTEPAD&quot;)<br />
&nbsp; &nbsp; lngLastRow = .Cells.Find(&quot;*&quot;, SearchOrder:=xlByRows, SearchDirection:=xlPreviou<wbr />s).Row<br />
&nbsp; &nbsp; .Rows(lngLastRow).Copy Destination:=Sheets(&quot;sheet<wbr />1&quot;).Rows(l<wbr />ngLastRow)<wbr /><br />
End With
</div>


<div>
If you want the last row to be pasted into A1 then<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42808257-1"><span>Dim lngLastRow As Long</span>
<span></span>
<span>With Sheets(&quot;PASTE_NOTEPAD&quot;)</span>
<span>    lngLastRow = .Cells.Find(&quot;*&quot;, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row</span>
<span>    .Rows(lngLastRow).Copy Destination:=Sheets(&quot;sheet1&quot;).Rows(&quot;1&quot;)</span>
<span>End With</span>
</code></pre>
</div>


<div>
In a sub.<br />

<pre><code class="code-10 nolinks" id="code-20-42808260-1"><span>Sub MySub()</span>
<span></span>
<span>Dim lngLastRow As Long</span>
<span></span>
<span>With Sheets(&quot;PASTE_NOTEPAD&quot;)</span>
<span>    lngLastRow = .Cells.Find(&quot;*&quot;, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row</span>
<span>    .Rows(lngLastRow).Copy Destination:=Sheets(&quot;sheet1&quot;).Rows(lngLastRow)</span>
<span>End With</span>
<span></span>
<span>End Sub</span>
</code></pre>
</div>


<div>
I need all of the data up to the last line from Paste_Notepad to get copied into Sheet1 starting at A1.
</div>


<div>
<blockquote>
<b><i>I verified that there is no data in A1100 to A5000</i></b>
</blockquote>
<br />
That can't be the case, there has to be something in column A.<br />
<br />
If you think there is nothing in the rows after a certain point, then how do you think / know they were copied to SHEET1, since there would be no data to show that in them?<br />
<br />

<hr />
»<b><i>bp</i></b>
</div>


<div>
Bill, I dont know why. I verified no data, highlighted and deleted and its still bringing over 5000 lines. Ideas on what to check?
</div>


<div>
<blockquote>
I need all of the data up to the last line from Paste_Notepad to get copied into Sheet1 starting at A1.
</blockquote>
<pre><code class="code-1 nolinks" id="code-20-42808272-1"><span>Sub MySub()</span>
<span>Dim lngLastRow As Long</span>
<span></span>
<span>With Sheets(&quot;PASTE_NOTEPAD&quot;)</span>
<span>    lngLastRow = .Cells.Find(&quot;*&quot;, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row</span>
<span>    .Rows(&quot;1:&quot; &amp; lngLastRow).Copy Destination:=Sheets(&quot;sheet1&quot;).Rows(&quot;1&quot;)</span>
<span>End With</span>
<span>End Sub</span>
</code></pre>
</div>


<div>
And you're sure you are running the updated macro, not the old 5000 row one?<br />
<br />
Well, you could try this, it will use column B as the reference for used rows rather than A, just to see what it does.<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42808280-1"><span>Sub COPY_PASTE()</span>
<span>    With Sheets(&quot;PASTE_NOTEPAD&quot;)</span>
<span>        .Range(&quot;A1:A&quot; &amp; .Cells(.Rows.Count, 2).End(xlUp).Row).Copy Destination:=Sheets(&quot;sheet1&quot;).Range(&quot;A1&quot;)</span>
<span>    End With</span>
<span>End Sub</span>
</code></pre>
<hr />
»<b><i>bp</i></b>
</div>


<div>
<blockquote>
That can't be the case, there has to be something in column A.
</blockquote>
Excel is not always accurate in its determination of the last row and I've found that <b>Cells.Find(&quot;*&quot;,SearchOrder<wbr />:=xlByRows<wbr />,SearchDir<wbr />ection:=xl<wbr />Previous).<wbr />Row</b> is the most reliable.
</div>


<div>
<blockquote>
nd you're sure you are running the updated macro, not the old 5000 row one?<br />
<br />
Well, you could try this, it will use column B as the reference for used rows rather than A, just to see what it does.
</blockquote>
<br />
I have checked and double checked. Using column b didnt bring over the data and the macro doesnt produce the updated information in the txt file.
</div>


<div>
I tried this from Martin Liss:<br />
<br />
Sub MySub()<br />
Dim lngLastRow As Long<br />
<br />
With Sheets(&quot;PASTE_NOTEPAD&quot;)<br />
&nbsp; &nbsp; lngLastRow = .Cells.Find(&quot;*&quot;, SearchOrder:=xlByRows, SearchDirection:=xlPreviou<wbr />s).Row<br />
&nbsp; &nbsp; .Rows(&quot;1:&quot; &amp;&nbsp;lngLastRow).Copy Destination:=Sheets(&quot;sheet<wbr />1&quot;).Rows(&quot;<wbr />1&quot;)<br />
End With<br />
End Sub<br />
<br />
It brought over the data, all 5001 rows....
</div>


<div>
There must be something in the cells all the way down to row 5000, even if it appears that they are empty.<br />
<br />
You could try going to what you think is the start of the &quot;empty&quot; rows, and selecting all rows from there to the end, and deleting them. &nbsp;Then see what the macro does.<br />
<br />

<hr />
»<b><i>bp</i></b>
</div>


<div>
<blockquote>
It brought over the data, all 5001 rows
</blockquote>
Is that what you wanted?
</div>


<div>
If it's not then try this<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-42808319-1"><span>Sub MySub()</span>
<span>Dim lngLastRow As Long</span>
<span></span>
<span>With Sheets(&quot;PASTE_NOTEPAD&quot;)</span>
<span>    For lngLastRow = 5500 To 1 Step -1</span>
<span>        If Not IsEmpty(.Cells(lngLastRow, &quot;A&quot;)) Then</span>
<span>            Exit For</span>
<span>        End If</span>
<span>    Next</span>
<span>    .Rows(&quot;1:&quot; &amp; lngLastRow).Copy Destination:=Sheets(&quot;sheet1&quot;).Rows(&quot;1&quot;)</span>
<span>End With</span>
<span>End Sub</span>
</code></pre>
</div>


<div>
I didnt want all 5001 rows...<br />
<br />
I tried what Bill mentioned....I highlighted the &quot;empty&quot; rows down past 5000 and deleted. AND IT WORKED!!!!!!!
</div>


<div>
Now, I need help to figure out how to exclude the quotation marks.<br />
<br />
What I am trying to do is use this for a template to build firewalls. So the quotations being added are a result I could really do with out.
</div>


<div>
YES Martin that also worked!!!!!!
</div>


<div>
I have no idea what your worksheet looks like, not even the headers, at this point, but what you might consider is adding an addition column to the right of your entries, and using string functions to concatenate and format the various columns you have entered into the useful &quot;line&quot; that you want output to a file. &nbsp;Then just save this &quot;calculated&quot; column to the file, rather than letting Excel save the various columns and do it's own formatting and delims, etc.<br />
<br />
It's a technique I often use for building large BAT scripts, or building test data, etc.<br />
<br />

<hr />
»<b><i>bp</i></b>
</div>


<div>
Martin, how would I incorporate your macro with this? This is the end of the macro I am currently using:<br />
<br />
Sub Save_as_text_file()<br />
Dim fPath As String, fname As String<br />
Dim ws As Worksheet<br />
Application.ScreenUpdating<wbr /> = False<br />
Application.DisplayAlerts = False<br />
Set ws = ActiveWorkbook.ActiveSheet<wbr /><br />
fname = Application.ActiveWorkbook<wbr />.Path &amp;&nbsp;&quot;\&quot; &amp;&nbsp;_<br />
&nbsp; &nbsp; &nbsp; &nbsp; ThisWorkbook.Worksheets(&quot;B<wbr />OB_BUILDER<wbr />&quot;).Range(&quot;<wbr />A2&quot;) &amp;&nbsp;&quot;_&quot; &amp;&nbsp;_<br />
&nbsp; &nbsp; &nbsp; &nbsp; ws.Name &amp;&nbsp;&quot;.txt&quot;<br />
fPath = Application.ActiveWorkbook<wbr />.Path<br />
ws.Copy<br />
ActiveWorkbook.SaveAs fname, xlUnicodeText<br />
Application.ActiveWorkbook<wbr />.Close<br />
Application.DisplayAlerts = True<br />
Application.ScreenUpdating<wbr /> = True<br />
End Sub
</div>


<div>
The problem isn't quotes in the data Martin (as I understand it), it's commas in the data, and when the file is saved Excel adds double quotes around those columns since comma if the field delim in the output file... &nbsp;Unless I read that all wrong, so hard working without seeing ay test examples...<br />
<br />

<hr />
»<b><i>bp</i></b>
</div>


<div>
If your quotes are the so-called &quot;Smart Quotes&quot; then you'll need to do it this way.<br />
<br />

<pre><code class="code-1 nolinks" id="code-20-42808369-1"><span>Sub MySub()</span>
<span>Dim cel As Range</span>
<span></span>
<span>For Each cel In Sheets(&quot;PASTE_NOTEPAD&quot;).UsedRange.Cells</span>
<span>    cel = Replace(cel, Chr(147), &quot;&quot;)</span>
<span>    cel = Replace(cel, Chr(148), &quot;&quot;)</span>
<span>Next</span>
<span></span>
<span>End Sub</span>
</code></pre>
</div>


<div>
OK, I am working on providing an example.....gonna be a few minutes.
</div>


<div>
If ws in your code is the &quot;PASTE_NOTEPAD&quot; sheet then<br />
<br />

<pre><code class="code-10 nolinks" id="code-20-42808380-1"><span>Sub Save_as_text_file()</span>
<span>Dim fPath As String, fname As String</span>
<span>Dim ws As Worksheet</span>
<span>Dim lngLastRow As Long</span>
<span></span>
<span>Application.ScreenUpdating = False</span>
<span>Application.DisplayAlerts = False</span>
<span>Set ws = ActiveWorkbook.ActiveSheet</span>
<span>With ws</span>
<span>    For lngLastRow = 5500 To 1 Step -1</span>
<span>        If Not IsEmpty(.Cells(lngLastRow, &quot;A&quot;)) Then</span>
<span>            Exit For</span>
<span>        End If</span>
<span>    Next</span>
<span>    .Rows(&quot;1:&quot; &amp; lngLastRow).Copy Destination:=Sheets(&quot;sheet1&quot;).Rows(&quot;1&quot;)</span>
<span>End With</span>
<span>fname = Application.ActiveWorkbook.Path &amp; &quot;\&quot; &amp; _</span>
<span>        ThisWorkbook.Worksheets(&quot;BOB_BUILDER&quot;).Range(&quot;A2&quot;) &amp; &quot;_&quot; &amp; _</span>
<span>        ws.Name &amp; &quot;.txt&quot;</span>
<span>fPath = Application.ActiveWorkbook.Path</span>
<span>ws.Copy</span>
<span>ActiveWorkbook.SaveAs fname, xlUnicodeText</span>
<span>Application.ActiveWorkbook.Close</span>
<span>Application.DisplayAlerts = True</span>
<span>Application.ScreenUpdating = True</span>
<span>End Sub</span>
</code></pre>
</div>


<div>
Martin, it still wrapped the lines with commas in quotes....<br />
<br />
I am working on a workbook that has data that I can provide as an example. Gonna take me a while to get it prepped.
</div>


<div>
Thinking out loud here...<br />
<br />
What if AFTER the creation and export of the filename.txt that another sub routine opens the filename.txt and removes the quotation marks, saves and closes???? That an option?
</div>


<div>
But then you will have more commas in the data than there are fields. &nbsp;How is that file used?<br />
<br />

<hr />
»<b><i>bp</i></b>
</div>


<div>
Can you show a picture of what some of the commas in quotes look like?
</div>


<div>
OK gentlemen. Please see the attached. Use the RUN_ALL macro in Sheet1. It will copy and paste data into the sheet and then export it into a text file in the same directory where you have the workbook. Take a look at line 11 in the text file. You will see it inserts the quotes any where the line has a comma.<br />
<br />
Thanks for all of the peristence. I am leaving to get on the road for a couple of hours and will be out of the office tomorrow.<br />
<a href="https://filedb.experts-exchange.com/incoming/2019/02_w09/1413463/ExpEch-Example.xlsm" target="_blank" class="file-inline" title="Working Example (33 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>ExpEch-Example.xlsm</a><br />
<a href="https://filedb.experts-exchange.com/incoming/2019/02_w09/1413464/NAME-987_SHEET1.txt" target="_blank" class="file-inline" title="txt file output (6 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>NAME-987_SHEET1.txt</a>
</div>


NAME-987_SHEET1.txt

ExpEch-Example.xlsm

<div>
Okay, I understand a bit better.<br />
<br />
But I don't think there is an easy way to prevent that in Excel saveing the file, all the CSV delimited formats will wrap in quotes if the field contains a comma. &nbsp;A couple of thoughts.<br />
<br />
(1) Could you use a different character in your data beside comma, like maybe semicolon?<br />
<br />
(2) Create a formatted column as I explained, and then only save that to text file.<br />
<br />
(3) Write a more complicated macro that loops through all the data and does it's own formatting of the output lines and writes them to the file. &nbsp;This would replace the SaveAs that you use now and would take a bit more code but is doable.<br />
<br />

<hr />
»<b><i>bp</i></b>
</div>


<div>
Bill,<br />
<br />

<blockquote>
I have no idea what your worksheet looks like, not even the headers, at this point, but what you might consider is adding an addition column to the right of your entries, and using string functions to concatenate and format the various columns you have entered into the useful &quot;line&quot; that you want output to a file. &nbsp;Then just save this &quot;calculated&quot; column to the file, rather than letting Excel save the various columns and do it's own formatting and delims, etc.<br />
<br />
It's a technique I often use for building large BAT scripts, or building test data, etc.<br />

</blockquote>
<br />
Can you expand upon this?
</div>


<div>
Bill, disregard that last comment. I am able to amend the original data to exclude commas.
</div>


<div>
You guys are awesome!<br />
<br />
More q's to come...
</div>


<div>
I’m glad I was able to help.<br />
<br />
If you expand the “Full Biography” section of <a href="https://www.experts-exchange.com/M_258447.html" rel="ugc">my profile</a> you’ll find links to some articles I’ve written that may interest you.<br />
<br />
Marty - Microsoft MVP 2009 to 2017<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Experts Exchange Most Valuable Expert (MVE) 2015, 2017<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Experts Exchange Top Expert Visual Basic Classic 2012 to 2018<br />
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Experts Exchange Top Expert VBA 2018
</div>


<div>
<div class="content wysiwyg-content">
I need to update the following macro to trim out any open or closed quotation marks. When I am running this I am getting some quotation marks thats being entered into the .txt file. Also, is there a way to remove any blank lines at the end of the sheet??? I dont need it exporting a bunch of spaces or lines...<br />
<br />
Sub Save_as_text_file()<br />
Dim fPath As String, fname As String<br />
Dim ws As Worksheet<br />
Application.ScreenUpdating<wbr /> = False<br />
Application.DisplayAlerts = False<br />
Set ws = ActiveWorkbook.ActiveSheet<wbr /><br />
fname = Application.ActiveWorkbook<wbr />.Path &amp;&nbsp;&quot;\&quot; &amp;&nbsp;_<br />
&nbsp; &nbsp; &nbsp; &nbsp; ThisWorkbook.Worksheets(&quot;B<wbr />OB_BUILDER<wbr />&quot;).Range(&quot;<wbr />A2&quot;) &amp;&nbsp;&quot;_&quot; &amp;&nbsp;_<br />
&nbsp; &nbsp; &nbsp; &nbsp; ws.Name &amp;&nbsp;&quot;.txt&quot;<br />
fPath = Application.ActiveWorkbook<wbr />.Path<br />
ws.Copy<br />
ActiveWorkbook.SaveAs fname, xlUnicodeText<br />
Application.ActiveWorkbook<wbr />.Close<br />
Application.DisplayAlerts = True<br />
Application.ScreenUpdating<wbr /> = True<br />
End Sub<br />
<br />
<br />
Or maybe modifying this would be easier? I set the range to copy from 1:5000. Just really needs to copy the last line of data from &quot;PASTE_NOTEPAD&quot;<br />
<br />
Sub COPY_PASTE()<br />
&nbsp; &nbsp; Sheets(&quot;PASTE_NOTEPAD&quot;).Ra<wbr />nge(&quot;A1:A5<wbr />000&quot;).Copy<wbr /> Destination:=Sheets(&quot;sheet<wbr />1&quot;).Range(<wbr />&quot;A1:A5000&quot;<wbr />)<br />
End Sub
</div>
</div>


I need to update the following macro to trim out any open or closed quotation marks. When I am running this I am getting some quotation marks thats being entered into the .txt file. Also, is there a way to remove any blank lines at the end of the sheet??? I dont need it exporting a bunch of spaces or lines...

Sub Save_as_text_file()
Dim fPath As String, fname As String
Dim ws As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set ws = ActiveWorkbook.ActiveSheet
fname = Application.ActiveWorkbook.Path & "\" & _
        ThisWorkbook.Worksheets("BOB_BUILDER").Range("A2") & "_" & _
        ws.Name & ".txt"
fPath = Application.ActiveWorkbook.Path
ws.Copy
ActiveWorkbook.SaveAs fname, xlUnicodeText
Application.ActiveWorkbook.Close
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub


Or maybe modifying this would be easier? I set the range to copy from 1:5000. Just really needs to copy the last line of data from "PASTE_NOTEPAD"

Sub COPY_PASTE()
    Sheets("PASTE_NOTEPAD").Range("A1:A5000").Copy Destination:=Sheets("sheet1").Range("A1:A5000")
End Sub

Update a macro

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.