Forum Discussion
VBA for autofill
Hi Wizards,
Let me first preface this by saying; I know nothing about coding. But in my experience, when you feel there can be a more efficient way to do something, there usually is one.
I am seeking a way to do two things:
1) Populate n number of columns with the same number.
Ex. Populate A2 - A16 with the number 1.
2) Populate n number of columns with a number range.
Ex. B2 - B16 with numbers 1 - 15.
I work with arrays of points, so I want to be able to attribute them as belonging to column 1, and numbered north to south in that column.
Imagine an array with 32 columns and 15 points in each...That is a lot of copying and pasting.
Any help with this will be greatly appreciated.
Thanks in advance 🙂
Mucho
16 Replies
Mucho_Elanstrand How's this:
Sub BuildArray() Dim i As Long Dim x As Long Dim y As Long x = Application.InputBox("How many columns do you want to fill?", "Column Count", 1, Type:=1) y = Application.InputBox("How many rows do you want to fill?", "Row Count", 1, Type:=1) Range("A2:A16").Value = 1 For i = 1 To y Range(Cells(i, 2), Cells(i, x)).Offset(1).Value = i Next i End SubFor a bigger picture, I am dealing with points that exist in real life as my sketch below.
So in excel, i'd like to categorize them in adjacent columns as shown in the excel screenshot also attached.
Categorizing the data in two columns for all points in the array. If we can pull this off it will speed up my workflow tremendously.
- OK, so in this case you want to create a repeating array based on the lower/upper boundaries in the outside column? I can see the row pattern for columns A & B, but how many columns should be created, and do they follow the same pattern?
Hi Smitty,
The code works! However I need a little modification. As it stands, it populates rows 2 - 16 of column A and B with the numbers 1-15 as in the first screenshot (A). What I need however, is what it looks like in the second screenshot (B). How would I accomplish that? 🙂 Thank you, sensei!
AB
Mucho_Elanstrand With the code I posted, if you were to enter 10 (columns) & 15 (rows), you'd get this:
Isn't that what you were after?
Also, you might want to add this line of code to clear out any previous entries:
ActiveSheet.UsedRange.Offset(1).ClearContents
