468,556 Members | 2,371 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Dynamic Deletions of Rows and Columns

The Dynamic deletion of Rows and Columns within a program is frequently desired but often hard to obtain. The Help files for VB-6 contained within Excel 2002 (XP) don’t always provide the help you need to perform this operation.

By recording a Macro, you can develop the following code snipet:

Expand|Select|Wrap|Line Numbers
  1. Sub Macro1()
  2. '
  3. ' Macro1 Macro
  4. ' Macro recorded 9/16/2009 by Ebernon
  5. '
  6.  
  7. '
  8.     Rows("1:20").Select
  9.     Selection.Delete Shift:=xlUp
  10. End Sub
  11.  
However, the Rows that the macro recorded are determined using the static “A1 Reference” annotation, the default for Excel, and this does not allow for the Dynamic setting of the Rows to be deleted within a program. You can change the reference, “1:20", to a number or a variable, but you then have a reference to a single Row.

To resolve this problem, the following snippet provides for the Dynamic setting of the Rows to be deleted by using the Range Object method:

Expand|Select|Wrap|Line Numbers
  1. Sub Macro2()
  2. '
  3. ' Macro2 Macro
  4. ' Macro recorded 9/16/2009 by Ebernon
  5. '
  6.  
  7. '
  8.     N1 = 20
  9.     N2 = 1
  10.     Range(Rows(N1), Rows(N2)).Select
  11.     Selection.Delete Shift:=xlUp
  12. End Sub
  13.  
Note that the beginning and ending Rows, N1 and N2, do not have to be in any order as the Range Object in Excel automatically adjusts for this. Also, N1 and N2 can now be set either within the program or by data input from an operator.

For Columns, simply substitute the "Range(Columns(N1),Columns(N2)).Select" for the Range to be deleted.

Final Note: Be careful when deleting cells to which links have been added, this will cause errors to appear in the cells or ranges that have been linked to the deleted cell.

Happy Programming!!!
Sep 17 '09 #1
0 2806

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

60 posts views Thread by Peter Olcott | last post: by
2 posts views Thread by =?Utf-8?B?dmFuZGls?= | last post: by
reply views Thread by Eniac | last post: by
2 posts views Thread by DaveL | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.