How To Delete Column S to Last Column?

[DataBlake]

I have this code and its not working, can anyone explain to me why?

sub test()
Dim lastCol As Long

with activesheet
    lastCol = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
    Columns(17 & ":" & lastCol).Delete Shift:=xlToLeft
end with

or

sub test()
Dim lastCol As Long

with activesheet
    lastCol = .Range("A1").SpecialCells(xlCellTypeLastCell).Column
    Range(0, 17 & ":" & lastCol).Delete Shift:=xlToLeft
end with

the only alternative i can think of is change lastCol to a string and use

range("T:" & lastCol)

 

[Fluff]

Are you trying to delete col S onwards, col Q on wards or col T onwards?

 

[MARK858]

Try...

Range(Cells(1, "S"), Cells(1, Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column)).EntireColumn.Delete

 

[DataBlake]

Are you trying to delete col S onwards, col Q on wards or col T onwards?

(including) T and onwards. I see where i was being confusing, apologies

 

[DataBlake]

Try...

Range(Cells(1, "S"), Cells(1, Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column)).EntireColumn.Delete

that worked as intended.
could you explain how this method works as opposed to rowcount method for finding last row?

 

[Fluff]

Either as Mark has shown, or

Sub test()
Dim lastCol As Long

    lastCol = Range("A1").SpecialCells(xlCellTypeLastCell).Column
    Range("T1", Cells(1, lastCol)).EntireColumn.Delete Shift:=xlToLeft
End Sub

 

[DataBlake]

Either as Mark has shown, or

Sub test()
Dim lastCol As Long

    lastCol = Range("A1").SpecialCells(xlCellTypeLastCell).Column
    Range("T1", Cells(1, lastCol)).EntireColumn.Delete Shift:=xlToLeft
End Sub

This works as intended and i see what the difference was, thank you

 

[MARK858]

that worked as intended.
could you explain how this method works as opposed to rowcount method for finding last row?

Find works backwards (due to xlprevious) from the first cell in a zig-zag pattern until it finds the last cell so in practice it works backwards from the last cell on the sheet(or a range if set) until it finds the last cell.

Rows.count (in your case because you are dealing with columns it would be Columns.Count) works backwards from the last cell in the specified row or column.

So the difference is with .Count you need the specific Row/Column and it only finds the last cell in that specific Row/Column.

The Find method when used with Cells finds the last Row/Column no matter which Row/Column is longest.

I never use SpecialCells(xlCellTypeLastCell) as (because it uses the usedrange) it has a nasty habit of retaining a memory and also picks up formats as well as values.