Is there a way to run this macro on different numbers of columns in different rows?-CodePudding

Sub pipey()
'Create a text file with each row cell delimited with "|"

Dim intUsedrows As Integer
Dim intUsedcolumns As Integer

Open "Y:\purchasing\Pipey.txt" For Output As #1

With Worksheets(1).Range("a:f")
intUsedrows = ActiveSheet.UsedRange.Rows.Count
intUsedcolumns = ActiveSheet.UsedRange.Columns.Count

For i = 1 To intUsedrows
  For j = 1 To intUsedcolumns - 1
   Print #1, .Cells(i, j); "|";
  Next j
   Print #1, .Cells(i, intUsedcolumns)
  Next i
End With
Close #1
MsgBox ("Done Successfully")
End Sub

The first row is always 12 columns, the last row is always 3 columns and the rows in between are always 9 columns. I'm tryin to avaoid having trailng delimiters at the end of the rows in the generated text file.

CodePudding user response：

Try putting the output into a string first, rather than directly into the text file. Then run a loop to eliminate trailing columns before outputting the entire string into the text file.
e.g.:

Do Until Not strOutput Like "*||*"
    strOutput = Replace(strOutput, "||", "|")
Loop

CodePudding user response：

Just realised there's a simpler answer; if there are no blank cells that you want to keep (ie all relevant cells in the given range have at least something in them) then change your For loop to :

For i = 1 To intUsedrows
    j = 1
    Do
        If .Cells(i, j 1) <> "" Then
            Print #1, .Cells(i, j); "|";
            j=j 1
        Else
            Print #1. .Cells(i, j)
            Exit Do
        End If
    Loop
Next i

CodePudding user response：

I'm sure Spencers solution would have solved it if I'd had the skills to apply it. While I was trying to work it our someone suggested this which works.

Sub pipey()
  'Create a text file with each row cell delimited with "|"
  Dim i As Long, j As Long, lr As Long, lc As Long
  
  Open "Y:\purchasing\Pipey.txt" For Output As #1

  With Sheets(1)
    lr = .Range("A" & Rows.Count).End(3).Row
    
    'first row is always 12 columns
    For j = 1 To 12
      Print #1, .Cells(1, j); IIf(j = 12, "", "|");
    Next
    Print #1,
    
    'the rows in between are always 9 columns
    For i = 2 To lr - 1
      For j = 1 To 9
        Print #1, .Cells(i, j); IIf(j = 9, "", "|");
      Next
      Print #1,
    Next
    
    'the last row is always 3 columns
    For j = 1 To 3
      Print #1, .Cells(lr, j); IIf(j = 3, "", "|");
    Next
    Print #1,
  End With
  
  Close #1
  MsgBox ("Done Successfully")
End Sub