Modify Code to Include More Columns

[nirvehex]

Hey guys,

I found a code that does what I'm trying to do, but I need to modify it to include more columns.

Code:

Sub FillColBlanks()
'by Dave Peterson  2004-01-06
'fill blank cells in column with value above
'http://www.contextures.com/xlDataEntry02.html
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long


Set wks = ActiveSheet
With wks
   
   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

Any idea how to (1) include more columns then only "b" and are non sequential? (ex. B, C, E, G, I, J) and (2) how to change the code to use Table Header names instead of column letters? Thank you!

This is cross posted at: http://www.mrexcel.com/forum/excel-q...e-columns.html

[mrice]

Maybe

Sub FillColBlanks()
'by Dave Peterson  2004-01-06
'fill blank cells in column with value above
'http://www.contextures.com/xlDataEntry02.html
Dim wks As Worksheet
Dim rng As Range
Dim LastRow As Long
Dim col As Long

Dim MyArray()

MyArray = Array("HeadingB", "HeadingC", "HeadingE", "HeadingG", "HeadingI", "HeadingJ")

Set wks = ActiveSheet
With wks
   For Each MyColumn In MyArray
   col = .Rows(1).Find(MyColumn, , xlValues, xlWhole).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
    
    Next MyColumn

End With


End Sub

[nirvehex]

Thank you Martin.

However, it errors out at:

col = .Rows(1).Find(MyColumn, , xlValues, xlWhole).Column

Should MyColumn be declared some where?

[mrice]

Try dim -ing it and MyArray as strings.

[nirvehex]

Still errors out on the same line. Says "For Each control variable on arrays must be Variant"

Any ideas?

[mrice]

OK - try

Dim MyArray() as Variant
Dim MyColumn as Variant

Not sure what 'errors out' means - is it a compile error or a runtime error?

[nirvehex]

I tried that too It is a compile error. I'm wondering do I have to set MyColumn= to anything?

[nirvehex]

Nevermind. I got it. I went another route and set multiple rng's and then combined them using the Union function!