CODEWITHSUNDEEP
excelvba
BriceTRockindale
BriceTRockindale

Reputation: 319

VBA script not compatible with Excel file?

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

Upvotes: 1

Views: 73

Answers (1)

Stewbob
Stewbob

Reputation: 16899

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

Upvotes: 2

Related Questions