I don't think the rows are deleted. The code left the autofilter on. You
that you are missing row numbers. the rows aren't actually deleted. Use
Showall to remove the autofilter. In the code below I removed the Selects to
simplify the macro.

Rows(1).Insert
Columns("A:A").Insert
Range("A2").FormulaR1C1 = "=IF(COUNTA(RC[1]:RC[49])<8,""not 8"",8)"
Range("A2").AutoFill Destination:=Range("A2:A42"), Type:=xlFillDefault
Columns("A:A").AutoFilter Field:=1, Criteria1:="not 8"
Columns("A:A").SpecialCells(xlCellTypeVisible).Cle arContents
Columns("A:A").Delete
Range("A1").Select
Cells.ShowAll


"Steve" wrote:

hi,

i have this code....

Sub Cleanupdata()

'

' Cleanupdata Macro

' Macro recorded 21/05/2009 by Keith

'



'

Rows("1:1").Select

Selection.Insert Shift:=xlDown

Columns("A:A").Select

Selection.Insert Shift:=xlToRight

Range("A2").Select

ActiveCell.FormulaR1C1 = "=IF(COUNTA(RC[1]:RC[49])<8,""not 8"",8)"

Selection.AutoFill Destination:=Range("A2:A42"), Type:=xlFillDefault

Range("A2:A42").Select

Range("A1").Select

Selection.AutoFilter

Selection.AutoFilter Field:=1, Criteria1:="not 8"

Selection.SpecialCells(xlCellTypeVisible).Select

Selection.ClearContents

Columns("A:A").Select

Selection.Delete Shift:=xlToLeft

Range("A1").Select

End Sub


is there anyway in which i can add to this so that it moves all the data
that is left to cell a1 without deleting the rows??

Thank you in advance,

Steve