Drag and Fill Code Help

Hello all, I'm in my mid fifties and completely new to computing and particularly excel. I am recently retired due to ill health and started a project to teach myself through online tutorials and Youtube videos.
Forgive my method of explaining what I require as I know no other way with the use of proper technical terms/language.

I have a worksheet where I would like to have excel automatically drag and fill into empty cell columns.

The cells in the range are H1:AP1501

So the range for each column that the code should only effect is H1:H1501, I1:I1501, J1:J1501, K1:K1501 and so on across as far as AP1:AP1501

The code should search in the range H1501:AP1501 for blank/empty cells in this range.

Any blank cell in this range should be filled, so if P1501 is blank and Q1501 is not, the range P11501 should be filled with whats in the range Q1:Q1501

When I say filled I mean that Q1:Q1501 contents should be dragged into P11501 not duplicated in so appearing twice on the worksheet.

I cannot just simply delete a column, as there is data in all columns past row 1501.

I would be extremely grateful if someone could provide me with the code that I can copy & paste in to do the above.


Thanking You In Advance.

Are you saying that if P1700 has data (formulae?), but P701 does not, you want Q1:Q701 copied over to P1701?

Yes, the whole range should be copied over. Q1:Q1501(inclusive) replaces P11501,


There are 35 columns are in the range H1:H1501 to AP1:AP1501

H1:AP1 are header cells

Lets say there are 35 Salesmen in the Company

18 called Mike

17 called John

H1 = Mike 1
I1 = John 1
J1 = Mike 2
K1 = John 2
L1 = Mike 3
M1 = John 3
and so on across to AP1

So we can see 18 mikes and 17 johns across the worksheet

as it happens in the range H1501:AP1501 only the cells under a salesman called mike have data in them the john cells are blank in the range h1501:ap1501

drag and fill all the mikes across so now all that can be seen are 18 mikes with no johns at all

the range to drag is always 1:1501 inclusive

Any chance of a reply?