Hi guys,

I'm looking for help with the macro below.

What it does at the moment is:

Delete Column A
Delete Column C
Delete Rows 1 to 7
Delete Column C to BA
Autofit Rows A and B
Insert 6 Columns
Split the Data in Column A using 'Text to Columns' (See below for settings)
Delete Columns B to F
This then leaves me with a Name in Column A and a Date in column B.

The problem I have however is with the section that "Delete Columns B to F"

What I would like to happen is:

The macro looks through Rows A to G
If there is a value in Row G, Delete Rows A to F
If there is no value in Row G, but there is a Value in Row F, Delete Rows A
to E and Row G

etc. etc.

Can anyone help me with this?

Thanks
Dave




Macro below:-

Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Rows("1:7").Select
Range("A7").Activate
Selection.Delete Shift:=xlUp
Columns("C:BA").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").Select

'Insert 6 columns

Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight


'Split Column A into Individual Names/Words i.e. First Name / Middle
Name /
'Surname /

Columns("A:A").Select

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("B:F").Select
Selection.Delete Shift:=xlToLeft


'Convert Column B into Date Format

Columns("B:B").Select
Selection.NumberFormat = "dd/mm/yyyy"
Range("C1").Select

You said rows when you may have meant columns. You said b-f and then a-f. I
assume you wanted to keep col A. Sounds like this may work for part of it.
If you post before/after examples or send a workbook I can help with the
rest. Selections are NOT necessary or desirable.

Sub deletecolumnsif()
mr = ActiveCell.Row
lc = Cells(mr, Columns.Count).End(xlToLeft).Column
Columns(lc + 1).Delete
Range(Cells(mr, 2), Cells(lc - 1)).EntireColumn.Delete
columns("a:b").autofit
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Dave" wrote in message
...
Hi guys,

I'm looking for help with the macro below.

What it does at the moment is:

Delete Column A
Delete Column C
Delete Rows 1 to 7
Delete Column C to BA
Autofit Rows A and B
Insert 6 Columns
Split the Data in Column A using 'Text to Columns' (See below for
settings)
Delete Columns B to F
This then leaves me with a Name in Column A and a Date in column B.

The problem I have however is with the section that "Delete Columns B to
F"

What I would like to happen is:

The macro looks through Rows A to G
If there is a value in Row G, Delete Rows A to F
If there is no value in Row G, but there is a Value in Row F, Delete Rows
A
to E and Row G

etc. etc.

Can anyone help me with this?

Thanks
Dave




Macro below:-

Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Rows("1:7").Select
Range("A7").Activate
Selection.Delete Shift:=xlUp
Columns("C:BA").Select
Selection.Delete Shift:=xlToLeft
Columns("B:B").Select
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:B").Select

'Insert 6 columns

Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight
Selection.Insert Shift:=xlToRight


'Split Column A into Individual Names/Words i.e. First Name / Middle
Name /
'Surname /

Columns("A:A").Select

Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True,
Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False,
FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("B:F").Select
Selection.Delete Shift:=xlToLeft


'Convert Column B into Date Format

Columns("B:B").Select
Selection.NumberFormat = "dd/mm/yyyy"
Range("C1").Select