Short code

[VBABEGINER]

ANy other way to write this code..

ActiveWorkbook.Sheets("Sur").Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Columns("S:S").Select
Selection.Copy
Columns("R:R").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

 

[ParamRay]

Both salient points, @ParamRay. Perhaps the shortest way to handle the original request with your points would be to modify Rick's code as follows:

    Sheets("Sur").Columns("S").Insert
    Sheets("Sur").Columns("R").Copy Sheets("Sur").Range("S1")

My original snippet still had a Select in it. Since VBABEGINER's original code also required that the Sur sheet be selected, I didn't see a problem with my code also requiring that, but I did think it would be beneficial to explicitly comment on it. I could have spent more time finding a way to remove the Select from my code, but I had time constraints, and since my snippet was already 4 lines shorter than the original, I felt it worthwhile to post.

It makes me think we're stuck in our ways! it's about time that VBA got acquainted with the modern world, and we get an explicit, strictly typed language, as well as a better object model in Excel, in order to be forced into better coding... (Rant over.)

... Not to mention, also a 21st century IDE!!

 

[Eric W]

Again, all good points!

From what I've read, the Excel developers really like VBA, so I don't see it going away anytime soon. But there may be cause for hope, if you're a Python fan. On the request site, the number one request is for Python integration:

https://excel.uservoice.com/forums/...0549005-python-as-an-excel-scripting-language

No promises from Microsoft, except that they'll consider it. And a better IDE would be nice too, but again, they're constrained by millions of current users loath to learn something new.

 

[ParamRay]

Again, all good points!

From what I've read, the Excel developers really like VBA, so I don't see it going away anytime soon. But there may be cause for hope, if you're a Python fan. On the request site, the number one request is for Python integration:

https://excel.uservoice.com/forums/...0549005-python-as-an-excel-scripting-language

No promises from Microsoft, except that they'll consider it. And a better IDE would be nice too, but again, they're constrained by millions of current users loath to learn something new.

Woah, tonnes of votes on that, thanks for sharing.

If they do feel constrained to VBA, at least a better IDE, and a richer set of events to handle would be a step in the right direction. It would be nice to have events for a range, a shape, an autofilter, etc. Also, some built-in wrappers for .NET classes would be handy, and introduction of generics could discourage all the dynamic typing with Variant.