I understand the following clears column A of the active sheet:

Sub ClearColumnTest()
ActiveSheet.Range("A:A").Clear
End Sub

I also understand that this does the same thing:

Sub ClearColumnTest()
ActiveSheet.Columns(1).Clear
End Sub

But when creating formulas in Excel (as opposed to in VBA) we refer to
column 1 as column A. Why the discrepancy?

Is this a holdover from the R1C1 reference style of Lotus?

Dave
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

On Apr 6, 7:38 am, Dave F <D...@discussions.microsoft.com> wrote:
> I understand the following clears column A of the active sheet:
>
> Sub ClearColumnTest()
> ActiveSheet.Range("A:A").Clear
> End Sub
>
> I also understand that this does the same thing:
>
> Sub ClearColumnTest()
> ActiveSheet.Columns(1).Clear
> End Sub
>
> But when creating formulas in Excel (as opposed to in VBA) we refer to
> column 1 as column A. Why the discrepancy?
>
> Is this a holdover from the R1C1 reference style of Lotus?
>
> Dave
> --
> A hint to posters: Specific, detailed questions are more likely to be
> answered than questions that provide no detail about your problem.

Dave,

The computer would probably prefer to handle row, column index numbers
rather than letters; however, you can create formulas in VBA using A1
notation. I don't know that there is a "discrepancy" because you can
execute code with numbers or letters. I wouldn't say that it is a
"discrepancy," I would say that it is flexibility.

For example, the following syntax will execute:
Range("a3").Formula = "=SUM(A1:A2)"

I hope this answers your question.

Matt

Dave,

On the VBA side I think it's there to make it easier for loops and using
other variables as the row or column indicators. On the Sheet side I think
it's more readable with the "A" notation. Just my opinion.



"Dave F" wrote:

> I understand the following clears column A of the active sheet:
>
> Sub ClearColumnTest()
> ActiveSheet.Range("A:A").Clear
> End Sub
>
> I also understand that this does the same thing:
>
> Sub ClearColumnTest()
> ActiveSheet.Columns(1).Clear
> End Sub
>
> But when creating formulas in Excel (as opposed to in VBA) we refer to
> column 1 as column A. Why the discrepancy?
>
> Is this a holdover from the R1C1 reference style of Lotus?
>
> Dave
> --
> A hint to posters: Specific, detailed questions are more likely to be
> answered than questions that provide no detail about your problem.

Excel and VBA are two different software entities, but VBA is compatible with
Excel as a macro and programming language. In VBA when writing code and
referring to collections like Sheets, Columns and Rows, it is more efficient
to use index numbers (i.e. 1, 2, 3, etc.) that usinging full range references
or names. The software developers built this flexibility into the product in
many cases based on feedback from users. There are only a few things that
are in Excel to accomodate Lotus123.

"Dave F" wrote:

> I understand the following clears column A of the active sheet:
>
> Sub ClearColumnTest()
> ActiveSheet.Range("A:A").Clear
> End Sub
>
> I also understand that this does the same thing:
>
> Sub ClearColumnTest()
> ActiveSheet.Columns(1).Clear
> End Sub
>
> But when creating formulas in Excel (as opposed to in VBA) we refer to
> column 1 as column A. Why the discrepancy?
>
> Is this a holdover from the R1C1 reference style of Lotus?
>
> Dave
> --
> A hint to posters: Specific, detailed questions are more likely to be
> answered than questions that provide no detail about your problem.

But these work, too:

Option Explicit
Sub ClearColumnTest()
ActiveSheet.Columns("A").Clear
ActiveSheet.Columns("C:C").Clear
End Sub

IIRC, the first may cause problems in some earlier versions of excel.

And it's more than a hold over. I'm guessing that it allows some flexibility
for the user. (There are people who actually like R1C1 reference style!)

You can use: Tools|Options|General|check R1C1 Reference style
and write all your formulas using column numbers:

=r3c5
or
=r[-1]c[7]

====
My personal preference is to use:
ActiveSheet.Range("A:A").Clear
or
ActiveSheet.Range("A1").entirecolumn.Clear

Then I can change it to:
ActiveSheet.Range("A:C,d,g:H").Clear

without swearing when I do:
ActiveSheet.Columns("A:C,d,g:H").Clear




Dave F wrote:
>
> I understand the following clears column A of the active sheet:
>
> Sub ClearColumnTest()
> ActiveSheet.Range("A:A").Clear
> End Sub
>
> I also understand that this does the same thing:
>
> Sub ClearColumnTest()
> ActiveSheet.Columns(1).Clear
> End Sub
>
> But when creating formulas in Excel (as opposed to in VBA) we refer to
> column 1 as column A. Why the discrepancy?
>
> Is this a holdover from the R1C1 reference style of Lotus?
>
> Dave
> --
> A hint to posters: Specific, detailed questions are more likely to be
> answered than questions that provide no detail about your problem.

--

Dave Peterson