Excel Macro doesn't work in Excel 2016 - Selection.cut

Question

I have written and used simple Excel Macro to reformat an Excel workbook. It is a simple loop that cut and pastes cells and stops when the cell is empty (i.e. when it reaches the bottom of the page).

The macro works fine on my laptop with Excel 2013. The file doesn't work for my colleague who has Excel 2016. My colleague finds that Selection.cut doesn't remove the old, it leaves the old cell contents in place so the while loop finishes early.

We have tried with literally the same file and the same contents and it works for me, but not my colleague. We think it might be some difference between Excel 2013 and 2016. Code is as follows:

Sub NewMACRO()

Dim y As Integer
Dim row As Integer
Dim test As String


y = 1
row = 1
test = "test"

Do While test <> ""

    Cells(y, 1).Select
    Application.CutCopyMode = False
    Selection.Cut
    Cells(row, 1).Select
    ActiveSheet.Paste

    y = y + 2
    Cells(y, 1).Select
    Selection.Cut
    Cells(row, 2).Select
    ActiveSheet.Paste

    y = y + 2
    Cells(y, 1).Select
    Selection.Cut
    Cells(row, 3).Select
    ActiveSheet.Paste

    y = y + 2
    Cells(y, 1).Select
    test = Selection
    row = row + 1

Loop

Cells(1, 1).Select

This could use some clean up - you also may not need a loop. Can you explain what the start/end result of your macro? There is likely a more efficient way to accomplish this. As far as your code goes, you will need to elaborate on what **doesn't work in Excel 2016**. Does the code give an error? Does nothing seem to happen? Does something unexpected seem to happen? Your unqualified instances of `Cells` and dependency of `Active` objects is error prone. — urdearboy, Aug 30 '19 at 14:56
Have a read on why you should avoid using things like [Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — Zac, Aug 30 '19 at 14:59
Urdearboy: This code should change a web export from: Proposal Address Case Number Proposal Address Case Number (with each row on a separate line with a blank row between them To: Proposal Address Case Number Proposal Address Case Number (adjacent columns, no blank rows) — rkemp78, Aug 30 '19 at 15:06
When I say it doesn't work in Excel 2016, the Selection.cut doesn't blank the cells afterwards, so the While loop doesn't register that there is no content in the cell. — rkemp78, Aug 30 '19 at 15:11

Wizhi · Accepted Answer · 2019-08-31T22:26:24.467

Using selection in the code creates a lot of time error in the code. I have removed all selection code which should minimize that kind of issue.

Option Explicit

Sub NewMACRO()

Dim y As Long
Dim row As Long
Dim test As String

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

y = 1
row = 1
test = "test"

Do While test <> ""
    ws.Cells(y, 1).Cut ws.Cells(row, 1)

    y = y + 2
    ws.Cells(y, 1).Cut ws.Cells(row, 2)

    y = y + 2
    ws.Cells(y, 1).Cut ws.Cells(row, 3)


    '##Not sure what you want to do here.
    '##This is your original code where you populate the variable "test" with the selection value
    y = y + 2
    test = Cells(y, 1).Value
    row = row + 1


    '##I created this since it seems you want to use the variable "test" with some custom value populate to the variable
    'y = y + 2
    'Cells(y, 1).Value = test
    'If ws.Cells(row, 2) = "" Then
    '    ws.Cells(row, 1).ClearContents
    '    Exit Do
    'End If
    'row = row + 1

Loop

Cells(1, 1).Select

End Sub

I'm little puzzled by your code since it doesn't seem to use the variable test. Maybe something like this is what you wish to use your variable for: Replace the code:

y = y + 2
test = Cells(y, 1).Value
row = row + 1

With this code:

y = y + 2
Cells(y, 1).Value = test
If ws.Cells(row, 2) = "" Then
    ws.Cells(row, 1).ClearContents
    Exit Do
End If
row = row + 1

Thanks for that, works a treat! I was going to spend this morning rewriting the macro to avoid select, so you've saved me several hour's work. Thanks to everyone who helped. — rkemp78, Sep 02 '19 at 11:16
Cool :), glad to help. Please mark the answer as accepted (grey tick mark below voting buttons) if you think the question is solved for you. Have a great day!! =) — Wizhi, Sep 02 '19 at 11:50

Excel Macro doesn't work in Excel 2016 - Selection.cut

1 Answers1