Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
FillDown from unknown column
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
FillDown from unknown column
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 "Woody" wrote in message .119... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
FillDown from unknown column
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 "Mike Fogleman" wrote in : 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 "Woody" wrote in message .119... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUM IF - What if column number is unknown | Excel Discussion (Misc queries) | |||
Unknown # cells need to be in one column - Macro | Excel Discussion (Misc queries) | |||
A function that looks for a value on a sheet on unknown column or row | Excel Worksheet Functions | |||
FillDown | Excel Programming | |||
Need to Find End Value of a Column with Unknown End Range | Excel Programming |