Hello folks.
My spreadsheet has 22000 rows. The data import was from an html
report from another app.
Column A is as shown, column B has data in cells 1 through 9. So
column B is no problem. When I apply a autofilter on the sheet column
A most of the time shows blanks. I need to fill in the blanks with
the preceeding nonblank. If it is nonblank it should skip to the next
line and try again. I'm familiar with if then else and for next
loops but not the MS Excel way of doing things. Syntax errors
galore. My other problem is that Im not sure where to put this
formula / script.

I want this:

A
1 blah
2
3
4 blah blah
5
6
7 hohum
8
9


To Turn into this:

A
1 blah
2 blah
3 blah
4 blah blah
5 blah blah
6 blah blah
7 hohum
8 hohum
9 hohum

Any help is greatly appreciated.

See
http://www.contextures.com/xlDataEntry02.html

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


<(E-Mail Removed)> wrote in message news:cb0c5744-5f32-4213-8c22-(E-Mail Removed)...
> Hello folks.
> My spreadsheet has 22000 rows. The data import was from an html
> report from another app.
> Column A is as shown, column B has data in cells 1 through 9. So
> column B is no problem. When I apply a autofilter on the sheet column
> A most of the time shows blanks. I need to fill in the blanks with
> the preceeding nonblank. If it is nonblank it should skip to the next
> line and try again. I'm familiar with if then else and for next
> loops but not the MS Excel way of doing things. Syntax errors
> galore. My other problem is that Im not sure where to put this
> formula / script.
>
> I want this:
>
> A
> 1 blah
> 2
> 3
> 4 blah blah
> 5
> 6
> 7 hohum
> 8
> 9
>
>
> To Turn into this:
>
> A
> 1 blah
> 2 blah
> 3 blah
> 4 blah blah
> 5 blah blah
> 6 blah blah
> 7 hohum
> 8 hohum
> 9 hohum
>
> Any help is greatly appreciated.

Try something like this.....the easiest place to put this code is copy the
code, then right-click the worksheet tab choose "view code" and paste. To
run choose from the Excel toolbar tools-macro-macros then select "Infill"
and click Run. If using xl2007 then choose the Developer ribbon - Macros -
choose "Infill" click Run. Or from the sheet code place cursor somewhere in
the sub Infill code and click Run.

The following code fills in the gaps with the preceding non-empty value, it
only works down the rows, so it only fills after the first non empty row in
column A. Column B is used to determine the row extent.

Sub Infill()
Dim xRow As Long, xCell

With Sheets("Sheet1") ' < change this to suit
xRow = 1
Do While xRow <= .Cells(Rows.Count, "B").End(xlUp).Row
If Len(Trim(.Cells(xRow, 1))) > 0 Then
xCell = .Cells(xRow, 1)
Else
.Cells(xRow, 1) = xCell
End If
xRow = xRow + 1
Loop
End With
End Sub

--

Regards,
Nigel
(E-Mail Removed)



<(E-Mail Removed)> wrote in message
news:cb0c5744-5f32-4213-8c22-(E-Mail Removed)...
> Hello folks.
> My spreadsheet has 22000 rows. The data import was from an html
> report from another app.
> Column A is as shown, column B has data in cells 1 through 9. So
> column B is no problem. When I apply a autofilter on the sheet column
> A most of the time shows blanks. I need to fill in the blanks with
> the preceeding nonblank. If it is nonblank it should skip to the next
> line and try again. I'm familiar with if then else and for next
> loops but not the MS Excel way of doing things. Syntax errors
> galore. My other problem is that Im not sure where to put this
> formula / script.
>
> I want this:
>
> A
> 1 blah
> 2
> 3
> 4 blah blah
> 5
> 6
> 7 hohum
> 8
> 9
>
>
> To Turn into this:
>
> A
> 1 blah
> 2 blah
> 3 blah
> 4 blah blah
> 5 blah blah
> 6 blah blah
> 7 hohum
> 8 hohum
> 9 hohum
>
> Any help is greatly appreciated.

Tested and they work!!!
Thank you Ron and Nigel.
This is Awsome!!

One time project, and I really did not look forward to the thought of
doing it manually.
Thanks again!