On 11 Maj, 17:35, FSt1 wrote:
hi
here is my guess.
your code goes through all cells in the column ie 65535 via for each....
when at the last cell, you try to select......myRange.Offset(0,
-1).Select....which is techniquelly off the sheet because you seem to be
coming from the bottom up. seems like you should be getting a "script out of
range" error.
i thing ron is suspecting the same thing i am.

regards
FSt1



"Brian" wrote:
I have to deal with CSV files output by some commercial software which
is not properly written. It gives long lists in which some of the rows
have more columns than others. These lists can be thousands of lines
long, so I wrote this macro, which moves the offending rows back to
where they should be.

First, I manually open up enough columns so that the rows can be moved
to the left. Then I establish which is the rightmost column containing
data, and I go to Row 1 and run the macro.

It works fine, except that it crashes at row 65535, instead of simply
ending because it has dealt with every cell in the column. I have
highlighted where it crashes, and would appreciate anyone who knows why
this happens.

Sub Line_up_cols()
'
' Keyboard Shortcut: Ctrl+Shift+L
'
Dim myRange As Range
Dim Cell As Range
For Each Cell In ActiveSheet.Columns()
* * *Selection.End(xlDown).Select
* * *Selection.End(xlToLeft).Select
* * *Set myRange = ActiveCell
* * *Range(Selection, Selection.End(xlToRight)).Select
* * *Selection.Cut
* * *myRange.Offset(0, -1).Select * * * * * <<<<---- Crashes here
* * *ActiveSheet.Paste
* * *ActiveCell.Offset(0, 1).Select
* * *Selection.End(xlToRight).Select
* * *ActiveCell.Offset(0, 1).Select
Next
End Sub- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Hi Brian

Try if this macro does what you need.

Sub ColToLeft()
Dim MyRange As Range
Set MyRange = Range("A1").CurrentRegion
rCount = MyRange.Rows.Count
For r = 1 To rCount
If Cells(r, 1).Value = "" Then
Range(Cells(r, 1).End(xlToRight), Cells(r,
1).End(xlToRight).End(xlToRight)).Cut Cells(r, 1)
End If
Next
End Sub

Regards,
Per