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

  • Thread starter Thread starter robzrob
  • Start date Start date
R

robzrob

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.
 
robzrob said:
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.
Click to expand...

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
 
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
Click to expand...


Wonderful. Thanks very much.
 
Back
Top