I need to sort a list by column "A" to take out blank rows and/or rows that
are populated with garbage that tagged along when I imported it as text.
Here is the problem (simplified for easier explanation):

Cell A1 is a part number.
Cells A2:A5 are empty
Cells B1:B5 are inventory counts of A1

This pattern repeats itself and the list is too long to manually copy Cell
A1 down to A2:A5.
Of course if I sort as it is, all the rows where A2:A5 are blank will sort
to the bottom.

How can I fill all empty cells with the value immediately above it?

Test on a copy of your data. This sort of thing should be rigorously tested
on duplicate data before put into use. The code will operate on the active
worksheet and will likely screw up data if run with the wrong sheet active.
Can be hard coded to work on a specified sheet:

Sub FillEmptyCells()
Dim r As Range, c As Range
Dim txt As String

Set r = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
Set r = r.Offset(0, -1)
r.Select
Exit Sub
For Each c In r.Cells
If IsEmpty(c) Then
c.Value = txt
Else
txt = c.Value
End If
Next
End Sub

Regards,
Greg


"Ivan" wrote:

I need to sort a list by column "A" to take out blank rows and/or rows that
are populated with garbage that tagged along when I imported it as text.
Here is the problem (simplified for easier explanation):

Cell A1 is a part number.
Cells A2:A5 are empty
Cells B1:B5 are inventory counts of A1

This pattern repeats itself and the list is too long to manually copy Cell
A1 down to A2:A5.
Of course if I sort as it is, all the rows where A2:A5 are blank will sort
to the bottom.

How can I fill all empty cells with the value immediately above it?

Take out the:

r.Select
Exit Sub

Testing leftovers. Sorry for the oversight.

Greg

"Greg Wilson" wrote:

Test on a copy of your data. This sort of thing should be rigorously tested
on duplicate data before put into use. The code will operate on the active
worksheet and will likely screw up data if run with the wrong sheet active.
Can be hard coded to work on a specified sheet:

Sub FillEmptyCells()
Dim r As Range, c As Range
Dim txt As String

Set r = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp))
Set r = r.Offset(0, -1)
r.Select
Exit Sub
For Each c In r.Cells
If IsEmpty(c) Then
c.Value = txt
Else
txt = c.Value
End If
Next
End Sub

Regards,
Greg


"Ivan" wrote:

I need to sort a list by column "A" to take out blank rows and/or rows that
are populated with garbage that tagged along when I imported it as text.
Here is the problem (simplified for easier explanation):

Cell A1 is a part number.
Cells A2:A5 are empty
Cells B1:B5 are inventory counts of A1

This pattern repeats itself and the list is too long to manually copy Cell
A1 down to A2:A5.
Of course if I sort as it is, all the rows where A2:A5 are blank will sort
to the bottom.

How can I fill all empty cells with the value immediately above it?

You have more replies at your post in .excel

Ivan wrote:

I need to sort a list by column "A" to take out blank rows and/or rows that
are populated with garbage that tagged along when I imported it as text.
Here is the problem (simplified for easier explanation):

Cell A1 is a part number.
Cells A2:A5 are empty
Cells B1:B5 are inventory counts of A1

This pattern repeats itself and the list is too long to manually copy Cell
A1 down to A2:A5.
Of course if I sort as it is, all the rows where A2:A5 are blank will sort
to the bottom.

How can I fill all empty cells with the value immediately above it?

--

Dave Peterson

If you want to do it without a macro, try entering this formula in C1
and copying down:
=OFFSET(A1,-MOD(ROW()+4,5),0)


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=531279