Starting at A1, if ( and only if -- if not, then the macro should stop ) there i

Question

Starting at A1, if (and only if -- if not, then the macro should stop) there is one character in A1, move the contents of A2 into B1, and move the contents of A3 into C1, and so on down Column A.

Thus, now with A2, if (and only if) there is one character in A2, move the contents of A2 into B2, and so on and so on.

If the macro is stopped, I can do some editing in the cell, and then the macro should be started up again where I place the cursor.

I would deeply appreciate you help in this matter.

Any suggestion???

Explanation / Answer

Took your advice and just having it process 3 rows at a time, but it processes from A1 down as far as it can go each time. If you have to fix something and continue, it does not mess up the previous processing.

Sub StrangeTranspose()
Dim lastRow As Long
Dim rowPtr As Long
Dim testFor1Word

lastRow = Range("A" & Rows.Count).End(xlUp).Row
'we will work in 3-row groups, testing A1, A4, A7 etc
'for just 1 word to the last row used

Application.ScreenUpdating = False

For rowPtr = 1 To lastRow Step 3
    testFor1Word = Trim(Range("A" & rowPtr))
    If InStr(testFor1Word, " ") > 0 Then
      'has space character, indicating more than 1 word
      MsgBox "Stopped processing at cell " & "A" & rowPtr
      Exit Sub ' stops at first mistake
    End If
    'move next row's data to column B
    'but only if it is not an empty cell
    If Not IsEmpty(Range("A" & rowPtr + 1)) Then
      Range("B" & rowPtr) = Range("A" & rowPtr + 1)
      'clear this entry in column A
      Range("A" & rowPtr + 1).ClearContents
    End If
    'do same thing for 2 rows down, but to column C
    If Not IsEmpty(Range("A" & rowPtr + 2)) Then
      Range("C" & rowPtr) = Range("A" & rowPtr + 2)
      'clear this entry in column A
      Range("A" & rowPtr + 2).ClearContents
    End If
Next ' end rowPtr loop
End Sub

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.