Delete columns - Start End range given in another sheet

[jaime1182]

Hi all

Hope you are all well. I am trying to work my way through a new project requirement. I need to remove columns from a sheet prior to sending it out. However, this range is often a variable. I want to use the value in the B2 and B3 cells to form the range to be used in the macro.

Sub DeleteColumns()
    Dim StartCol As Long
    Dim LastCol As Long

    StartCol = Sheets("Sheet1").Range("B2").Value
    LastCol = Sheets("Sheet1").Range("B3").Value
    
    Sheets("Sheet2").Select
    Columns(StartCol : LastCol).EntireColumn.Delete

End Sub

In other words, if B2 and B3 are 5 and 11 respectively, columns E to K are to be deleted.

However, I am having trouble getting this part to actually work:

Columns(StartCol : LastCol).EntireColumn.Delete

Did I 'dim' it wrongly? Should I be using numbers or should I be using the column names (ie E etc)?

Much thanks in advance

 

[Michael M]

MAybe like this

Sub DeleteColumns()
    Dim StartCol As Integer, LastCol As Integer
    StartCol = Sheets("Sheet1").Range("B2").Value
    LastCol = Sheets("Sheet1").Range("B3").Value
    Sheets("Sheet2").Select
    Range(Columns(StartCol), Columns(LastCol)).Delete
End Sub

 

[bebo021999]

Define range by first cell and last cell, like this:

Range(Cells(1, StartCol), Cells(1, LastCol)).EntireColumn.Delete

 

[jaime1182]

Both solutions are great! Thank you so much everyone! That just saved me a week of headaches and painful eyes!

Much appreciated!

 

[Michael M]

Glad WE could help