code that will FIND FIRST BLANK CELL in COL B as trigger, then begin COPY DOWN from there

[ChrisOK]

need code that's smart enough to: Start w/ Col "B", locate first empty cell, then copy downward --- whatever is present in the cell above it... (through Col "AM").

Columns: B, C, D,...F, G, I, K, L, M, N, S, U, Y, O are simple codes, etc

Columns: H, J, T and X through AM are formulas that need to be drug downward

The copying downward can look to Column "O"... if Col "O" is empty then stop copying downward...

SKIP columns: E, O, P, Q as data is already present..

In This Visual Example: (B3 would be the first empty cell that would trigger the code to copy B2 and copy it down through B6)
(Columns C, D, F and so on -- would also look to what is present in row 2 (in this case) and copy it downward since it is the last row that held data to copy downward)

Col...... A......B.......C........D........E......F..
Row1...xxx....xxx....xxx.....xxx......xxx...xxx..
2........xxx....xxx....xxx.....xxx......xxx...xxx..
3........xxx....[...]...[...]....[...].....xxx..[...]..
4........xxx....[...]...[...]....[...].....xxx..[...]..
5........xxx....[...]...[...]....[...].....xxx..[...]..
6........xxx....[...]...[...]....[...].....xxx..[...]..
7...........................................................
8...........................................................

Row 7 and below has no data at all in column "O" so, it will stop copying downward based on that trigger..

 

[C Moore]

Try

   Sub fill_test()
    lastcol = Range("A1").End(xlToRight).Column
    lastrow = Range("A1").End(xlDown).Row
    For i = 2 To lastcol
        Range(Cells(1, i).End(xlDown), Cells(lastrow, i)).FillDown
    Next i
End Sub

 

[ChrisOK]

Hi C Moore, (and others who may know how to accomplish this) ???
I've tested the above and it's not doing what is needed as explained above...
The code above is going to B4 (which is not a blank) and copies it down to the bottom and is overwriting existing data...(don't want to do that). It should simply be backfilling where blank cells exist to ensure they are populated with available data from above it...

Using the example above, I need the code to locate the first *BLANK* cell in column B...
When found, it will look to the populated cell immediately above it and copy/drag that contents downward.
In the example above, it would have copied whatever was present in B2 down through B6.

On other days, the first blank may be found on B5 and only need to copy down a little bit... (in other words, it needs to be smart enough to find that first blank cell in column B whereever it may exist and copy down from there... then continue doing the same with the other columns noted..such as C, D, etc..

 

[ChrisOK]

Ok, got it working with this:
(wanted to share for others who are looking for similar solution)

Sub CopyDwn() 
    Dim lr As Long 
    Dim lw As Long 
    Dim i As Integer 
    Dim ar As Variant 
     
    ar = [{"B", "D","F","N","Q","Q","S","W"}] 
    lr = Range("A" & Rows.Count).End(xlUp).Row 
    lw = Range("O" & Rows.Count).End(xlUp).Row 
     
    For i = 1 To 8 Step 2 
        Range(ar(i) & lr & ":" & ar(i + 1) & lr).AutoFill Range(ar(i) & lr & ":" & ar(i + 1) & lw) 
    Next i 
End Sub

 

[C Moore]

Sorry about that, it sounded (and looked) like you were missing everything below a certain point. I wrote it that way because it was simple, I can fix it, it will just be a longer code.