By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,558 Members | 1,533 Online
Bytes IT Community
Submit an Article
Got Smarts?
Share your bits of IT knowledge by writing an article on Bytes.

Dynamic Deletions of Rows and Columns

P: 1
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. '
  7. '
  8.     Rows("1:20").Select
  9.     Selection.Delete Shift:=xlUp
  10. End Sub
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. '
  7. '
  8.     N1 = 20
  9.     N2 = 1
  10.     Range(Rows(N1), Rows(N2)).Select
  11.     Selection.Delete Shift:=xlUp
  12. End Sub
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
Share this Article
Share on Google+