Define Range of empty cells parrallel used cells

[jdttek]

I have two columns,
A B
1 AAA
2 BBB
3 CCC

"A" with data and "B" blank.

I put a formula in (B1) and then want to fill the rest of col B with same formula for as many records as are in "A" WITHOUT going past the end of the length of col "A". Length of "A" will change, so the "B" fill function must be dynamic to determine if there is a record in "A". Copy/paste column and "Range(Selection, Selection.End(xlDown)).Select" don't work because they select 65536 rows and I just need to go to length of Col A.

Thanks for any help.

jdttek

 

[xlbo]

2 options
1: Use R1C1 formula, find the last row of A and copy into all cells
2: Use A1 method for the formula, enter into the top cell in B, find number of rows in A and FILLDOWN

For 1:, using a simple formula

sub SameFormula()
lRow = range("A65536&quot.end(xlup).row
range("B2:B" & lRow).formulaR1C1 = "=RC[-1]" ' =A1
end sub

For 2:, using a simple formula
Sub FillDownToBottom()
lRow = range("A65536&quot.end(xlup).row
With activesheet.range("B2&quot
.formula = "=A2"
.AutoFill Destination:=Range("B2:B" & lRow))
end with
End Sub

HTH Rgds
~Geoff~

 

[jdttek]

Geoff
THanks

If you are still on this thread:
This works perfect if I always need to fill Col B. How do I add flexibility so that it applies to what ever the col is for the active cell at the start of the procedure? E.G., say F2 is active cell and I need to fill F as long as there is data in E. The autofill you suggest always is for B. I have tried different ways to designate the Col as the Col of the active cell but can't get it to work/

Thanks for your help/

Regards

jdttek

 

[xlbo]

Ok - try this:

Sub FillDownToBottomDynamically()
Dim fdCol As Integer, refCol As Integer, refRng As String
Dim lRow As Long
fdCol = ActiveCell.Column
refCol = fdCol - 1
refRng = ActiveCell.Offset(0, -1).Address(False, False)
lRow = Cells(65536, refCol).End(xlUp).Row
With ActiveSheet.Range(ActiveCell.Address)
.Formula = "=" & refRng
.AutoFill Destination:=Range(Cells(ActiveCell.Row, fdCol), Cells(lRow, fdCol))
End With
End Sub

HTH Rgds
~Geoff~