VBA script not compatible with Excel file?

Question

I have this Excel spreadsheet/workbook that parses through some data and creates another condensed spreadsheet. The problem is when the sheet has more than 255 columns. When the line of VBA code at the bottom is run, it returns 1, rather than 258. It seems as though the VBA script that I am working on is made for the older version of Excel, right? What do I have to do to get this to work properly?

Excel.Worksheet.Cells(1, MySheet.Columns.Count).End(xlToLeft).Column returns 1 when there are more than 255 columns

The problem might be that Excel.Worksheet.Cells and MySheet.Columns.Count actually refer to different worksheets. It should probably read: MySheet.Cells(1, MySheet.Columns.Count)..... — Stewbob
– Stewbob, Commented Feb 7, 2014 at 19:46
That should always return column number for the last no empty cell in that row... — CRondao
– CRondao, Commented Feb 7, 2014 at 19:49

Stewbob · Accepted Answer · 2014-02-07 19:53:39Z

2

In your code Excel.Worksheet.Cells refers to the current, or active, worksheet, while MySheet.Columns.Count refers to a specific worksheet, defined in the MySheet variable.

If MySheet is not the current sheet, this code will return results that may differ from what you expect.

Change the line of code to:

MySheet.Cells(1, MySheet.Columns.Count).End(xlToLeft).Column

answered Feb 7, 2014 at 19:53

Stewbob

16.9k9 gold badges67 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BriceTRockindale Over a year ago

As I mentioned above, you are absolutely correct. I appreciate the quick and accurate answer!!

Collectives™ on Stack Overflow

VBA script not compatible with Excel file?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related