Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
clearing columns in VBA
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
clearing columns in VBA
On Apr 6, 7:38 am, 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, 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
clearing columns in VBA
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
clearing columns in VBA
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
clearing columns in VBA
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:D,g:H").Clear without swearing when I do: ActiveSheet.Columns("A:C,d: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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing cells without clearing formulas | Excel Discussion (Misc queries) | |||
Clearing out blank rows/columns | Excel Worksheet Functions | |||
Clearing #VALUE | Excel Worksheet Functions | |||
Clearing information in certain columns | Excel Discussion (Misc queries) | |||
Clearing row and columns | Excel Programming |