Add Blank Row After Group

[DMT Dave]

Hi Guy's please forgive me on this one, I have tried several things on this!!!!!, now lost my mind trying.......

I am outputting a recordset to excel, all good

there are 15 groups of records under field named SortNo (set as number)

so there could be

1 ItemNo
1 ItemNo
1 ItemNo
2 ItemNo
2 ItemNo
3 ItemNo
3 ItemNo
3 ItemNo
3 ItemNo
4 ItemNo
5 ItemNo

etc etc.. up to 15 item types

There will be no more than 15 items types but can be varying amount of records under sortNo 1 to 15

what i have lost my concentration on is trying to add a blank row to sperate SortNo rows

so the above listing becomes

1 ItemNo
1 ItemNo
1 ItemNo
(New Blank Row)
2 ItemNo
2 ItemNo
(New Blank Row)
3 ItemNo
3 ItemNo
3 ItemNo
3 ItemNo
(New Blank Row)
4 ItemNo
(New Blank Row)
5 ItemNo

etc etc

again please fogive me, i have lost my way here and appreciate any help ???

Correct Amount of records are outputting and immediate window showing correct amount of records

    
    Set rs = CurrentDb.OpenRecordset("qryStockUpdate")
            
    intCount = rs.RecordCount
    
    With xlSHT1
    
        xlSHT1.Cells(3, 3).CopyFromRecordset rs
        
        intLR = xlSHT1.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
         
    For x = 1 To intCount
    
        xlSHT1.Cells(3 & x, intCount).EntireRow.Insert
        
    Next x
    
    x = x + 1
    
    Debug.Print "Recoreset Count: " & intCount & vbCrLf & _
                "Last Row After Export: " & intLR & vbCrLf & _
                "How Many Single Records: " & x
                
    xlAP.Visible = True
    


    Set xlAP = Nothing
    Set xlWB = Nothing
    Set xlSHT1 = Nothing
    
    End With

[Micron]

You're trying to accomplish this in a loop by inserting a row? Then assign range A1 (1) to a variable and test for when it changes (A4 becomes 2). Then insert your row. Maybe I didn't understand your post completely.

[Welshgasman]

You cannot do it with the recordset as you have it.
Conside excel automation and inserting a blank row whe your criteria changes.

[June7]

When you reference an object with With, don't repeat that object within the With.

Why are you using ampersand?

This gets tricky because adding a row changes the end of the range and cell references.

You need a variable set to group ID which can compare to each group ID as it reads each cell and when the value is different, insert row and reset variable. Consider the following (assumes group numbers start with 1 and are sequential without gaps):

intID = 1
Do While x < intCount
     x = x + 1
     If intID <> .Cells(3 + x, 1).Value Then
          .Cells(3 + x, 1).EntireRow.Insert
          intID = intID + 1
          intCount = intCount + 1
          x = x + 1
     End If
Loop

[Micron]

To deal with expanding a row count during an operation, you start at the bottom and work your way to the top (Step -1).

[CJ_London]

you might get some ideas from the queries in this link
https://www.access-programmers.co.uk...report.326715/

none of the examples insert a blank line, but all determine the contents of each row. Should be easy enough to show nothing rather than a specific field

[June7]

Could also export a report with Grouping design. Group header without any controls would cause blank row between each group.

[Welshgasman]

To deal with expanding a row count during an operation, you start at the bottom and work your way to the top (Step -1).

I was thinking of just walking the rows, checking for a change in a column, until cell Ax is blank.

Then again, there is always Google?
https://www.google.com/search?q=inse...hrome&ie=UTF-8

and one of those links reveals.

[Sub blankRows()Dim LR As Long, i As Long
LR = Range("E" & Rows.Count).End(xlUp).Row
For i = LR To 2 Step -1
    If Range("E" & i).Value <> Range("E" & i - 1).Value Then Rows(i).Insert
Next i
End Sub

which is the method that @Micron advocates.

[DMT Dave]

Thanks WGM and all others, yes, i have picked up on what is suggested by all, also downloaded CJ London link to inspect later today, June7 in post 4 works great for every record and does insert a blank row, i think the main point i got lost with x = x + 1 PRIOR to insert as well as after insert, which i expect as it has to increment

So June7 option works well for all records, i now am going to loop the recordset as there are 15 SortNo (sorting Numbers) for all records, now want to group in sort numbers then insert the blank row

for example there maybe 150 records in 15 sort numbers

kind of hope i have made sense

but my biggest intake is how June7 has done that

[DMT Dave]

@WGM yes i have had a look at your link, also as suggested by micron by Step from the last row to first row

[Welshgasman]

Thanks WGM and all others, yes, i have picked up on what is suggested by all, also downloaded CJ London link to inspect later today, June7 in post 4 works great for every record and does insert a blank row, i think the main point i got lost with x = x + 1 PRIOR to insert as well as after insert, which i expect as it has to increment

So June7 option works well for all records, i now am going to loop the recordset as there are 15 SortNo (sorting Numbers) for all records, now want to group in sort numbers then insert the blank row

for example there maybe 150 records in 15 sort numbers

kind of hope i have made sense

but my biggest intake is how June7 has done that

Well if you are going to loop the recordset, and put the data in one record at a time, you can do it all from that, and not have to modify the worksheet afterwards.

[DMT Dave]

Thanks WGM and to all of you, this now separates the grouping numbers by a blank row perfectly, will use methods on here for other projects / inspection

Const NumRows As Integer = 1
Const TargetClm As String = "C"     ' change to suit
Set rs = CurrentDb.OpenRecordset(sSQL1)
    
    With xlSHT1
    
    intCount = rs.RecordCount
    
    
        xlSHT1.Cells(3, 3).CopyFromRecordset rs
    
        
        For r = (.Cells(.Rows.Count, TargetClm).End(xlUp).Row - 1) To 2 Step -1
            If .Cells(r, TargetClm).Value <> .Cells(r + 1, TargetClm).Value Then
                .Cells(r + 1, TargetClm).Resize(NumRows).EntireRow.Insert
            End If
        Next r
    
    
  '      intID = 1
  '      Do While x < intCount
  '          x = x + 1
  '          If intID <> .Cells(3 + x, 1).Value Then
  '              .Cells(3 + x, 1).EntireRow.Insert
  '              intID = intID + 1
  '              intCount = intCount + 1
  '          x = x + 1
  '          End If
  '      Loop
  
  
        intLR = xlSHT1.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        
            .Cells.EntireColumn.HorizontalAlignment = xlLeft
            .Cells(intLR + 2, 3) = "TOTALS:"
            .Cells(intLR + 2, 3).Interior.ColorIndex = 15
        iWHQty = DSum("StartQty", "tblStock", "[ItemType] Is Not Null")
            .Cells(intLR + 2, 7) = iWHQty
            .Cells(intLR + 2, 7).Interior.ColorIndex = 15
            .Cells(intLR + 2, 8).Interior.ColorIndex = 15
            .Cells(intLR + 2, 9).Interior.ColorIndex = 15