moving all data to cell a1

S

Steve

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
 
J

Joel

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).ClearContents
Columns("A:A").Delete
Range("A1").Select
Cells.ShowAll
 
J

Joel

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).ClearContents
Columns("A:A").Delete
Range("A1").Select
Cells.ShowAll
 
S

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 said:
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).ClearContents
Columns("A:A").Delete
Range("A1").Select
Cells.ShowAll


Steve said:
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
Click to expand...
Click to expand...
 
S

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 said:
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).ClearContents
Columns("A:A").Delete
Range("A1").Select
Cells.ShowAll


Steve said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro Loop 1
Problem with Macro 7
Hard reference in a macro 1
code question 2
Different Results from the Same Macro 2
Please Help: Macro Nearly Finished 1
Auto filter using macro not woking 2
Paste method error in macro 2

Top