auto fill problem

jamesozzie

New Member
Joined
Jul 28, 2007
Messages
3
Hi there, I hope somebody here can help me. Maybe it is a very simple solution or else not possible.

Anyway I have 1 column with around 6,000 rows in use (column A)
I have another column with merged cells. (all blank) (Column B)

I would like to make each cell in the merged cells simple equal the value in the first column. This I can do easily using (=a1)

The problem is Excel doesn't seem to be finding the pattern I would like. Because I have merged cells 3 rows in size(Column B) the auto fill is going from =a1 to =a4. I would like it to simply go from =a1 to =a2 to =a3 with my fill.

Sorry if i did not explain this in a great way but I would really really appreciate anybody s help!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
I did some experiments.

I presume that cells B1 to B3 are merged

try this macro

Code:
Sub test()
Dim rng As Range, c As Range
Set rng = Range("b1:b3")
rng = ([a1].Value & Chr(10) & [a2].Value & Chr(10) & [a3].Value)
    Range("B4").Select
End Sub

remember the values in merged cells will be texts and not numbers.
Code:
 

jamesozzie

New Member
Joined
Jul 28, 2007
Messages
3
couldnt get it working unfortunately!
im not familar with macros either, isnt there any other way i can use a function to get this done?
i appreciate your help greatly
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
57,446
Office Version
  1. 365
Platform
  1. Windows
jamesozzie

Welcome to the MrExcel board!

Can you tell us exactly which cells are merged? Tell us 2 or 3 sets of merged cells.
Do they have to remain merged? If so, can you explain why?
Tell us the data that you have in A1:A10 and use that data to tell us what results you want and where (not the formulas, but the resulting values).

All of the above would be easier if you could post a small sample of your sheet. There are a couple of ways of doing that and you might like to investigate these in due course:
Colo’s HTML Maker:
http://www.mrexcel.com/board2/viewtopic.php?t=92622
or Excel jeanie:
http://www.excel-jeanie-html.de/index.php?f=1
 

Forum statistics

Threads
1,181,720
Messages
5,931,668
Members
436,797
Latest member
fullmetalpogi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top