tidy vba

[mc136355]

Hi ive recorded a macro to transpose a cell contain text and then use in a validation. The code works fine but is really messy and i would just like to tidy it up. Wonder if there is any other code that would be more beneficial. Thanks in advance MC

Code:

Sub Macro3()
'
' Macro3 Macro
'

'
Range("C9").Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("F2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Range("F2:G2").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("F2:G2").Select
Application.CutCopyMode = False
Range("F2:G2").Select
Selection.ClearContents
Range("E2").Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("G2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

Range("G2:H2").Select
Selection.Copy
Range("G3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("G2:H2").Select
Application.CutCopyMode = False
Range("G2:H2").Select
Selection.ClearContents
Range("C2").Select

Range("H19").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$F$2:$F$4"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Selection.Copy
Range("H20:H21").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C2").Select


End Sub

 

[steve the fish]

Lets take a little part:

Range("F2:G2").Select
Selection.Copy
Range("F3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

You dont need to be selecting a range and then doing the operation on it so you could write:

Range("F2:G2").Copy
Range("F3").PasteSpecial Paste:=xlPasteAll, Transpose:=True

Hopefully you can see they are the same. Try it out on the numerous times you have it in the code.

 

[mc136355]

Thanks for the quick reply Steve. Will check this out tomorrow when i finish work. Thanks again MC

 

[mc136355]

Thanks for your help Steve. Cuts the vba down but still looking untidy. Will need to learn how to set out vba, which might help. thanks again MC

 

[Fluff]

If you post you updated code, we can look to see if any further improvements are possible