Fill down - adjust code

[J.W. Aldridge]

I am currently using two identical codes (like below) to fill in
blanks in column A.
(subsequent-identical code for Column B).

Any ideas on combining them both?

Need to run one code that fills in blanks in both columns A & B.

Thanx.



Sub FillColBlanksA()
Dim wks As Worksheet
Dim Rng As Range
Dim lastrow As Long
Dim col As Long

Set wks = ActiveSheet
With wks
col = .Range("A1").Column

Set Rng = .UsedRange
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

With .Cells(1, col).EntireColumn
.Value = .Value
End With

End With

End Sub

 

[Dove]

JW,

This can be done rather simply with only a couple of changes. Try passing
the column as an argument... Then, call the sub from a seperate sub with
the following. Call it again with a new column_number to perform it on a
second (or more) column(s):

' Code before this point left out...

Call FillColBlanks(column_number)

' Code after this point left out...

Then make the following changes (added the argument being passed and two
lines commented out):

Sub FillColBlanks(col As Long)
Dim wks As Worksheet
Dim Rng As Range
Dim lastrow As Long
'Dim col As Long <-----Now passed as argument

Set wks = ActiveSheet
With wks
'col = .Range("A1").Column <----Now passed as argument

Set Rng = .UsedRange
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

With .Cells(1, col).EntireColumn
.Value = .Value
End With

End With

End Sub

David

 

[J.W. Aldridge]

Thanx...

However, I am not that good with French yet. I only speak English and
a little Spanish.
(In other words.... I'm good at deciphering code given, but dont quite
got the lingo and understanding of putting it together).

Is there any simpler way to guide me?

I just need to run the code I gave above on columns A & B instead of
one column - or instead having to run two different macro's to get
this done.

Thanx Again