FillDown from unknown column

  • Thread starter Thread starter Woody
  • Start date Start date
W

Woody

I'm adding a formula to a column that I don't know in advance, and I need
to fill the formula down the same number of rows as there are in column A.

If I knew the column, I would do something like:

LastRow = Cells.SpecialCells(xlLastCell).Row
Range("F1:F" Trim(Str(LastRow))).FillDown

How can I do this?

Thanks,
Woody
 
Assuming the unknown columns are contiguous,

Sub UnknownFillDown()
Dim ColCount
Dim rng1

Sheet1.Range("A1").Activate
Selection.CurrentRegion.Select
ColCount = Selection.Columns.Count
ColCount = ColCount + 1
Sheet1.Cells(1, ColCount).Activate
Sheet1.Cells(1, ColCount).Formula = "=TRIM(A1)"
rng1 = ActiveCell.Address
Range(rng1, Range("A1").End(xlDown)).Offset(0, ColCount - 1).FillDown
End Sub

Adjust sheet name, ranges, and formula as needed,
Mike
 
Thanks Mike. That was a great help. Here's the code I wound up with.
For some reason it didn't work if the sheet wasn't active.

Sub AddCAPPivotFields()

' Add Cap Pivot Fields
' Assumes first criteria is cost account

Dim oDatasheet As Worksheet
Dim LastRow As Long
Dim LastCol As Long

On Error Resume Next

Set oDatasheet = ActiveWorkbook.Worksheets("Data")
If oDatasheet Is Nothing Then
MsgBox "Active workbook doesn't appear to be a Cobra spreadsheet
report.", vbCritical
Exit Sub
End If

With oDatasheet
.Activate 'don't understand why this necessary
LastCol = .Cells.SpecialCells(xlLastCell).Column
.Cells(1, LastCol + 1).Formula = "=LEFT(A1,6)"
.Cells(1, LastCol + 2).Formula = "=LEFT(A1,FIND("" "",A1)-1)"
.Cells(1, LastCol + 3).Formula = "=MID(A1,FIND("" "",A1)+1,FIND(""
"",A1,FIND("" "",A1,FIND("" "",A1,FIND("" "",A1)+1)+1)+1)-FIND("" "",A1)-
1)"
.Cells(1, LastCol + 4).Formula = "=RIGHT(A1,LEN(A1)-FIND(""
"",A1,FIND("" "",A1,FIND("" "",A1,FIND("" "",A1)+1)+1)+1))"
rng1 = .Cells(1, LastCol + 1).Address
.Range(rng1, Range("A1").End(xlDown)).Offset(0, LastCol).FillDown
End With
End Sub
 
Back
Top