Automating Autofill Coding Question

[Guest]

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]

Trisha

You could do this without code.

Select column A and F5>Special>Blanks>OK

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
Special>Values>OK>Esc.

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

 

[Guest]

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

 

[Guest]

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

Trisha

 

[theargus]

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

 

[theargus]

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]

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.

 

[theargus]

Hi Dave,

Thanks for the assistance!

I get an error message (Compile Error: Syntax Error) when
attempting to run the macro you provided. I think perhaps this is
happening because of the spacing. I am viewing your post on
googlegroups.

There seems to be a problem with 2 parts of the macro:

1:

Set Rng = .Range (.Cells(2,"A"), .Cells(LastRow, "C")) _

 

[Dave Peterson]

I'm not sure how Google represents that code.

But this line:
Set Rng = .Range (.Cells(2,"A"), .Cells(LastRow, "C")) _
had a line right under it.

Set Rng = .Range(.Cells(2, "A"), .Cells(LastRow, "C")) _
.Cells.SpecialCells(xlCellTypeBlanks)

And this line:

Rng.FormulaR1C1 = "=R[-1]C "
was really:
Rng.FormulaR1C1 = "=R[-1]C"

But I'm not sure if there was a different error that was causing the problem.

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

Hi Dave,

Thanks for the assistance!

I get an error message (Compile Error: Syntax Error) when
attempting to run the macro you provided. I think perhaps this is
happening because of the spacing. I am viewing your post on
googlegroups.

There seems to be a problem with 2 parts of the macro:

1:

Set Rng = .Range (.Cells(2,"A"), .Cells(LastRow, "C")) _
.

2:

Rng.FormulaR1C1 = "=R[-1]C "

"

Do you have any idea how to correct this?

Any and all help is sincerely appreciated.

I am using Excel 2002 SP-2 and running Windows XP.

THANKS!

- Kobi

 

[theargus]

Dave,

Thanks alot - that fixed both problems!

However, I now get the following error on the following line of code:

.Value = .Value 'do you want them converted­ to values?

It highlights the ".value" that appears AFTER the equals sign and gives
the error message:

Compile error:
Method or data member not found

Any idea why this is occurring?

THANKS SO MUCH FOR YOUR ASSISTANCE!


- Kobi

 

[Dave Peterson]

I couldn't get it to break.

Did you make any changes to these lines?

With Intersect(Rng.EntireColumn, .UsedRange)
.Value = .Value 'do you want them converted to values?
End With

And just a silly guess.

Is your worksheet protected (with some cells locked and others unlocked)?

Any merged cells in column A:C?

 

[theargus]

I couldn't get it to break.

Did you make any changes to these lines?


And just a silly guess.

Is your worksheet protected (with some cells locked and others unlocked)?

Any merged cells in column A:C?

Hi Dave,

I reinserted the original code you provided and corrected some
spacing issues - it works perfectly now. I guess Google Groups adds
spaces from time to time.


THANKS SO MUCH!


- Kobi