Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Auto Fill Issue Numbers 1 - 70000 repeated each 30 times

0

I am trying to get the spreadsheet to auto fill a sequence of numbers starting with 1 and ending with 70000. Each number should repeat 30 times as counted out in column c and then change to the next number in sequence for a total of 2.1milion lines.

I have tried to highlight and drag but it does not autofill the correct sequence is there anyway to do this?

Thanks

Answer
Discuss

Answers

0

Your version of Excel has only around 65,000 rows so it won't work in a single column. The new versions of Excel, from 2007 and up only have a little over 1 million rows, so you will need at least two columns in there.

However, you can increment a number like you want.

First, input 1 in the first cell.

Second, input this formula in the next cell below the first one:

=IF(MOD(ROW()-1,30)=0,C1+1,C1)

I assumed you put the 1 into cell C1 and the formula into cell C2.

Copy the formula down as far as you would like or are able to.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login