D
Dave
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
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