Ned help with Fill

[Sean15]

Hi:

I need to fill a large worksheet as follows:
all blank rows that follows a cell with a value should be filled with that cell value.
For example in column C below, C5, C6, C7 should be filled with value in C4.
Value in C8 must stay as is but C11, CC12 must be filled with value in C8

The same will apply to column D, E and F

Could someone help please?

Excel 2010
	C	D	E	F
2	Check	Date	Acct	Name
3	366329	07/27/18	63358	John Peter
4	366570	08/24/18	63358	John Peter
5
6
7
8	361289	03/24/17	467	Mike James
9
10	361361	03/31/17	63404	Joan Yell
11
12
13	Required values:
14	Check	Date	Acct	Name
15	366329	07/27/18	63358	John Peter
16	366570	08/24/18	63358	John Peter
17	366570	08/24/18	63358	John Peter
18	366570	08/24/18	63358	John Peter
19	366570	08/24/18	63358	John Peter
20	361289	03/24/17	467	Mike James
21	361289	03/24/17	467	Mike James
22	361361	03/31/17	63404	Joan Yell
Sheet2

 

[jtakw]

Hi,

Enter this formula in C5:

=C4

1. With C4 selected, right click, Copy
2. Drag selected C4 down and across to F10
3. Hit F5 (NOT the cell - F5 on your keyboard)
4. In the Go To pop up, click "Special", select "Blanks"
5. Click OK, then Enter

Format Column D as Date

 

[Sean15]

Hi:

I'm sure I am doing something wrong but suggestion in post #2 is filling value in C4 in C5 to D7 only.

I need to fill a large worksheet as follows:

Sorry I should have mentioned worksheet has 17,000+ rows so suggestion in post #2 would be cumbersome.

 

[My Aswer Is This]

Try this:

Sub Filldown_Please()
'Modified 9/29/2018 8:59 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "C").End(xlUp).Row
For i = 3 To Lastrow
    If Cells(i, "C").Value = "" Then Cells(i, "C").Resize(, 4).Value = Cells(i, "C").Offset(-1).Resize(, 4).Value
Next
Application.ScreenUpdating = True
End Sub

 

[No_Need_For_Macros]

Sean15, in the example you have 3 blank rows under "John Peter". If you have, say, 100 blank rows below a name, this may not work. But here's a brute force way to clean things up:

1. In cell G3 type: =if(isblank(c3),1,c3). Copy this into columns H, I, and J in order to match your other data columns. Drag the formulas down to Row 18000 (per your post of 17k +rows)
2. In cell K2 type: =countif(k3:k17000,"=1"). This will tell you if you have blanks left.
3. In cell K3 type: =if(g3=1,g2,g3). Copy this formula all the way from K3 to AA18000
4. Copy the formula in K2 all the way to AA2. If the number in AA2 is NOT 0, you will need to continue Step 3 out to, say ZZ18000, etc...

You will eventually have no blank rows and you can just copy and paste values into a new, fresh Tab!

 

[Peter_SSs]

I have assumed
- that your data column is formatted with a Date format, not General. If General, one extra line may be needed in the code.
- that the 'blank' cells are in fact blank and not formulas returning "".
- these columns do not contain any formulas that need to be retained as formulas.

In that case there should be no need to loop through the blank 'sections' or individual rows but do them all at once.
Test with a copy of your workbook.

Sub FillBlanks()
  Application.ScreenUpdating = False
  With Intersect(ActiveSheet.UsedRange, Columns("C:F"))
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
  Application.ScreenUpdating = True
End Sub

 

[Sean15]

Hi:

Thanks for help.

Tested code on 337 rows.

Code in post #4 work works flawlessly.

Code in post #6:
Inserted dates in column C.
Did not insert any values in column E and F.
Inserted values in column C & D beyond the test range of 337 rows. Values inserted all the way to row 16947.


Regards,

 

[My Aswer Is This]

Glad my script worked for you.

 

[Peter_SSs]

Code in post #6:
Did not insert any values in column E and F.

Then the 'blank' cells in columns E:F are not actually empty. Ref my second assumption (though there are also ways other than a formula returning "" that can make a cell appear blank but not actually be empty)

Inserted values in column C & D beyond the test range of 337 rows. Values inserted all the way to row 16947.

That was deliberate. In your sample, John Peter & Mike James require rows to be filled. Is it not possible that Joan Yell may also need some blanks filled? If so, how would we know how far to fill those values?
I used the assumption that if there is data elsewhere in your sheet (eg columns A:B) that might determine where the fill should go to. Looks like that as a wrong assumption.

Anyway, you have something that works. With 17,000+ rows and 4 columns I thought that might take a bit of time with MAIT's suggested code so was trying for a way to do it all at once. If it was possible to determine what is happening with those 'blanks' in columns E & F, it still may be possible to process the whole range at once if you wanted to pursue that possibility.

 

[jtakw]

Sorry, meant to post earlier, but LH (life happens)

If you actually posted a copy of your real data, there're spaces in Columns E & F, so you'll need to clean those up first before any formula can work...

Edit: once you've cleaned up the spaces in E & F, to copy the formula down 17000 rows (or more) is not difficult at all...