Want to copy a cell down until it comes to the next nonblank cell, and then take this next nonblank

[GretOgrady]

First time post-er, please be patient!

I've got an excel spreadsheet which I'm trying to put into a format
that's easy to filter. Before I can do that, I've got to clean up the
data in one column - I'm bound by how the data dumps from another tool.

There are several rows that basically relate to one grouping of
information and so in this one column I have a project name (as part of
a long string of text defining additional aspects of this project) and
then for several rows below that, same column, I have names of people
working on this particular project. (The name of the project only shows
up in the first row of this grouping of rows.)

I want to basically get the name of the project to show up for each of
the rows that it also shows people for. (Thus, I can filter for a
particular project and see all of the people assigned to it.)

I've so far set up a column to basically pull in the name of the
project name on each row, if that particular row is the row that is
housing the name of the project. If that particular row is instead a
person's name, it pulls in "" - my attempt at getting it to keep the
cell blank (I do this thru a combination of replace and search
functions:
=IF((ISERR(SEARCH($D$12,D16))=TRUE),"",(REPLACE(D16,SEARCH($D$12,D16),500,"")))
).

I was then hoping to set up a macro via VBA that would take the name of
the project and replicate it down until it came to the next project
name. When it gets to the next project name, it would pick up this new
name and continue on until the next project name is encountered.

I also think I have a problem in how I've set up my search & replace
functions in that I technically don't have blank cells when it brings
in "" into the cell as described above - I think I might need to fix
this as well.

Any help pointing me in right VBA direction would be greatly
appreciated! I'm only dipped my toe in the great pool of VBA
programming...so be gentle.

 

[Gord Dibben]

Gretchen

You can do this manually if you wish.

Select the column with the project names.

Hit F5 key and Special>Blanks>OK.

Enter an = sign in first blank(active cell) then point to the cell above.

Hit CRTL + ENTER.

Now re-select the column and copy>paste special>values>OK>Esc.

If you really want a macro..............

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above
Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long

Set wks = ActiveSheet
With wks
Col = ActiveCell.Column
'or
'col = .range("b1").column

Set Rng = .UsedRange 'try to reset the lastcell
LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set Rng = Nothing
On Error Resume Next
Set Rng = .Range(.Cells(2, Col), .Cells(LastRow, Col)) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
End If

'replace formulas with values
With .Cells(1, Col).EntireColumn
.Value = .Value
End With

End With

End Sub


Gord Dibben Excel MVP

 

[GretOgrady]

Thank you so much for such a quick response! This works and gives me
what I need!