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-13 16:30:04Z

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, 2012 at 16:30

answered Aug 13, 2012 at 16:21

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Scott Conover Over a year ago

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?

Siddharth Rout Over a year ago

Add this line If LastCriteriaCell < 2 then Exit Sub after this LastCriteriaCell = .Range("A" & Sheets("Criteria").Rows.Count).End(xlUp).Row

Scott Conover Over a year ago

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.

Siddharth Rout Over a year ago

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

Scott Conover Over a year ago

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 provides the full code. However, I think your solution is more elegant and likely less intensive with large sets of code in VBA.

Collectives™ on Stack Overflow

Curious Delete Behavior with Command Button VBA

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related