Why does this macro crash?
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 |
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 |
Why does this macro crash?
Bob Phillips wrote:
Presumably because you are in column A, and there is nothing to the left. No, I open up some columns first and work from the right, so that there is space to move cells into. |
Why does this macro crash?
Bob Phillips wrote:
Presumably because you are in column A, and there is nothing to the left. BTW, I'm using XL2000 and XP SP2, if that makes any difference. |
Why does this macro crash?
On Sun, 11 May 2008 10:39:51 +0100, 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 What is the value of myRange.Address at the time of the crash? --ron |
Why does this macro crash?
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 |
Why does this macro crash?
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 |
All times are GMT +1. The time now is 04:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com