Curious Delete Behavior with Command Button VBA

Question

I am continuing my efforts with data pulled from a mainframe. Currently, I am continuing work on sorting data for use in forms and potential data processing and the like. The data is alphanumeric and is similar in form to one of my previous questions related to my continuing data efforts.

One of my current development lines involved increased usability in the form of macro-enabled buttons. One such button involves a macro which is intended to delete all data in one column "A" from A2 to the end of any existing datarows in the sheet.

The code is as follows:

Sub DeleteCriteria_Click()

Dim CriteriaRange As Range

Dim LastCriteriaCell As Long

With Sheets("Criteria")
    LastCriteriaCell = .Range("A" & Sheets("Criteria").Rows.Count).End(xlUp).Row
    Set CriteriaRange = .Range("A2:A" & LastCriteriaCell)
End With

CriteriaRange.Cells.Select

Selection.Delete

End Sub

Curiously, this code ends up shifting my columns over by one each time I activate the button, and ends up deleting my header and subsequent columns with repeated clicks. This behavior resembles that of a normal delete function for a column. Refactoring the range commands CriteriaRange.Cells.Select | Selection.Delete into the forms CriteriaRange.Cells.Delete and CriteriaRange.Delete does not correct this issue.

My intent is to completely delete the entries so that there are no blanks or leftover datasets when new criteria is entered after the entries are deleted. I thus have two questions:

1) What is causing this behavior, i.e. what I am doing incorrectly, here?

2) How do I correct this behavior to utterly delete the cells or functionally perform the equivalent, thereby prevent any references to blank or null cells, while retaining my column?

Siddharth Rout · Accepted Answer · 2012-08-13T16:30:04.777

2

Change your code to

CriteriaRange.Delete Shift:=xlUp

The default is

CriteriaRange.Delete Shift:=xlToLeft

Because of this your columns are moved.

Your code can be written as

Sub DeleteCriteria_Click()
    Dim LastCriteriaCell As Long

    With Sheets("Criteria")
        LastCriteriaCell = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> This is required so that your header cells are not touched
        If LastCriteriaCell < 2 Then Exit Sub

        .Range("A2:A" & LastCriteriaCell).Delete Shift:=xlUp
    End With
End Sub

OR

Sub DeleteCriteria_Click()
    Dim LastCriteriaCell As Long

    With Sheets("Criteria")
        LastCriteriaCell = .Range("A" & .Rows.Count).End(xlUp).Row

        '~~> This is required so that your header cells are not touched
        If LastCriteriaCell >= 2 Then _
        .Range("A2:A" & LastCriteriaCell).Delete Shift:=xlUp
    End With
End Sub

edited Aug 13 '12 at 16:30

answered Aug 13 '12 at 16:21

Siddharth Rout

147,039
17
206
250

Siddharth Rout, how very interesting - it never occured to me that .Range.Delete had settings or defaults for those settings, although that clearly makes sense. Notably, when I execute the code above, it ends up deleting my header the second time. Do I need to reset my variable in some way, or have I missed another step? – Scott Conover Aug 13 '12 at 16:25
Add this line `If LastCriteriaCell < 2 then Exit Sub` after this `LastCriteriaCell = .Range("A" & Sheets("Criteria").Rows.Count).End(xlUp).Row` – Siddharth Rout Aug 13 '12 at 16:28
Siddharth Rout, Thanks! I tested the code and was doing research; it is interesting that if A1 is the only one left it simply uses the range of A to the row.count - "A1" - to create a "A1:A2" range, which deletes the header. – Scott Conover Aug 13 '12 at 17:01
Yup and that is why we have that additional check in the code to ensure that the code runs only if the lastrow is >= 2 – Siddharth Rout Aug 13 '12 at 17:02
Indeed, that is a very kewl fix, and very much in line with the focus in VBA on functionality and scripting. My C#-focused framework seems to resist it at times - I was originally searching for a conditional method, but I needed the right framework to consider the solution. For instance, you can also use an If condition with the LastCriteriaCell range variable, using the form `If LastCriteriaCell >= 2` . This [pastebin link](http://pastebin.com/PUdiH1Zv) provides the full code. However, I think your solution is more elegant and likely less intensive with large sets of code in VBA. – Scott Conover Aug 13 '12 at 17:16

Curious Delete Behavior with Command Button VBA

1 Answers1