# Thread: Auto-filling a Series

1. ## Auto-filling a Series

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!  Reply With Quote

2. 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/ex...010222142.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.  Reply With Quote

3. 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")
'get ready for next day
cDa = cDa + 1
Next cel

End Sub```
I put this code into the sheets module, hope it's of some use, just an idea.  Reply With Quote

4. 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  Reply With Quote

#### Tags for this Thread

auto-fill, series #### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•