I'm not even sure if there's a solution on this one....

[PETERFT73]

Hi everyone,
So I've got a workbook with about 5500 rows of stats. The first column is a name and I need to delete or clear the first 3 rows each time a new name appears (the names are in alphabetical order). Some names have many more instances than others and there's no pattern to it. The most I've been able to do is find a way to insert a blank row between each name, but I can't figure how to do the deleting except to do it manually.
Any help would be greatly appreciated.
Thanks!!
Peter

 

[oldbrewer]

let A1 = fred
add alan to bottom of list, sort, so A1=alan and A2 = fred

do you really want to delete rows 1,2,3 ??????????????

 

[Rick Rothstein]

Give this macro a try...

Sub ClearFirstThreeNewNames()
  Dim R As Long, Cell As Range
  For R = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If Cells(R, "A").Value <> Cells(R - 1, "A").Value Then Rows(R).Insert
  Next
  For Each Cell In Columns("A").SpecialCells(xlConstants).Areas
    Cell.Resize(3).Clear
  Next
  Columns("A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[PETERFT73]

let A1 = fred
add alan to bottom of list, sort, so A1=alan and A2 = fred

do you really want to delete rows 1,2,3 ??????????????

Im sorry, I’m not following your suggestion. And yeah I think I have to. These are statistics and I’m trying to have the prior three instances’ average in each row. Since the first 3 will have data from a prior name, I see no other way than to clip 3 rows from each name.

 

[PETERFT73]

Thanks! I’m gonna try it right now.

 

[oldbrewer]

I am afraid I do not see what you want.........

 

[PETERFT73]

Give this macro a try...

Sub ClearFirstThreeNewNames()
  Dim R As Long, Cell As Range
  For R = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
    If Cells(R, "A").Value <> Cells(R - 1, "A").Value Then Rows(R).Insert
  Next
  For Each Cell In Columns("A").SpecialCells(xlConstants).Areas
    Cell.Resize(3).Clear
  Next
  Columns("A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

That worked perfectly. Thanks a million!

 

[PETERFT73]

Good Morning Rick,
Thank you again for your help. I have a quick follow up question. I'm considering trying to show the average of the previous 3 and 10 values in each row. I looked over the code you wrote and it seems as if I'd only need to change "Cell.Resize(3).Clear" subbing in the 10 for the 3. Do I have that right?
Thank you Sir!