While-Wend loop to fill in nulls

[tbmarlie]

I'm trying to find any non-null cells in column f and then fill in any
null cell(s) below it with the value of that particular non-null cell
until it gets to the next non-null cell in column f. And, then repeat
this process for every non-null cell it until it gets to the end of my
overall data. I've created the code shown below, but it only partially
fills in the data; it only fills in one null cell below the non-null
cell. I'm sure I'm missing something obvious.

Dim h As Long
For h = Cells(1, "g").End(xlDown).Row To 1 Step -1
If Cells(h, "f") <> "" Then
While Cells(h + 1, "f") = "" And Cells(h + 1, "g") <> ""
Cells(h + 1, "f") = Cells(h, "f")
Wend
End If
Next h

Some sample data is as follows:
column f column g
xxx-xx-xxxx INCENTIVE PAYMENTS YTD
EXCLUDED FROM FICA-HI YTD
yyy-yy-yyyy INCENTIVE PAYMENTS YTD
EXCLUDED FROM FICA-HI YTD
zzz-zz-zzzz SOLTAX EARNINGS NO 3
TAXABLE FICA-SS YTD
TAXABLE FICA-HI YTD
TAX FEDERAL WITHHOLDING
TAX FICA-SS
TAX FICA-HI
SOLTAX DEDUCTION NO 1
999-99-999 SUPPL PAY

 

[Gary''s Student]

Easier than you thought. Start from the top:

Sub marlie()
h = Cells(Rows.Count, "G").End(xlUp).Row
For i = 2 To h
Set r = Cells(i, "F")
If IsEmpty(r.Value) Then
r.FillDown
End If
Next
End Sub

 

[Dave Peterson]

Debra Dalgleish shares a couple of ways to do this:
http://contextures.com/xlDataEntry02.html

Personally, unless this is a portion of a larger mechanized routine, I'd do it
manually. I think it's quicker to do than to find the code, modify it for a
specific column and run it.

 

[JW]

Debra Dalgleish shares a couple of ways to do this:http://contextures.com/xlDataEntry02.html

Personally, unless this is a portion of a larger mechanized routine, I'd do it
manually. I think it's quicker to do than to find the code, modify it for a
specific column and run it.

I created an add-in that has a Fill Blanks routine. The first thing
it does is popup an input box asking for the column letter that is
then assigned to a variable and used throughout the code. It cycles
through that column until the last row of the spreadsheet is reached.
Of course, there may be times when it doesn't need to cycle through to
the last used row, but those times are so few and far between that I
found it more effective to just write it for the most common process.

I wrote it as a sub with a colLetter argument so that I can simply
call the sub whenever needed using the column letter as it's argument
such as fillBlanks "A". If this is something that you see yourself
routinely doing I strongly urge that you do the same. As Dave said,
it is very inefficient to have to modify the code constantly for
different scenarios.

Regards
-Jeff-

 

[tbmarlie]

I created an add-in that has a Fill Blanks routine. The first thing
it does is popup an input box asking for the column letter that is
then assigned to a variable and used throughout the code. It cycles
through that column until the last row of the spreadsheet is reached.
Of course, there may be times when it doesn't need to cycle through to
the last used row, but those times are so few and far between that I
found it more effective to just write it for the most common process.

I wrote it as a sub with a colLetter argument so that I can simply
call the sub whenever needed using the column letter as it's argument
such as fillBlanks "A". If this is something that you see yourself
routinely doing I strongly urge that you do the same. As Dave said,
it is very inefficient to have to modify the code constantly for
different scenarios.

Regards
-Jeff-- Hide quoted text -

- Show quoted text -

Thanks G.Student. That's what I needed

Dave/Jeff: Thanks for the input. I needed this code as part of a
larger routine.