Yup, I noticed as I ran the formula but just amended the values to 8 and your
solution works very well.

All that I need now is to shift the results up to the top of the sheet. As
this will vary with each import I am unsure how to ask Excel to choose just
the results cut and paste them to the top. Maybe, its F5 select visibles copy
and paste? Apologies for the badly worded "without collapsing the above
columns" bit.

So far the code is (Sorry if I am not using the proper convention to show
this)

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




"Gord Dibben" wrote:

Actually we both made a mistake.............A:H is 8 columns, not 7 so
formula will have to adjusted if you want 8

Your subject line reads 7 columns but you state " columns A to
H"...........I missed that first time around.

Not sure what you mean by "without collapsing the above columns".

Deleting the helper column you inserted should give you 7 columns A:G


Gord

On Thu, 21 May 2009 08:41:01 -0700, Keith
wrote:

I have just recorded this as a macro and it works brilliantly.

I now need to move the resulting cells back to A1 without collapsing the
above columns. Any help would be greatly recieved.



"Keith" wrote:

Thank you, I would never have got that.

"Gord Dibben" wrote:

Insert a new column left of current column A

Insert a new row above row 1

In new A1 enter any title text.

Then in A2 =IF(COUNTA(B2:AX2)<7,"not 7",7))

Copy down.

Autofilter for "not 7" then F5SpecialVisible cells onlyOKDelete Row

Delete Column A


Gord Dibben MS Excel MVP


On Thu, 21 May 2009 06:13:10 -0700, Keith
wrote:

Good Afternoon,

I have a simple problem that has me scratching my head. I have a report
imported into Excel that I need to clean of unwanted data. I dont have
control over what is sent and this varies considerably.

However, the data that I need is allways in rows and populates columns A to
H. Is there away to delete all the other rows automatically?