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?