VBA error on selecting a whole column A1:A

[PAULLYSTEW]

I was sent a workbook that had macros selecting columns of varying length. The debugger showed me it did not like the selection (A1:A)
Once I changed it to (A1:A1000) far more cells than I'll ever populate, the macro works fine.

The question is why does my Office 2016 not like it but many others who got the same file have no issues?
Is there a parameter I can change on my system to allow this type of alliteration?

 

[Scott T]

To select a column you would use

Columns("A:A").Select

It worked after you changed it since it now had a valid range

 

[PAULLYSTEW]

I would but we have to omit the first cell so it is actually (A2:A) that is in the macro.
Select cell A2 all the way down.

 

[MARK858]

Try the below if it is the active sheet....

Range("A2:A" & Range("A" & Rows.count).End(xlUp).Row).Select

Although you don't need to select cells to work with them

 

[PAULLYSTEW]

I see this is another workaround and I will pass that off to the author of the workbook.
Any guesses as to why or when the (A2:A) did work? Older version? Newer version?

That is my question more than anything.

 

[Fluff]

As far as I know, it has never worked.
Certainly doesn't work in 2003, or 2013
Pretty sure it didn't work in 2000, but cannot check that.

 

[MARK858]

Any guesses as to why or when the (A2:A) did work?

As Fluff has stated the A2:A syntax would never have worked in my memory as well, unless you are referencing the entire column (including A1) as Scott T has posted in post number 2 you need the row number on the first cell and the last cell or you will get a syntax error.

I think you need to look again at what the other users have in their code as it can't be just A2:A.

 

[PAULLYSTEW]

Here is a sample from the Macro within the workbook that no one else has issue with but me in a 90 store chain. You can see multiple times these selection format that you say should not work.

Range("I1").Select
ActiveCell.Formula = "=SUM(F2:F)/I2"
Range("I1").Select
Selection.NumberFormat = "#,##0"
Range("I2").Select
Selection.NumberFormat = "0.0"
ActiveCell.Formula = "=SUM(G2:G)"

 

[MARK858]

No what you have posted is a single cell with a column and row reference I2 not a column which you would have as I2:I.

The SUM(F2:F) is within a formula not the VBA syntax and basically is just a text string as it is in quotes..

 

[PAULLYSTEW]

Yes, sure, but when the macro runs, it halts at this point saying invalid. I correct the (G2:G) to say (G2:G50) and it works.
What I want to know is why does this macro work on all other users that get this workbook, but me?