Dynamic Deletions of Rows and Columns

[ebernon]

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:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 9/16/2009 by Ebernon
'
 
'
    Rows("1:20").Select
    Selection.Delete Shift:=xlUp
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:

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 9/16/2009 by Ebernon
'
 
'
    N1 = 20
    N2 = 1
    Range(Rows(N1), Rows(N2)).Select
    Selection.Delete Shift:=xlUp
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!!!