Copy selected cell in column and fill in blanks

[Bluestar]

Greetings,

I am a novice at programming and have been reading the FAQs trying to solve a problem I have.

I need to fill in a column's various "blank cell" of several hundred rows in the following manner:

1. Select cell of column with text (is the name of individual working project)

2. Copy cell and paste in all empty cells below text of that column.

3. Upon reaching next cell in column with text, stop pasting, select the cell with new text, repeat steps 1-3.

I figured I needed a fail safe like: If blank cells = >15, end sub. Otherwise it would keep running.

I have started with Excel "record a Macro" function and got the below

Sub FILLinBLANKS()
'
' FILLinBLANKS Macro
' Macro recorded 6/10/2005
'
' Keyboard Shortcut: Ctrl+j
'
Cell.Select
Selection.Copy
Range("B13").Select
ActiveSheet.Paste
Range("B14").Select
ActiveSheet.Paste
Range("B15").Select
ActiveSheet.Paste
End Sub

but of course it is putting stuff in B13:B15. and not doing what I need it do with looping, copying text cell, filling in blank cells of the column below the text cell, then selecting the next text cell in column after the blanks are filled, and repeating process.

thanks for any replies,

bluestar
"ben"

[keepITcool]

try like:

Sub FillBlanks()
Dim rngCol As Range, rngBlank As Range, rngArea As Range

If Not TypeOf Selection Is Range Then Beep: Exit Sub
Set rngCol = Selection
'expand to entire column
If rngCol.Count = 1 Then Set rngCol = rngCol.EntireColumn
'limit to usedrange
Set rngCol = Intersect(rngCol, ActiveSheet.UsedRange)


On Error Resume Next
'find blank cells
Set rngBlank = rngCol.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If rngBlank Is Nothing Then Beep: Exit Sub

'Process each area
'copy value in cell above to blank cells
application.screenupdating=False
For Each rngArea In rngBlank.Areas
rngArea.Value = rngArea.Cells(0, 1).Value
Next
application.screenupdating=true

End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bluestar wrote :

>
> Greetings,
>
> I am a novice at programming and have been reading the FAQs trying
> to solve a problem I have.
>
> I need to fill in a column's various "blank cell" of several hundred
> rows in the following manner:
>
> 1. Select cell of column with text (is the name of individual
> working project)
>
> 2. Copy cell and paste in all empty cells below text of that
> column.
>
> 3. Upon reaching next cell in column with text, stop pasting,
> select the cell with new text, repeat steps 1-3.
>
> I figured I needed a fail safe like: If blank cells = >15, end sub.
> Otherwise it would keep running.
>
> I have started with Excel "record a Macro" function and got the below
>
> Sub FILLinBLANKS()
> '
> ' FILLinBLANKS Macro
> ' Macro recorded 6/10/2005
> '
> ' Keyboard Shortcut: Ctrl+j
> '
> Cell.Select
> Selection.Copy
> Range("B13").Select
> ActiveSheet.Paste
> Range("B14").Select
> ActiveSheet.Paste
> Range("B15").Select
> ActiveSheet.Paste
> End Sub
>
> but of course it is putting stuff in B13:B15. and not doing what I
> need it do with looping, copying text cell, filling in blank cells of
> the column below the text cell, then selecting the next text cell in
> column after the blanks are filled, and repeating process.
>
> thanks for any replies,
>
> bluestar
> "ben"

[Bluestar]

Many Many Many thanks

Highest regards,

blue