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

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

cheers joel.
i dont think that this is helping after trying it a few times

what i am trying to do is make the macro only keep data on the sheet which
has 8 columns and then move that data to cell a1 as i have formulas waiting
to collect the data in these cells.

i was using the code i posted earlier and then made another wich is

Sub Cleanupdata()

'

' Cleanupdata Macro

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

Selection.SpecialCells(xlCellTypeConstants, 3).Select

Application.CutCopyMode = False

Selection.Cut

Range("A1").Select

ActiveSheet.Paste

End Sub

this works apart from it deletes the rows and the formulas in the other
sheet then turn to #REF as they cannot find the original cells i had asked
them to look for.

is this any clearer?
sorry if its not.

Steve



"Joel" wrote:

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