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?

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?

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?

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?

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?

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?

In your code you ClearContents rather than delete rows so you get blank
rows. Deleted rows automatically shift up.

Try this tested revision...........note the changes made to give you a
dynamic range rather than a hard-coded A2:A42

And I got rid of most of the "selects" which slows code dowm. You rarely
have to select things to work on them.

Sub Cleanupdata()
'
' Cleanupdata Macro
' Macro recorded 21/05/2009 by Keith
'

'
Dim Lrow As Long
Lrow = Range("B" & Rows.Count).End(xlUp).Row
Rows("1:1").EntireRow.Insert
Columns("A:A").Insert
Range("A2").FormulaR1C1 = "=IF(COUNTA(RC[1]:RC[49])<8,""not 8"",8)"
Lrow = Range("B" & Rows.Count).End(xlUp).Row
Range("A2:A" & Lrow).FillDown
With ActiveSheet.UsedRange
.AutoFilter
.AutoFilter Field:=1, Criteria1:="not 8"
.SpecialCells(xlCellTypeVisible).Select
.EntireRow.Delete
End With
Columns("A:A").Delete
Rows("1:1").EntireRow.Delete
Range("A1").Select
End Sub


Gord

On Thu, 21 May 2009 09:42:04 -0700, Keith
wrote:

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?

You may also want to qualify which sheet to operate on.

Sub Cleanupdata()
Sheets("Sheet1").Select 'adjust sheetname to suit
rest of code goes here
End Sub


Gord

On Thu, 21 May 2009 12:13:07 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

In your code you ClearContents rather than delete rows so you get blank
rows. Deleted rows automatically shift up.

Try this tested revision...........note the changes made to give you a
dynamic range rather than a hard-coded A2:A42

And I got rid of most of the "selects" which slows code dowm. You rarely
have to select things to work on them.

Thank you sir, that seems to be it, you have been a great help. I see a very
steep learning curve heading my way!

"Gord Dibben" wrote:

You may also want to qualify which sheet to operate on.

Sub Cleanupdata()
Sheets("Sheet1").Select 'adjust sheetname to suit
rest of code goes here
End Sub


Gord

On Thu, 21 May 2009 12:13:07 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

In your code you ClearContents rather than delete rows so you get blank
rows. Deleted rows automatically shift up.

Try this tested revision...........note the changes made to give you a
dynamic range rather than a hard-coded A2:A42

And I got rid of most of the "selects" which slows code dowm. You rarely
have to select things to work on them.