Counting rows and inserting blank lines

[Valerie]

Hello, all.

I have a spreadsheet that has "sections" - a header row and several detail
rows beneath it until the next header row, etc. The sections are different
companies. This is for an JE upload into SAP and the row count for each
company section is limited to 190 rows. I have 2 companies that are always
larger than 190 and occasionally one other company. This is the macro I
currently have that is for a specific company:

'Split lines longer than 190 for US14

''' where to search
Set rg = ActiveSheet.Range("E:E")
''' search for 'found'
Set rg = rg.Find(What:="US14", LookIn:=xlValues, LookAt:=xlWhole)
''' process result
If rg Is Nothing Then ''' was not found
MsgBox "Not found"
Else
''' go 190 rows below that found cell
Set rg = rg.Offset(190)
''' resize to 2 rows
Set rg = rg.Resize(2)
''' insert 2 rows
rg.EntireRow.Insert xlShiftDown
End If
'Range("A1").Select
Selection.End(xlUp).Select
ActiveCell.Offset(0, -4).Select
Selection.EntireRow.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(2, 0).Select
ActiveSheet.Paste
'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

Cells.Find(What:="US14 Total", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False).Activate
Selection.EntireRow.Copy
Selection.End(xlUp).Select
ActiveCell.Offset(-1, 0).Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(0, 8).Select
ActiveCell.FormulaR1C1 = _

"=SUMIF(R[-189]C3:R[-1]C3,40,R[-189]C9:R[-1]C9)-SUMIF(R[-189]C3:R[-1]C3,50,R[-189]C9:R[-1]C9)"

I am wondering if there is a way this macro could be enhanced/revised to
where XL would evaluate the number of lines within the company section to see
if a break is needed and if so, how many breaks (US14 often needs 2 - has
more than 380 lines) and insert these breaks. I currently have to do the
second break manually. Any help with this would be greatly appreciated!!

Thanks!
Valerie

 

[PY & Associates]

Hello, all.

I have a spreadsheet that has "sections" - a header row and several detail
rows beneath it until the next header row, etc.  The sections are different
companies. This is for an JE upload into SAP and the row count for each
company section is limited to 190 rows.  I have 2 companies that are always
larger than 190 and occasionally one other company.  This is the macro I
currently have that is for a specific company:

'Split lines longer than 190 for US14

   ''' where to search
   Set rg = ActiveSheet.Range("E:E")
   ''' search for 'found'
   Set rg = rg.Find(What:="US14", LookIn:=xlValues, LookAt:=xlWhole)
   ''' process result
   If rg Is Nothing Then   ''' was not found
      MsgBox "Not found"
   Else
      ''' go 190 rows below that found cell
      Set rg = rg.Offset(190)
      ''' resize to 2 rows
      Set rg = rg.Resize(2)
      ''' insert 2 rows
      rg.EntireRow.Insert xlShiftDown
   End If
    'Range("A1").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(0, -4).Select
    Selection.EntireRow.Copy
    Selection.End(xlDown).Select
    ActiveCell.Offset(2, 0).Select
    ActiveSheet.Paste
    'Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False

    Cells.Find(What:="US14 Total", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
        xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
        False).Activate
    Selection.EntireRow.Copy
    Selection.End(xlUp).Select
    ActiveCell.Offset(-1, 0).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.Offset(0, 8).Select
    ActiveCell.FormulaR1C1 = _

"=SUMIF(R[-189]C3:R[-1]C3,40,R[-189]C9:R[-1]C9)-SUMIF(R[-189]C3:R[-1]C3,50,­R[-189]C9:R[-1]C9)"

I am wondering if there is a way this macro could be enhanced/revised to
where XL would evaluate the number of lines within the company section tosee
if a break is needed and if so, how many breaks (US14 often needs 2 - has
more than 380 lines) and insert these breaks.  I currently have to do the
second break manually.  Any help with this would be greatly appreciated!!

Thanks!
Valerie

I imagine I would search for the header rows and get the number of
rows inbetween.
If more than "190" then insert accordingly, else do nothing.