End of Column Entry Autofill 3

[vixvee]

Hello,

Can anybody help me do autofill of column upto last entry adjacent to it?

1 entry1
2 entry2
3 entry3
: :
: :
N entryN

Where N is not known and can be any number and the entries can autofill upto this N column.

Thanks,
vix

 

[MeGustaXL]

Hi Vixvee,

Let's say you have the numbers 1, 2, 3....N in column A, starting at A8, and "Entry1" in B8.

Manual Method:

Select B8, hover the mouse pointer over the bottom-right corner of the cell border (black square) until the arrow turns to a +, double-click et Voila!

But you knew that already, of course!

VBA Method

Name the column of numbers from A8 to A(N) as "Data"
Name cell B8 "FillStart"
Attach this code to a button:

[FillStart].AutoFill Destination:=[Data].Offset(0, 1)
    [FillStart].Select

Smile broadly and continue Excel-ing!

Hope this is what you need



Chris

Varium et mutabile semper Excel

 

[vixvee]

Thanks! Yep this is perfect!

 

[DaleWatson123321]

Chris,

Good one ...earns you another STAR.

Thanks.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[wwgmr]

Chris A learners question where would you put that line of code? Would it be the worksheet activate? or selectionChange even?


Thanks!

 

[vixvee]

Hi Chris, A follow up question, How can I do this for the code to know upto which row it will go? For example, Data size is variable. How can I make the autofill upto only the cell wherein there is the data on the cell next to it?
A(N) size is unknown...
Thanks,
vix

 

[MeGustaXL]

Thanks for the STAR Dale, although I suppose it should be a SLAP for that "[FillStart].Select"!

wwgmr:
It depends; you could put it in the CommandButton_Click() event as I suggested, or have it in the SelectionChange() if you wanted it to run every time you moved about the sheet. If it was filling a load of formulas down, you'd probably want it at the end of whatever routine you were using to put the numbers in column A.

Vix:
Define "Data" as a Dynamic Named Range. Assuming your data start in A8, and A1:A7 are all empty, try putting this in the "Refers To" box:

=OFFSET($A$8,0,0,COUNT(A:A))

Now whenever you append another number to the bottom of the list, Data will expand automatically.

I'm sure that Skip, Dale, xlbo or somebody has done a FAQ about Dynamic Ranges, but I'm darned if I can find it
Maybe if they read this, one of 'em will point us to it?

Hope this helps you guys!


Chris

Varium et mutabile semper Excel

 

[MeGustaXL]

Still can't find that FAQ

In the meantime, Chip Pearson gives all the examples you need to make you a Named Range Guru right here:

http://www.cpearson.com/excel/named.htm

Chris

Varium et mutabile semper Excel

 

[MeGustaXL]

Hi Vixvee & wwgmr (And anyone else interested)

I've been thinking a bit more about this, and Skip's lectures about not selecting or referencing whole columns as in =OFFSET($A$8,0,0,COUNT(A:A))

This is B-A-D, because Excel immediately assigns 65536 cells to that range, just in case you need them sometime. Do this for a few dozen columns and you've got a 5 Meg workbook!

Make the Data range dynamic as you work like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    Set Data = Range([TopofList], [TopofList].End(xlDown))
    [FillStart].AutoFill Destination:=[Data].Offset(0, 1)
End If
End Sub
[code]

Name the first data cell in column A as "TopofList" and the first cell containing your formula to be filled as "FillStart" and any time you change one of the values in the list, or add another at the end, the formulas will fill down automatically.

Hope this helps (and keeps Skip happy [wink])


Chris

[b][i]Varium et mutabile semper Excel[/i][/b]

 

[wwgmr]

Thanks Chris,

I am wondering as I think that the offset was used in giving us our Named range. How could you use that to make your named ranged dynamic so that if you add something to the list it is added the way the offset(****,counta(a:a)) is?

As a second question where you put the
[TopofList].End(xlDown)in this line below
Set Data = Range([TopofList], [TopofList].End(xlDown))

Could you instead of using xldown use a named cell that had number value? So if you wanted to only offer say 5 names from your drop down list you could?

Also When I tried this code I named a1 on blank sheet TopofList and then in column C I named "C1" as FillStart I then copied and pasted your code into the
Private Sub Worksheet_Change(ByVal Target As Range)

I get nothing in the FillStart cell or below.
Also when I try to add something to the list under TopofList I get MSVB error
Run-Time error '1003':
Autofill method of Range class failed.
The code is having problem with this line

[FillStart].AutoFill Destination:=[Data].Offset(0, 1)

Did anyone else have this problem?

Thanks!

 

[MeGustaXL]

Hi wwgmr, and apologies for not replying sooner, but I’ve been out of the country for a week or so. In case you haven’t already found answers to your questions, here are some for you to consider:

Q1. How could you use that to make your named ranged dynamic so that if you add something to the list it is added the way the offset(****,counta(a:a)) is?

A. Using my code does this automatically for you, without the memory overhead associated with referencing an entire (but mostly empty) column.

Q2. Could you instead of using xldown use a named cell that had number value? So if you wanted to only offer say 5 names from your drop down list you could?

A. You could do this by creating another named range, say “DataList”, dimensioned using Refers To:
=OFFSET($A$1,0,0,NumberOfItems). Just replace NumberOfItems with 5, or however many items you want to appear in the list, then populate your dropdown with DataList.

Q3. When I tried this code I named a1 on blank sheet TopofList and then in column C I named "C1" as FillStart.

A. AutoFill only works if the column to be filled is immediately to the right of the “Master” column. Just move FillStart to B1 and all will be well.

Please let me know if it all works for you now.


Chris

Varium et mutabile semper Excel