Deleting Columns

PE New User

New Member
Joined
Aug 4, 2014
Messages
21
Hi,

I am trying to do something that I thought would be quite simple, but seem to keep stumbling.

I have a spreadsheet of multiple columns and am copying and pasting text from one tab to another, but in the new tab I only need a certain number of columns. I have written the syntax to delete most of the columns, but am coming unstuck with trying to get rid of the last columns to the right of the data.

I can find the column that I need to remove (using Cells.Find) and highlight the entire column, but cannot get the syntax right to select all further columns to the right. Have tried the following, but can someone point out where I have gone wrong or suggest another way of achieving the desired result:

Code:
Cells.Find(What:="kVa Rate(£/kVA)", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Select
ActiveCell.EntireColumn.Select
Range(ActiveCell, Column.End(x1ToRight)).Select
Selection.Delete
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,252
How about:
Code:
Cells.Find(What:="kVa Rate(£/kVA)", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Select
Range(Cells(1, Selection.Column), Cells(1, 16384)).EntireColumn.Delete
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,202
Office Version
  1. 365
Platform
  1. Windows
Code is generally quicker if you don't actually 'select' things on your worksheet. Here's one way (which will also work in older Excel versions).
Rich (BB code):
Sub Del_Cols()
  Dim lastCol As Long, myCol As Long
  
  myCol = Cells.Find(What:="kVa Rate(£/kVA)", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column
  lastCol = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlValues, _
    SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, SearchFormat:=False).Column
  Columns(myCol).Resize(, lastCol - myCol + 1).Delete
End Sub
 

NewOrderFac33

Well-known Member
Joined
Sep 26, 2011
Messages
1,252
Peter,

I stand corrected, but I just tested this and it also works (although I admit it deletes ALL columns to the right, not just those that actually contain something).

I guess that's why you have 16 times as many posts as I do! :)

Code:
Range(Cells(1, Cells.Find(What:="kVa Rate(£/kVA)", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
            SearchFormat:=False).Column + 1), Cells(1, 16384)).EntireColumn.Delete

Pete
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,202
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Peter,

I stand corrected, but I just tested this and it also works (although I admit it deletes ALL columns to the right, not just those that actually contain something).

I guess that's why you have 16 times as many posts as I do! :)

Code:
Range(Cells(1, Cells.Find(What:="kVa Rate(£/kVA)", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, _
            SearchFormat:=False).Column + 1), Cells(1, 16384)).EntireColumn.Delete

Pete
I don't think deleting extra columns is a problem, but note that your code would fail if run in versions earlier than 2007 because there would not be 16,384 columns. Easily fixed though by replacing that hard-coded number with Columns.Count :)
 

PE New User

New Member
Joined
Aug 4, 2014
Messages
21
Hi Peter & Pete,

Thank you both so much for taking the time to respond to my question - I have used Peters version which will be more robust as I may have to (believe it or not) use my macro within a 2003 version of Excel due to the output for reports from the operating system on a new system we are moving over to!!! Not ideal :mad:

Thanks again
Karen
 

Watch MrExcel Video

Forum statistics

Threads
1,108,806
Messages
5,524,983
Members
409,613
Latest member
Dalex100

This Week's Hot Topics

Top