macro for autofilling cells below identically

[Ivan]

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?

[Greg Wilson]

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?
>
>
>

[Greg Wilson]

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?
> >
> >
> >

[Dave Peterson]

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

[John James]

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)