Just wanted to share as I had huge amount of trouble looking for ways to do this online and have finally gotten it through series of trial and error.
Sheet1.Range(Cells(1, 11), Cells(1, 100)).EntireColumn.Delete
This deletes columns 11 to 100.
2 Answers
With Sheet1
.Range(.Cells(1, 11), .Cells(1, 100)).EntireColumn.Delete
End With
Comments
More ways
Deleting consecutive columns like 1 - 100
Sub Sample()
With Sheet1
'A:CV
.Columns(ReturnName(1) & ":" & ReturnName(100)).Delete Shift:=xlToLeft
End With
End Sub
'~~> Returns Column Name from Col No
Function ReturnName(ByVal num As Integer) As String
ReturnName = Split(Cells(, num).Address, "$")(1)
End Function
Deleting non consecutive columns like 1, 3, 5
Sub Sample()
With Sheet1
'A:A,C:C,E:E
.Range( _
ReturnName(1) & ":" & ReturnName(1) & "," & _
ReturnName(3) & ":" & ReturnName(3) & "," & _
ReturnName(5) & ":" & ReturnName(5) _
).Delete Shift:=xlToLeft
End With
End Sub
Function ReturnName(ByVal num As Integer) As String
ReturnName = Split(Cells(, num).Address, "$")(1)
End Function
'**Another way**
Sub Sample()
Dim Rng As Range
With Sheet1
Set Rng = Union(.Columns(1), .Columns(3), .Columns(5))
End With
Rng.Delete Shift:=xlToLeft
End Sub
1 Comment
Jay
Thanks Sid, although I might just use the one I have posted since it's one-liner and easier for me to understand. For doing this with multiple non-consecutive columns, Union() look's great and I will start using that as I have not heard of this until today :)
Sheet1.Range(Sheet1.Cells(1, 11), Sheet1.Cells(1, 100))