CODEWITHSUNDEEP
vbaexcel
Rowling
Rowling

Reputation: 213

Delete rows from A2 to the end of the column in VBA

I am try to come up a code in VBA the will delete from A2(row 2) to the end of rows

Option Explicit
Sub Ticket()
    Dim a As Long
    Application.Calculation = xlManual

    a = ThisWorkbook.Worksheets("Fleet Report").Range("A2").End(xlDown).Row
    MsgBox "Last Row is " & a

    ThisWorkbook.Worksheets("Fleet Report").Rows("3:a").EntireRow.Delete

End Sub

But I got error message: type mismatch. Does anyone has an idea?

enter image description here

Upvotes: 0

Views: 2876

Answers (2)

L0uis
L0uis

Reputation: 721

Try this:

Sub Ticket()
Dim a As Long
Dim WB As Workbook, WS As Worksheet



Set WB = ActiveWorkbook
Set WS = WB.Worksheets("Fleet Report")
Application.Calculation = xlManual
a = WS.Range("A2").End(xlDown).Row


MsgBox "Last Row is " & a

Upvotes: 0

Matt Gaydon
Matt Gaydon

Reputation: 117

Your line of code for deleting is incorrectly written. It reads:

ThisWorkbook.Worksheets("Fleet Report").Rows("3:a").EntireRow.Delete

When it should say:

ThisWorkbook.Worksheets("Fleet Report").Rows("3:" & a).EntireRow.Delete

Also to avoid row and column numbers, which I personally am not a fan of, you could format it as:

ThisWorkbook.Worksheets("Fleet Report").Range("A3:A" & a).EntireRow.Delete

Hope this helps!

Upvotes: 2

Related Questions