Macro: Find certain criteria then delete everything below

[Slowy]

i have a report that creates many duplicate names. am using the Subtotals function so i can make the sheet only show 1 name.
i then have "Grand Count"

what i would like the macro do to is to
Find Grand count and then delete everything from Grand count to the end of the sheet

Grand count is ALWAYS in column A, but NOT always in the same row

thank you

 

[Slowy]

sorry, i will be using the macro to do everything from beginning to end. i will be inserting the 'find and delete' portion into the macro

 

[pboltonchina]

See if you can work this in to your code

With ActiveSheet
  Set Rng = Columns("A")
Set rFound = Rng.Find(What:="Grand Count", LookAt:=xlPart, LookIn:=xlValues, MatchCase:=False)
  'check if it was found:
  If Not rFound Is Nothing Then   'if it isn't nothing then it's something ie it was found!
    'now use rFound to start your process:
    rFound.Offset(1).Select
    Range(Selection, SpecialCells(xlCellTypeLastCell)).Select
    Selection.Delete Shift:=xlUp
    End If
End With

 

[Slowy]

Range(Selection, SpecialCells(xlCellTypeLastCell)).Select

i'm getting a Compile Error that says 'Sub of Function not Defined and it highlights 'special cells'

 

[pboltonchina]

Try replacing this

Range(Selection, SpecialCells(xlCellTypeLastCell)).Select
    Selection.Delete Shift:=xlUp
[CODE]
With this

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Delete Shift:=xlToLeft
[/CODE]

 

[Slowy]

thank you Sir!