VBA autofill variable column same row

[kmoore]

I have created a list drop-down that I want to repeat on the same row, but the number of columns it reapeats will vary. Below is the code I would like to fill right.

Please Login or Register  to view this content.

The columns will vary based on the code below.

Please Login or Register  to view this content.

Thanks for your help!

[barryleajo]

Hi kmoore
Try the attached
I think it does what you require although dealing with dates can be a bit fraught!
Put your dates in cells B6 and B7 as per your spec and run macro called "DateSeriesVal"
Note that I have modified your second macro slightly
barry

[kmoore]

Thanks Barry, you are genius!!! That worked perfectly. I'm new to the site and I hope its not improper ettiquet to ask you another questions. Would you be able to help me figure out how to put the date in a merge of two cells?

I have re-attached.

Thank you again so much!!

[barryleajo]

Hi kmoore
Asking supplementary questions on the same topic is OK.
It is not quite so good etiquette to post duplicate posts onthe same topic.
Your new thread Fillright (below) would seem to be a duplicate of this one!
Leith has provided a far more elegant solution so you do have a coupe of options now.

I would personally avoid merged cells - they tend to give grief.
Your modified example illustrates this perfectly.
That said, if you specify the first cell in the merged range you can enter a value "as normal".

You can achieve the same formatting effect without merging with:

Please Login or Register  to view this content.

Again specify the first cell in the "centred" range in which to place your value.
Glad you are making progress.
barry

[kmoore]

Thanks Barry. The reason I wanted to merge cells is because I need to date to appear above two columns. If I do not merge, I need to skip a cell in between. Basicall the first date should appear in B6 and C6 (merged), the second date in D6 and E6 (merged), and so on. Is there a better way to accomplish this without merging? When I center, it just centers in the current cell. All of the dates appear in subsequent cells, which is not what I need.

Thanks again.

[barryleajo]

HI kmoore
Yes - its called Center Across Selection
In VBA see the code snippet in my post 4 above.
Or if you just want to do the same by formatting the worksheet directly do the following:
Select the cells - in your example B6 and C6 together (but it works for any number of cells)
Go to the Home menu
In the Cells group choose Format
Select Format Cells..
Select the Alignment Tab
In the Text Alignment Horizontal drop down choose Center Across Selection.

Presumably you can't just increase the width of the columns?

[kmoore]

Hey Barry,

I follow now. I read up on Center Across Selection, but I do not know why its not doing what I expect. It just centers across the current cell, not two cells like I would prefer. Please see attached. I added

Please Login or Register  to view this content.

to the spreadsheet you uploaded. I have also included what I would like it to look like. Could you make the changes to the spreadsheet so I can see what you are talking about and what I am doing wrong.

I'm not sure what you mean by increasing the column width. I'm not aware of a way to increase the column width for a cell to make it appear as one long cell while the rows below it have two cells.

Thank you again.

[barryleajo]

Hi kmoore
You first need to select at least two cells:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Sorry about the column width comment.
I didn't realise you had two data cols underneath your date heading.
This layout does mean that you can't rely on .DataSeries itself to populate your dates without some further work. It can't skip columns.
This may also explain why you can't CentreAcrossSelection - it won't work if adjacent cells contain data.
One final point, despite all I have been saying, if you want the dropdown to span two columns you will have to use merge cells ... Arghh! ... CentreAcrossSelection won't work.
Well I guess we both may have learnt something from this!

[kmoore]

Thanks Barry.