CODEWITHSUNDEEP
vbaexcel
Antti
Antti

Reputation: 275

VBA Excel delete multiple rows

This works 

  mySheet.Rows(CStr(activeRow) & ":" & CStr(activeRow + deletedRowAmount)).Delete

This does not work

mySheet.Columns(CStr(columDelete) & ":" & CStr(columDelete + deletedRowAmount)).Delete

What I am missing here?

ERROR is VBA Runtime Error 1004 “Application-defined or Object-defined error”

Upvotes: 1

Views: 1735

Answers (2)

Vityata
Vityata

Reputation: 43585

In general, what Scott Craner says is quite enough, but here is a way to go around it and make it easier:

Sub TestMe()

    Columns(GetColumnRange(2, 5)).Delete

End Sub

Public Function GetColumnRange(colStart As Long, colEnd) As String

    GetColumnRange = Split(Cells(1, colStart).Address(True, False), "$")(0) & _
              ":" & Split(Cells(1, colEnd).Address(True, False), "$")(0)

End Function

Try the TestMe function, it should delete the columns from 2(B) to 5(E).

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152505

Columns are A,B,C,... when used in a string as you are using them. If they are numbers you will need to do it slightly different:

With mySheet    
    .Range(.cells(1,columDelete), .cells(1,columDelete + deletedRowAmount)).EntireColumn.Delete
End With

Upvotes: 3

Related Questions