VBA Code - Exclude Header/Footer row in Format

[gofalc]

Inherited Script that needs a modification. Unfamiliar with VBA code.
Script creates file a text successfully and formats columns with Pipe, however I need to Exclude First and Last row (header/footer) with "PIPE".


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


Dim intUsedrows As Long
Dim intUsedcolumns As Long


Dim rCell As Range
Dim rRow As Range
Dim sOutput As String
Dim sFname As String, lFnum As Long
Dim rowCount As Long


'Excel setup
Set xlApp = CreateObject("excel.application")
xlApp.DefaultSaveFormat = xlOpenXMLWorkbook
'Set xlWkb = xlApp.Workbooks.Add
'lWorkBookname = xlApp.ActiveWorkbook.Name
xlApp.Visible = True


xlApp.Workbooks.OpenText (IHTempLoc & "BRGLABS.TXT")


Open "L:\IT\MCK Reports\HAC\Humana\BRGLABS0.TXT" For Output As #1


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


For i = 1 To intUsedrows
For J = 1 To intUsedcolumns - 1
If J <> 1 Or J <> 17 Then colFormat = Trim(.Cells(i, J)) Else
If (J = 1 Or J = 17) Then colFormat = Trim(Mid((.Cells(i, J)), 2))




Print #1, colFormat; "|";


Next J
Print #1, Trim(.Cells(i, intUsedcolumns))
Next i
End With


Close #1
xlApp.DisplayAlerts = False
xlApp.Quit


End Sub

 

[JLGWhiz]

Try changing your values for i in the For i loop.

For i = 2 To intUsedrows - 1

That should then begin on row 2 and stop on next to last row.

 

[gofalc]

Try changing your values for i in the For i loop.

For i = 2 To intUsedrows - 1

That should then begin on row 2 and stop on next to last row.

If I modify "For i = 2 To intUsedrows - 1" , it completely deletes my header/footer row. In my case, I want to keep the those rows but not format it;

Example;
Header Row -
01|HELLO|20140701||||||||||||||||||||||||||||||||||||||||||||||||||||||

NEED -
01 HELLO 20140701