Using Excel 2000.

Situation: Importing a text file where column a contains part number,
columns b, c, and d contain information relating to column a. Column a's
value is only listed once for many values in columns b, c, and d.

Goal: Automate autofilling column a's value until that value changes. So,
if cell a1's first value is 12345 with 4 blank cells below it, we need to
autofill cells a2:a5 with 12345, then skip a6 and copy its value to the empty
cells beneath it until that value changes.

The number of cells vary between values.

Any help in accomplishing this would be greatly appreciated.

Trisha

Trisha

You could do this without code.

Select column A and F5SpecialBlanksOK

In the active blank cell enter an = sign then point & click on cell above.

Hit CRTL + ENTER to copy down.

When happy with results, copy the column and in place Paste
SpecialValuesOKEsc.

Macro to do the same job.......

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


On Mon, 10 Jan 2005 14:09:05 -0800, "TrishaB"
wrote:

Using Excel 2000.

Situation: Importing a text file where column a contains part number,
columns b, c, and d contain information relating to column a. Column a's
value is only listed once for many values in columns b, c, and d.

Goal: Automate autofilling column a's value until that value changes. So,
if cell a1's first value is 12345 with 4 blank cells below it, we need to
autofill cells a2:a5 with 12345, then skip a6 and copy its value to the empty
cells beneath it until that value changes.

The number of cells vary between values.

Any help in accomplishing this would be greatly appreciated.

Trisha

Gord Dibben wrote:

Macro to do the same job.......

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above


This macro is very helpful!

How can it be modified to perform the same action on columns B and C
(in addition to column A?)


Thanks in advance!,

- Kobi

One way:

Option Explicit
Sub Fill_Blanks2()

Dim wks As Worksheet
Dim Rng As Range
Dim LastRow As Long
Dim Col As Long

Set wks = ActiveSheet
With wks
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, "A"), .Cells(LastRow, "C")) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Rng Is Nothing Then
MsgBox "No blanks found"
Exit Sub
Else
Rng.FormulaR1C1 = "=R[-1]C"
With Intersect(Rng.EntireColumn, .UsedRange)
.Value = .Value 'do you want them converted to values?
End With
End If
End With

End Sub

I find that it's easier to do this manually--rather than finding the macro and
then running it. (Unless if it's part of a larger mechanized process)

I'd select the range I want "A2:C (lastrow)"
then edit|goto special|check Blanks
hit the equal sign, then hit up arrow (without looking at the screen)
then hitting ctrl-enter (to fill those empty cells)

Then select column A:C and edit|copy, edit|pastespecial|Values (if necessary).

I think it's quicker and it allows me to do Undo.

wrote:

Gord Dibben wrote:

Macro to do the same job.......

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above

This macro is very helpful!

How can it be modified to perform the same action on columns B and C
(in addition to column A?)

Thanks in advance!,

- Kobi

--

Dave Peterson

Option Explicit

Sub FillColA()

Dim RowCount as Interger
Dim i As Integer
Dim NewValue

NewValue = Range("A1").Value

'Get the number of data rows
RowCount = Range("B65536").End(xlUp).Row

For i = 1 to RowCount

If Cells(i + 1, 1) = "" Then
Cells(i + 1, 1) = NewValue
Else
NewValue = Cells(i + 1, 1)
End If
Next i

End Sub

"TrishaB" wrote:

Using Excel 2000.

Situation: Importing a text file where column a contains part number,
columns b, c, and d contain information relating to column a. Column a's
value is only listed once for many values in columns b, c, and d.

Goal: Automate autofilling column a's value until that value changes. So,
if cell a1's first value is 12345 with 4 blank cells below it, we need to
autofill cells a2:a5 with 12345, then skip a6 and copy its value to the empty
cells beneath it until that value changes.

The number of cells vary between values.

Any help in accomplishing this would be greatly appreciated.

Trisha

Thank you both for your responses. I learned a lot and the problem is solved.

Trisha

"gocush" wrote:

Option Explicit

Sub FillColA()

Dim RowCount as Interger
Dim i As Integer
Dim NewValue

NewValue = Range("A1").Value

'Get the number of data rows
RowCount = Range("B65536").End(xlUp).Row

For i = 1 to RowCount

If Cells(i + 1, 1) = "" Then
Cells(i + 1, 1) = NewValue
Else
NewValue = Cells(i + 1, 1)
End If
Next i

End Sub

"TrishaB" wrote:

Using Excel 2000.

Situation: Importing a text file where column a contains part number,
columns b, c, and d contain information relating to column a. Column a's
value is only listed once for many values in columns b, c, and d.

Goal: Automate autofilling column a's value until that value changes. So,
if cell a1's first value is 12345 with 4 blank cells below it, we need to
autofill cells a2:a5 with 12345, then skip a6 and copy its value to the empty
cells beneath it until that value changes.

The number of cells vary between values.

Any help in accomplishing this would be greatly appreciated.

Trisha

Gord Dibben wrote:



Macro to do the same job.......

Sub Fill_Blanks()
'by Dave Peterson 2004-01-06
'fill blank cells in column with value above



This macro is very helpful!

How can it be modified to perform the same action on columns B and C
(in addition to column A)?


Thanks in advance!,


- Kobi