Record a macro  of yourself doing it. You can then work it into the rest of
your code.

Thanks ...but I was looking for something a bit more
dynamic.

Sub Macro1()
Dim MT As Integer
Range("H3:M3").Select
Selection.Delete Shift:=xlUp
Range("B4").Activate
MT = 0
Do While MT < 10
If ActiveCell.Value = "Quarter To Date" Then
ActiveCell.EntireRow.Delete
MT = 0
ActiveCell.Offset(-1, 0).Activate
ElseIf Len(ActiveCell.Value) = 0 Then
ActiveCell.EntireRow.ClearContents
MT = MT + 1
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

I forgot you wanted the rows  to have a "Quarter To Date" heading instead
of "Current Period" when finished. This modified version of my earlier
suggestion will do that, too.

Sub Macro1()
Dim MT As Integer
Range("H3:M3").Select
Selection.Delete Shift:=xlUp
Range("B4").Activate
MT = 0
Do While MT < 10
If ActiveCell.Value = "Quarter To Date" Then
ActiveCell.EntireRow.Delete
MT = 0
ActiveCell.Offset(-1, 0).Activate
ElseIf Len(ActiveCell.Value) = 0 Then
ActiveCell.EntireRow.ClearContents
MT = MT + 1
End If
ActiveCell.Offset(1, 0).Activate
Loop
Columns("B:B").Select
Selection.Replace What:="Current Period", Replacement:="Quarter To Date", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=
_
False, ReplaceFormat:=False
Range("A1").Select
End Sub

Thanks for the code. I will test it and let you know.

Doesn't quite work. The "Consultant" and "Quarter to
Date" should always be on the same line.

What is "MT" in the code below.

Your spreadsheet must be different than what I understood from your original
email. To create dummy data  for developing the macro, I entered Consultant1 in
A3 on a blank sheet. I entered Current Period in B3 and Quarter To Date in B4.
Then I selected A3:B5 and used the fill handle to copy  down through row  32,
giving me headings for Consultant1 through Consultant10. I put even numbers  in
the Current Period rows  and odd numbers in the Quarter To Date rows. Does that
resemble your spreadsheet? With or without the blank row between sets of
records, the macro  gives me exactly the output you described.

MT is a counter. When it accumulates a count of 10 contiguous empty cells in
column B, the macro stops looping. Since you said the number of consultants may
vary, this is a way for the macro to know when to quit.

Thanks for your reply. The consultant names are in
column 1 and the "Quarter to Date" is in column  H.
I will have another look at your code.

Since the info is downloaded from Crystal there are
some blank rows.

Yes, if the Current Period & Quarter To Date headings are in column  H, the macro
won't work as intended. Here is a revised version:

Sub Macro2()
Dim MT As Integer
Range("I3:M3").Select
Selection.Delete Shift:=xlUp
Range("H4").Activate
MT = 0
Do While MT < 10
If ActiveCell.Value = "Quarter To Date" Then
ActiveCell.EntireRow.Delete
MT = 0
ActiveCell.Offset(-1, 0).Activate
ElseIf Len(ActiveCell.Value) = 0 Then
ActiveCell.EntireRow.ClearContents
MT = MT + 1
End If
ActiveCell.Offset(1, 0).Activate
Loop
Columns("H:H").Select
Selection.Replace What:="Current Period", Replacement:="Quarter To Date", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=
_
False, ReplaceFormat:=False
Range("A1").Select
End Sub

I think I have figured it out ................

Yes, but once you know HOW to do it, you can insert it in the rest of your
code.