Refer to multiple columns to delete last cell in column

Question

I am trying to delete the last cell of two columns. I get the error that the object doesn't support this method. Here is what I have tried:

'     Delete last cell of columns C and E
Worksheets("Sheet1").Columns ("C"), Columns("E").End(xlDown).Cells.Delete

Thanks

See [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) for how to find the last row. — BigBen, Jan 17 '20 at 20:58
Why do you use `EntireRow.Delete`? But you write that you want to _delete the last cell of two columns_ . Why should these two cells be in the same row? Furthermore do you want the content of the cells deleted or do you want to delete the cells. In case you want to delete the cells what should happen to the surrounding cells? Should they be moved to the left or above? Please clarify! — Storax, Jan 17 '20 at 21:05
@Storax Ah yes I did see that I referred to EntireRow, I have changed it to .Cells. However I still receive the same error. I am trying to delete the content of the last cells in the columns as well as the formatting. I do not want any surrounding cells to be deleted, just the one cell that is not required in my worksheet. — patriciajlim, Jan 17 '20 at 21:16

Storax · Answer 1 · 2020-01-17T21:39:52.177

You could use the following function to clear the last cell in a column

 Function ClearLastCell(colLetter As String, Optional ws As Worksheet)

    If ws Is Nothing Then
        Set ws = ActiveSheet
    End If

    With ws
        .Range(colLetter & .Rows.Count).End(xlUp).Clear
    End With

End Function

In your case for column C and E

Sub ClearIt()
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    ClearLastCell "E", ws
    ClearLastCell "C", ws
End Sub

Refer to multiple columns to delete last cell in column

1 Answers1