Select and copy only cells with values, open an app, paste into
 

Hello

Writing a little macro, got partway, now I need this bit:

I've got a worksheet (one of many in the workbook) called Adjust with formulae in A1:J2000 but the formulae are such that only a certain number of rows have values in them and that will vary. So, for example, today only A1:J1082 have values in them, next week it could be A1:J1200 or A1:J1010 (the cells with values will always be in one 'block'). I want to copy all the rows (from column A to J) with values in them, open Notepad, paste the values into the Notepad file and Save As C:\...ABC.txt - but C:\...ABC.txt will already exist and I'll want to discard it and replace it with the new version.

Thanks.

Select and copy only cells with values, open an app, paste into
 

robzrob wrote:

Writing a little macro, got partway, now I need this bit:

I've got a worksheet (one of many in the workbook) called Adjust with
formulae in A1:J2000 but the formulae are such that only a certain
number of rows have values in them and that will vary. So, for example,
today only A1:J1082 have values in them, next week it could be A1:J1200
or A1:J1010 (the cells with values will always be in one 'block'). I
want to copy all the rows (from column A to J) with values in them, open
Notepad, paste the values into the Notepad file and Save As
C:\...ABC.txt - but C:\...ABC.txt will already exist and I'll want to
discard it and replace it with the new version.

No need to involve notepad; VBA can handle text files quite easily. This
stops with the first all-blank line:

Sub writeNonBlanksToText()
Const OUTPUTFILENAME = "C:\ABC.txt"
Dim cell As Range, ro As Range, outP As String
fnum = FreeFile
Open OUTPUTFILENAME For Output As fnum
For Each ro In Range("A1:J" & Cells.End(xlDown).Row).Rows
outP = ""
allBlank = True
For Each cell In ro.Cells
If cell.Column < 1 Then outP = outP & vbTab
outP = outP & cell.Value
If Len(Trim$(cell.Value)) Then allBlank = False
Next
If allBlank Then Exit For
Print #fnum, outP
Next
Close fnum
End Sub

--
What's a little aggravated assault amongst proper European scientists?

Select and copy only cells with values, open an app, paste into
 

On Monday, July 2, 2012 5:53:11 AM UTC+1, Auric__ wrote:
robzrob wrote:

Writing a little macro, got partway, now I need this bit:

I've got a worksheet (one of many in the workbook) called Adjust with
formulae in A1:J2000 but the formulae are such that only a certain
number of rows have values in them and that will vary. So, for example,
today only A1:J1082 have values in them, next week it could be A1:J1200
or A1:J1010 (the cells with values will always be in one 'block'). I
want to copy all the rows (from column A to J) with values in them, open
Notepad, paste the values into the Notepad file and Save As
C:\...ABC.txt - but C:\...ABC.txt will already exist and I'll want to
discard it and replace it with the new version.

No need to involve notepad; VBA can handle text files quite easily. This
stops with the first all-blank line:

Sub writeNonBlanksToText()
Const OUTPUTFILENAME = "C:\ABC.txt"
Dim cell As Range, ro As Range, outP As String
fnum = FreeFile
Open OUTPUTFILENAME For Output As fnum
For Each ro In Range("A1:J" & Cells.End(xlDown).Row).Rows
outP = ""
allBlank = True
For Each cell In ro.Cells
If cell.Column < 1 Then outP = outP & vbTab
outP = outP & cell.Value
If Len(Trim$(cell.Value)) Then allBlank = False
Next
If allBlank Then Exit For
Print #fnum, outP
Next
Close fnum
End Sub

--
What's a little aggravated assault amongst proper European scientists?


Wonderful. Thanks very much.