How to delete columns of a range Excel VBA

Question

I have a range named "Start" located at cell "I21". And I have another range named "End" located at cell "Q21". I want to write a code to delete all the columns between them. In other words, I want to delete columns J,K,L,M,N,O,P completely. Here is the code I have:

with ThisWorkbook.sheets("Sheet1")
    'unprotect sheet
    .Columns(.Range("Start").Column+1 & ":" & .Range("End").Column-1).Select
     Selection.Delete Shift:xlLeft
End with

when it comes to the first line .Columns... it gives me an error as undefined application. please help,

shahkalpesh · Accepted Answer · 2010-08-28 20:35:13Z

7

Range(Range("start").Offset(,1), Range("end").Offset(,-1)).EntireColumn.Delete

answered Aug 28, 2010 at 20:35

shahkalpesh

33.6k4 gold badges66 silver badges89 bronze badges

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

Comments

Fandango68 · Accepted Answer · 2019-03-21 05:10:14Z

0

You don't have to specify a row reference...

ActiveSheet.Range(, MyColumn).EntireColumn.Delete

Where 'myColumn' is an arbitrary reference to a column number or anything you want.

answered Mar 21, 2019 at 5:10

Fandango68

4,9555 gold badges50 silver badges88 bronze badges

Comments

user1448329 · Accepted Answer · 2012-06-25 05:43:28Z

-1

    Dim xlsRange As Excel.Range

    xlsRange = xlsSheet.Range("i2", "i10")

    xlsRange.EntireColumn.Delete()

answered Jun 25, 2012 at 5:43

user1448329

1

Collectives™ on Stack Overflow

How to delete columns of a range Excel VBA

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related