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?

 

[MARK858]

The code shouldn't stop, you should just get a #Name error in the cells.

Do you get the correct result with

    Range("I1").Formula = "=SUM(F:F)/I2"
    Range("I1").NumberFormat = "#,##0"
    With Range("I2")
        .NumberFormat = "0.0"
        .Formula = "=SUM(G:G)"
    End With

 

[Eric W]

One possibility. Are you sure this macro runs on Excel? I have seen the A2:A syntax used on Google Sheets, but it's not Excel standard. I don't know if VBA can run on Google sheets though.

 

[MARK858]

I don't know if VBA can run on Google sheets though.

Hi Eric, Google sheets uses Google Apps Script which is based on Java not VBA.
You can sort of get around it by using something like GSpread.NET (only played with it a bit on my friends computer... she hasn't managed to convert me to the darkside yet).