# Auto-filling a Series

#### forgameslive

##### New member
Hi,

I am trying to automatically get the following series filled in but have failed thus far:

1,2,3,4,5,6,7,11,12,13,14,15,16,17,21,22,23....... (after 7, it skips 3 digits)

Similarly,

Excel has the "Fill Weekdays" option. However, how do I also remove Friday along with Saturday and Sunday from the dates of a year?

Any help would be much appreciated!

Thanks!

#### JeffreyWeir

##### Super Moderator
Staff member
How far do you need the series to extend? You can achieve the 'skipping dates' thing by using custom lists. See http://office.microsoft.com/en-nz/e...for-sorting-and-filling-data-HA010222142.aspx

But i don't think it will work for your number sequence question. Although you can use a formula.
Put this in row 1 and drag down:
=INT(ROW()/7-1/7)*10+IF(MOD(ROW(),7)=0,7,MOD(ROW(),7))
Then you could copy and paste special as values.

#### NoS

##### New member
You could achieve this with macros.

For the numbers something like this
Code:
``````Sub Skip890()
Dim rng As Range
Dim cel As Range
Dim cNum As Long

'where ever you are putting the numbers
Set rng = Range("C3:C38")

'put the number you want to start at in cell A2
cNum = Range("A2").Value

For Each cel In rng
cel.Value = cNum
cNum = cNum + 1
If Right(cNum, 1) = 8 Then
cNum = cNum + 3
End If
Next cel

End Sub``````

maybe like this for the days
Code:
``````Sub SkipFriToSun()
Dim rng As Range
Dim cel As Range
Dim cDa As Date

'where ever you are putting the days
Set rng = Range("D3:D38")

'put the starting date in cell B2
cDa = Range("B2").Value

For Each cel In rng
'if the day is Friday add 3 days to get Monday
If Format(cDa, "ddd") = "Fri" Then
cDa = cDa + 3
End If

'format this how ever you want it displayed
cel.Value = Format(cDa, "Long Date")
cDa = cDa + 1
Next cel

End Sub``````

I put this code into the sheets module, hope it's of some use, just an idea.

Last edited:

#### JeffreyWeir

##### Super Moderator
Staff member
Here's a formula that handles any series of repeating numbers on the far right from 1 to 9. Just replace X with the number
=INT((ROW()-1)/x)*10+MOD(ROW(),-x)+x