VBA Excel delete multiple rows

Question

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”

What are the values of the variables? What are they declared? What is the error you are getting? — Scott Craner
– Scott Craner, Commented Sep 12, 2017 at 14:14

Scott Craner · Accepted Answer · 2017-09-12 14:18:23Z

3

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

answered Sep 12, 2017 at 14:18

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

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

1 Comment

Antti Over a year ago

Thanks Mate. Very useful piece of code. As a newbie it is hard to understand VBA illogical syntax.

Vityata · Accepted Answer · 2017-09-12 14:45:16Z

1

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).

answered Sep 12, 2017 at 14:45

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

5 Comments

Scott Craner Over a year ago

As this is maybe easier to understand, the fact that vba will need to parse the resultant string back to numbers it will add time, albeit a fraction of a second. This is like changing your cars DC output to AC just to convert it back to DC to charge your phone. If done in a loop it will make a difference in performance.

Vityata Over a year ago

@ScottCraner - yup, a few fractions of a second could be lost.

Scott Craner Over a year ago

It was just a FWIW comment, the OP may be willing for the time payoff if it is easier to understand and use for them.

Vityata Over a year ago

@ScottCraner - I know :)

Antti Over a year ago

Thank you for your proposal, but I have so many methods already in place, so not convenient to make new ones :-)

Collectives™ on Stack Overflow

VBA Excel delete multiple rows

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related