I solved my problem last night in a flurry of inspiration. I notice that no one else has voted for this question so this answer may not be helpful to anyone, but just in case.
First of all, I combined my custom row control and RowsPresenter in a grid of two rows, each row sized to Auto. I then placed the grid inside a ScrollViewer and then sized the scroll viewer row to Star sizing. I did not add the VerticalScrollbar template part into my template as this only scrolls the RowsPresenter.
This gave me the exact behaviour I was looking for where a row is added and the custom row remains pinned to the bottom of the last data row. When the rows and custom row overflow off the end of the visible area, the scrollbar appears to allow scrolling while keeping the headers fixed in place.
Job done. I hope someone finds this helpful. Below is my ControlTemplate XAML.
<ControlTemplate TargetType="swcd:DataGrid" x:Key="DataGridTemplate">
<Border
BorderBrush="{TemplateBinding BorderBrush}"
BorderThickness="{TemplateBinding BorderThickness}">
<Grid Name="Root" Background="{TemplateBinding Background}">
<Grid.RowDefinitions>
<RowDefinition Height="Auto" />
<RowDefinition Height="*" />
<RowDefinition Height="Auto" />
</Grid.RowDefinitions>
<Grid.ColumnDefinitions>
<ColumnDefinition Width="Auto" />
<ColumnDefinition Width="*" />
<ColumnDefinition Width="Auto" />
</Grid.ColumnDefinitions>
<swcdp:DataGridColumnHeader Name="TopLeftCornerHeader" Grid.Column="0"/>
<swcdp:DataGridColumnHeadersPresenter Name="ColumnHeadersPresenter" Grid.Column="1"/>
<swcdp:DataGridColumnHeader Name="TopRightCornerHeader" Grid.Column="2"/>
<ScrollViewer
Grid.Row="1"
Grid.Column="1"
Grid.ColumnSpan="1"
Padding="0,0,0,0"
BorderThickness="0,0,0,0"
VerticalScrollBarVisibility="Auto">
<Grid >
<Grid.RowDefinitions>
<RowDefinition Height="Auto" />
<RowDefinition Height="Auto" />
</Grid.RowDefinitions>
<swcdp:DataGridRowsPresenter Name="RowsPresenter" Grid.Row="0" />
<Border
Margin="1,1,1,1"
Padding="2,2,2,2"
BorderThickness="{TemplateBinding BorderThickness}"
BorderBrush="{TemplateBinding BorderBrush}"
Grid.Row="1">
<Grid Background="{TemplateBinding Background}">
<Grid.RowDefinitions>
<RowDefinition Height="Auto"/>
<RowDefinition Height="Auto"/>
</Grid.RowDefinitions>
<TextBlock
Grid.Row="0"
TextAlignment="Left"
TextWrapping="NoWrap"
Text="Add a new item using the lists below:" />
<mystuff:MySelectionControl
HorizontalContentAlignment="Stretch"
Grid.Row="1"
SelectionChanged="OnSelectionChanged"/>
</Grid>
</Border>
</Grid>
</ScrollViewer>
<Rectangle Name="BottomLeftCorner" Grid.Row="3" Grid.ColumnSpan="2" />
<Grid Grid.Column="1" Grid.Row="3">
<Grid.ColumnDefinitions>
<ColumnDefinition Width="Auto" />
<ColumnDefinition Width="*" />
</Grid.ColumnDefinitions>
<Rectangle Name="FrozenColumnScrollBarSpacer" />
<ScrollBar Name="HorizontalScrollbar" Grid.Column="1" Orientation="Horizontal" Height="18" />
</Grid>
<Rectangle Name="BottomRightCorner" Grid.Column="2" Grid.Row="3" />
</Grid>
</Border>
</ControlTemplate>