Deleting Columns

[PE New User]

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:

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

 

[NewOrderFac33]

How about:

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]

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

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]

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!

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

 

[Peter_SSs]

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!

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]

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

Thanks again
Karen

 

[NewOrderFac33]

Peter - thanks for the useful tip.

PE - thanks for the feedback!

Pete