Try this - notice the new Const value - change that to whatever phrase you
decide to use at the end of each sequence. In the code it needs to be all
UPPERcase, although it doesn't matter how you type it in on the worksheet.
Also, the last sequence (as the 201-250 so far) needs to end with that
phrase. This code will handle the entries whether they're real numbers or
numbers as text, and there is optional code in it, now commented out to add
new numbers to the sequence as numbers as text vs real numbers.

Sub InsertNewColumnsAndMissingValues()
'two series involved: 101-250 and 201-250
'routine presumes no blank cells in series
'although obviously there are missing values
'

Dim LowerLimit As Integer
Dim UpperLimit As Integer
Const EndOfSeriesPhrase = "TOTAL" ' use all caps in here

'deal with first series
Range("C1").Select
LowerLimit = 101
UpperLimit = 150
If ActiveCell < LowerLimit Then
ActiveCell.EntireColumn.Insert
ActiveCell = LowerLimit
' or for number as text
'ActiveCell = "'" & Trim(Str(LowerLimit))
End If
Do Until UCase(Trim(ActiveCell)) = EndOfSeriesPhrase
If Val(ActiveCell.Offset(0, 1)) < Val(ActiveCell) + 1 Then
ActiveCell.Offset(0, 1).Activate
ActiveCell.EntireColumn.Insert
ActiveCell.Value = Val(ActiveCell.Offset(0, -1)) + 1
'if you need to put it in as text, use this instead
' ActiveCell.Value = "'" & Trim(Str(Val(ActiveCell.Offset(0, -1)) +
1))
If UCase(Trim(ActiveCell)) = EndOfSeriesPhrase _
Or ActiveCell = UpperLimit Then
Exit Do
End If
Else
ActiveCell.Offset(0, 1).Activate
End If
Loop
'deal with second series
ActiveCell.Offset(0, 1).Activate
If UCase(Trim(ActiveCell)) = EndOfSeriesPhrase Then
ActiveCell.Offset(0, 1).Activate ' move over 1 more column
End If
'*****
LowerLimit = 201
UpperLimit = 250
'in case series goes like
' ...149 150 202 203...
If Val(ActiveCell) < LowerLimit Then
ActiveCell.EntireColumn.Insert
ActiveCell = LowerLimit
' or for number as text
'ActiveCell = "'" & Trim(Str(LowerLimit))
End If
Do Until UCase(Trim(ActiveCell)) = EndOfSeriesPhrase
If Val(ActiveCell.Offset(0, 1)) < Val(ActiveCell) + 1 Then
ActiveCell.Offset(0, 1).Activate
ActiveCell.EntireColumn.Insert
ActiveCell.Value = Val(ActiveCell.Offset(0, -1)) + 1
'if you need to put it in as text, use this instead
' ActiveCell.Value = "'" & Trim(Str(Val(ActiveCell.Offset(0, -1)) +
1))
If UCase(Trim(ActiveCell)) = EndOfSeriesPhrase _
Or ActiveCell = UpperLimit Then
Exit Do
End If
Else
ActiveCell.Offset(0, 1).Activate
End If
Loop
ActiveCell.Offset(0, 1).Activate
If UCase(Trim(ActiveCell)) = EndOfSeriesPhrase Then
ActiveCell.Offset(0, 1).Activate ' move over 1 more column
End If
'*****
'if you have more series after 201-250
'just copy the code segment between '***** above and
'paste below and change LowerLimit and UpperLimit values
'repeat for all series you have

End Sub


"Arain" wrote:

What if the numbers are saved in text and the series end with word Total

for example c1 has 101 and ends at 142 then word total comes. then series
201 starts. Is there a way to implement that. Also the numbers are stored as
text should i convert them or is there a way around it.

I know i am making these changes because my boss is making these changes.

thanks

"Arain" wrote:

I have a sheet where the C1 has value 101 and goes to AF till 142. Its
missing values in between like 112 and 117. I want to add the column for the
missing value in that sheet. can someone please help. thanks.