View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips Bob Phillips is offline
external usenet poster
 
Posts: 10,593
Default Why does this macro crash?

Presumably because you are in column A, and there is nothing to the left.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Brian" wrote in message
...
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