Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
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



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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.

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 19
Default 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.

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 5,651
Default 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


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,942
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 703
Default 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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2007 crash when trying to add macro button to QAT Ron Excel Discussion (Misc queries) 2 August 17th 07 10:53 PM
VB Crash - Little Bit Of Fun RajenRajput1 Excel Discussion (Misc queries) 4 May 21st 07 02:35 PM
Filtering crash cj21 Excel Discussion (Misc queries) 0 December 14th 05 04:03 PM
Crash martin Excel Discussion (Misc queries) 2 July 13th 05 09:36 PM
Macro Glitch Causing Crash Danimagus Excel Discussion (Misc queries) 1 June 6th 05 06:19 PM


All times are GMT +1. The time now is 03:01 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"