Autofill with vba

I have a spreadsheet that someone gave me. It has formulas in columns A thru J and data in columns K thru Y.
I would like to be able to autofill columns A thru J when the amount of rows in columns K thru Y changes (gets overwritten).

So far all I have is the following which just selects the last row of columns A thru J.

     Cells(Rows.Count, 1).End(xlUp).Offset(0).Select
     Range(Selection, Selection.End(xlToRight).Offset(0, 1)).Resize(1, 10).Select

How do I get the autofill part? Or, if there is a different way than autofill, that's fine too.


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
Post this line.. You can simply do this...

Selection.Value= " Whatever value you want to fill here"

Open in new window

not a lot to go on here but if you are using later versions of excel (2010 and above) you can simply select the whole data set and 'insert, table' and your data set will be converted to a 'table'.

Tables (search help for background) look the same as a normal datasheet but have some extra functions - especially interesting for you would be the fact that formulae are automatically filled when new rows are added

If this is not what you meant please attach an example! - this is a noddy view to show you what I mean, fill in the first few columns on a row and the table adds it and copies the formula in the last column
dhausnerAuthor Commented:
Not following you. What exactly would I put in where you wrote, "" Whatever value you want to fill here"
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Saurabh Singh TeotiaCommented:

What i understand you have a selection already made you want to fill value their? If that's what you are not trying to do then can you please help me understand with your example file what you are trying to do as it will help me to provide a better solution for you right their and then..

dhausnerAuthor Commented:

Say column A thru J all have vlookup formulas (that utilize some values from columns K thru Y).
Say the whole worksheet has 10 complete rows. Later on, someone will overwrite the data in columns K thru Y (making those columns have more (or possibly less) than 10 rows. So for argument sake, the file started out with 10 rows. Then, columns K thru Y were overwritten and now have 20 rows. I would like to autofill the formulas in columns A thru J down to row 20.
Saurabh Singh TeotiaCommented:
Now in order to autofill..I want to understand the what is the formula you have that you want to fill?? Also if you can give me the file along with the formula that you are applying..It will help me to design a solution for you accordingly..

@dhausner - Saurabh will be able to come up with a VBA solution but unless you are doing something besides autofill VBA is unnecessary, tables will achieve what you requested
dhausnerAuthor Commented:
Can't use tables. This guys spreadsheet is formatted with columns and headers in different colors and all sorts of other junk where a table (for him) wouldn't do.

I've attached a very simple 2 & 2 column example with notes.
Saurabh Singh TeotiaCommented:
I don't see the file here..Can you please upload the file again...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dhausnerAuthor Commented:
The file doesn't seem to want to upload but that doesn't matter. I found one of your previous solutions to this same issue:

 lrow = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Selection.AutoFill Destination:=Range("A2:J" & lrow)
Range("A2:J" & lrow).Select

Thanks you for your help (both now and in the past!!!)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.