I have( with help of this forum) written a macro in which a range of columns
is selected and deleted depending on the amount of worksheets in the workbook.
The VBA code is:
Dim result As Long

result = Application.InputBox("Enter Number of Days which are in this
report( highest number of worksheets in document", "BPC Performance
Calculator")

Select Case result
Case 1
Columns("T:CJ").Select
Case 2
Columns("U:CJ").Select
Case 3
Columns("V:CJ").Select
Case 4
Columns("W:CJ").Select
Case 5
Columns("X:CJ").Select
Case 6
Columns("Y:CJ").Select
Case 7
Columns("Z:CJ").Select
Case 8
Columns("AA:CJ").Select
Case 9
Columns("AB:CJ").Select

Etc etc

Case 68
Columns("CI:CJ").Select
Case 69
Columns("CJ:CJ").Select
Case 70
Columns("CZ:CZ").Select
Case Else
MsgBox "please enter a valid number", , "BPC Performance
Calculator"
Exit Sub

End Select
Selection.ClearContents
Range("A1").Select


Now this works perfect, unless if there is only 1 worksheet in the file(
Case 1). In this situation columns S:CJ are selected and deleted. I do not
understand and can't figure it out.
Does anyone have an idea?
if needed I can send the file by email.

thanks.

Chris

don't know about the #1 case, but you could shorten/simplify your code using
something like:

activesheet.range(Cells(1,19+result),cells(1,88)). entirecolumn.clearcontents

(assuming your case 70 was a typo)

Tim


"Chris D" wrote in message
...
I have( with help of this forum) written a macro in which a range of
columns
is selected and deleted depending on the amount of worksheets in the
workbook.
The VBA code is:
Dim result As Long

result = Application.InputBox("Enter Number of Days which are in this
report( highest number of worksheets in document", "BPC Performance
Calculator")

Select Case result
Case 1
Columns("T:CJ").Select
Case 2
Columns("U:CJ").Select
Case 3
Columns("V:CJ").Select
Case 4
Columns("W:CJ").Select
Case 5
Columns("X:CJ").Select
Case 6
Columns("Y:CJ").Select
Case 7
Columns("Z:CJ").Select
Case 8
Columns("AA:CJ").Select
Case 9
Columns("AB:CJ").Select

Etc etc

Case 68
Columns("CI:CJ").Select
Case 69
Columns("CJ:CJ").Select
Case 70
Columns("CZ:CZ").Select
Case Else
MsgBox "please enter a valid number", , "BPC Performance
Calculator"
Exit Sub

End Select
Selection.ClearContents
Range("A1").Select


Now this works perfect, unless if there is only 1 worksheet in the file(
Case 1). In this situation columns S:CJ are selected and deleted. I do not
understand and can't figure it out.
Does anyone have an idea?
if needed I can send the file by email.

thanks.

Chris