CODEWITHSUNDEEP
vbaexcel
Aabo
Aabo

Reputation: 213

Delete rows after last cell with data in a column

I want to figure out a code to delete all the rows after the last row of my copied data in column "C".

So let's say my data in column C ends at row 1265, the code is suppose to delete all the rows after that..

Thanks

Upvotes: 0

Views: 6857

Answers (1)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9966

You may try something like this...

Sub DeleteRows()
Dim LastRowColC As Long, LastRow As Long
LastRow = ActiveSheet.UsedRange.Rows.Count
LastRowColC = Cells(Rows.Count, "C").End(xlUp).Row
If LastRow > LastRowColC Then
    Rows(LastRowColC + 1 & ":" & LastRow).Delete
    'OR
    'Rows(LastRowColC + 1 & ":" & LastRow).Clear
End If
End Sub

If the data is in an Excel Table, try it like this...

Sub DeleteRows()
Dim LastRowColC As Long, LastRow As Long
Dim tbl As ListObject
Set tbl = ActiveSheet.ListObjects(1)
LastRow = tbl.DataBodyRange.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastRowColC = tbl.DataBodyRange.Columns(3).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
If LastRow > LastRowColC Then
    Rows(LastRowColC + 1 & ":" & LastRow).Delete
End If
End Sub

Upvotes: 4

Related Questions