Add blank row after each record

[mercmannick]

[VBA]'transfer data to Excel
intLastRow = ShDest.Cells(Rows.Count, 1).End(xlUp).Row 'find last row in column A
Range("A" & intLastRow + 1).CopyFromRecordset rst ' Copy from the recordset starting at the Row after the last row of data in column A[/VBA]

Have this bit code to transfer recordset to Excel , after each record how can i add a blank row.

[VBA]sSQL = "SELECT [Sql Man Plan].[Roll No], [Sql Man Plan].Customer, [Sql Man Plan].[Wo Ref] AS WO, BOMm.[Cw No], BOMm.Type, BOMm.[Part No], BOMm.Drawing, BOMm.[quantity required] AS [Per Roll]" _
& " FROM [Sql Man Plan] INNER JOIN BOMm ON [Sql Man Plan].[Roll No] = BOMm.[roll no]" _
& " WHERE (((BOMm.[roll no])='" & ShDest.Range("O1").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O2").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O3").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O4").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O5").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O6").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O7").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O8").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O9").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O10").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O11").Value & "'));"[/VBA]

Many Thanks

Nick

[p45cal]

change
intLastRow + 1
to
intLastRow + 2
?

Correction (I'd assumed copyrecordset only did one line at a time) after your current code add a variant of:
[vba] newlastrow = Cells(Rows.Count, 1).End(xlUp).Row
For rw = newlastrow To intlastrow +1 Step -1
Cells(rw, 1).EntireRow.Insert Shift:=xlDown
Next rw[/vba]

[mercmannick]

change
intLastRow + 1
to
intLastRow + 2
?

Correction (I'd assumed copyrecordset only did one line at a time) after your current code add a variant of:
[vba] newlastrow = Cells(Rows.Count, 1).End(xlUp).Row
For rw = newlastrow To intlastrow +1 Step -1
Cells(rw, 1).EntireRow.Insert Shift:=xlDown
Next rw[/vba]

Hi p45cal

it actually puts a row between all rows now not after each recordset

Thanks

Nick

[rory]

That was what you asked for though. Otherwise, the intLastRow + 2 suggestion should work.

[mercmannick]

That was what you asked for though. Otherwise, the intLastRow + 2 suggestion should work.

Hi Rory , what i am trying to do is after each record from :

[VBA]& " WHERE (((BOMm.[roll no])='" & ShDest.Range("O1").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O2").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O3").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O4").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O5").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O6").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O7").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O8").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O9").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O10").Value & "'))" _
& "OR(((BOMm.[roll no]) ='" & ShDest.Range("O11").Value & "'));"[/VBA]

this sql , put a row between next record , so if [VBA]WHERE (((BOMm.[roll no])='" & ShDest.Range("O1").Value & "'))" [/VBA]

has 3 rows from recordset , keep together, then next recordset [VBA]WHERE (((BOMm.[roll no])='" & ShDest.Range("O2").Value & "'))" [/VBA]

should be a row between that and so on

Thanks

Nick

[rory]

You've only got one recordset, so you will have to go back afterwards and add the blank rows. You might want to use the Subtotals feature, depending on your situation.
Either that or you could run separate queries for each criteria and simply start the copyfromrecordset after a blank row for each.

[mercmannick]

You've only got one recordset, so you will have to go back afterwards and add the blank rows. You might want to use the Subtotals feature, depending on your situation.
Either that or you could run separate queries for each criteria and simply start the copyfromrecordset after a blank row for each.

99029 ArcelorMittal Atlantique et Lorraine - St Agathe 6369 CW6813KEY Ref:CW6813BK
99029 ArcelorMittal Atlantique et Lorraine - St Agathe 6369 CW6813FITTING Ref:CW6813FT
101408 Corus Port Talbot (Hot Mill) 6711 CW6294NECK RING FOR CW6294E

Hi Rory can you explain a little more this is what i get at moment and would like a row seperating them

Regards

Nick

[rory]

If you've only got a couple of rows coming back, then don't use copyfromrecordset - just loop through the fields and records and add a new line if the criteria field doesn't match the prvious one.