Microsoft KB Archive/35731

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 16:55, 18 July 2020 by 3155ffGd (talk | contribs) (importing KB archive)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)



Selecting a Block of Cells with an Excel Macro

Last reviewed: November 4, 1994
Article ID: Q35731



2.x 3.x 4.00 5.00 | 2.20 2.21 3.00

WINDOWS           | OS/2

kbusage The information in this article applies to:

  • Microsoft Excel for Windows, versions 2.x, 3.0, 4.0, 5.0
  • Microsoft Excel for OS/2, versions 2.2, 3.0

SUMMARY

In Microsoft Excel it is possible to create and use a macro to select a block of cells starting at the current active cell and ending with the last cell in that block.

MORE INFORMATION

Microsoft provides macro examples for illustration only, without warranty either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. This macro is provided 'as is' and Microsoft does not guarantee that the following code can be used in all situations. Microsoft does not support modifications of the code to suit customer requirements.

The following Microsoft Excel macro selects a block of cells starting with the current active cell on the Worksheet and ending with the last cell of the block.

   =DEFINE.NAME("a", ACTIVE.CELL())
   =SELECT.END(4)
   =SELECT.END(2)
   =DEFINE.NAME("b", ACTIVE.CELL())
   =SELECT(!a:!b)
   =RETURN()

The following is an example:

   A1: 23   B1: a   C1: 2   D1: de
   A2: 24   B2: b   C2: 3   D2: fg
   A3: 25   B3: c   C3: 4   D3: hi
   A4: 27   B4: d   C4: 5   D4: jh

Select the desired sheet and position the cursor to the beginning of the block (Cell A1 in the example). Execute the macro using the defined Command Key sequence or by selecting run from the macro menu. The macro will select from Cell A1 to Cell D4. To use this macro, the block of information cannot have trailing information in the first column (in the example, Column A Row 5). This is because of the nature of the SELECT.END command. SELECT.END(4) will select down Column A until it finds a blank cell. SELECT.END(2) will select across Row 5 until it finds a blank cell. Since all cells in the row following Cell A5 are blank SELECT.END(2) will select from A5 until the end of the spreadsheet (IV5).

The DEFINE.NAME command is used in preference to the SET.NAME formula. This is because the SET.NAME statement will define the name (like the sheet command Formula, Define Name) to the Macro sheet and DEFINE.NAME will define the name to the active sheet.



KBCategory: kbusage

KBSubcategory:

Additional words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 3.00
4.0 4.00 4.0a 4.00a 5.0 5.00


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 4, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.