Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

**ITY** · 07-23-2019, 06:14 AM

Hi,

I will appreciate your help in some simple action I want to do in VBA:

I have a dynamic table with changing amount of columns and rows.

For some of the columns I have data only in their first row (row#2) that I need to duplicate to the other rows of the columns (respectively)
till last row of the table (reference can be column A).

I'm trying to use the Auto Fill / Fill Down options (copy values type), but missing something to perform this action successfully.

*** Comment: Since it's a dynamic table, I can't use fixed ranges like D2:H2 or D2:H.
I need to use the Headers names of these columns, which are unique, as a reference.

Attached is an example.

Can you please recommend how to write a simple code for this need?

Thanks in advance

**Olly** · 07-23-2019, 06:31 AM

Use:

Please Login or Register  to view this content.

**Olly** · 07-23-2019, 06:39 AM

Alternatively, format your source data as a table, then use Power Query (Get & Transform Data):

Please Login or Register  to view this content.

**Sintek** · 07-23-2019, 06:40 AM

..................deleted................
Olly solved....

**ITY** · 07-23-2019, 07:27 AM

Thanks Olly for your quick response and solution, but it is not good for me... I must clarify that I have a lot of blank cells across all the table, here and there, and your macro fills them with not needed data and also with #REF.

I need the AutoFill or FillDown macro to be activated ONLY on the columns I marked in yellow, which I should refer to them according to their unique names (Header#4 ---> Header #8), not their ranges because they change.
and macro should refer to the 1st TEXT row (row#2) as the "MODEL" for duplication down to the empty cells, in each column respectively, till last row of the table.

I hope it is clear now.

Thanks.

**ITY** · 07-23-2019, 07:29 AM

Thanks for this solution as well. As for now, due to technical issues, I prefer using a regular Excel/VBA solution without dealing with power query.

**Sintek** · 07-23-2019, 07:38 AM

Please Login or Register  to view this content.

EDIT...
Or perhaps....Seen as it is specific to column name....

Please Login or Register  to view this content.

**Olly** · 07-23-2019, 07:39 AM

What technical issues do you have with Power Query? Or is it just that you're unfamiliar with it?

Either way, it will be easier to make reference to your columns, if you format your data as a table. Then you could use something like:

Please Login or Register  to view this content.

This lets you specify which column headers you wish to deal with.

**ITY** · 07-23-2019, 08:05 AM

Thanks Olly. Regarding using Power Query (which indeed I'm not much familiar with...), you said that for that I have to convert my source data to a table.
It's really seems to be a good solution, but my final product must be in a regular table format.
So I think it is better to find other solution instead of converting twice...

**ITY** · 07-23-2019, 08:17 AM

sintek, Thanks a lot! Your second solution works perfect for me. Really appreciate your help

**Olly** · 07-23-2019, 08:23 AM

I'm intrigued about your reluctance to use Tables.

It's not just you, there's lots of users around who are still reluctant to format data in Tables, and to use some of the newer, more powerful Excel techniques.

I don't really understand why?

**ITY** · 07-23-2019, 08:57 AM

I prefer using tables, but for this specific need, that I must have regular table to upload to some system that doesn't recognize the table format and reject it...

**Sintek** · 07-23-2019, 09:57 AM

sintek, Thanks a lot! Your second solution works perfect for me.

Pleasure...Thanks for rep +

Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

LinkBack

Thread Tools

Rate This Thread

Display

Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

Re: Duplicate data for multiple cells by Auto Fill / Fill Down - Dynamic Table

Thread Information

Users Browsing this Thread

Similar Threads

Fill arrays from sheet / SUMIFS / create & fill table based on multiple criteria

Skip x cells and fill (Fill/create weekly average from 7 days and fill down)

Match Data and Auto Fill Multiple Cells

Dynamic Table Auto Fill Help "Inconsistent Calculated Column Formula"

Auto Fill cells in a Column. Fill down from preceeding cell, using a formula.

Auto fill table based on the data from another workbook

auto fill data into a cell from a lookup table

Bookmarks

Bookmarks

Posting Permissions