copy range across columns to last column

[288enzo]

Hello,

Is there a method similar to Range("B4:B" & Range("A" & Rows.Count).End(xlUp).Row) that can be used for columns?

I have a formula in B3, I used the above to copy the formula down to the last row. I would now like to copy right to the last column based on row 2.

Basically trying to fill a range last row and last column with the formula in B3.

A is used for the last row
2 is used for the last column

Thank you

 

[JoeMo]

Maybe:

Sub FillFormula()
Dim Lrw As Long, Lcol As Long
Lrw = Columns(1).Find("*", , , xlByRows, xlPrevious).Row
Lcol = Rows(2).Find("*", , , xlByColumns, xlPrevious).Column
Range("B3:B" & Lrw).FillDown
Range("B3", Cells(Lrw, Lcol)).FillRight
End Sub

Another way that fills the formula but not the format of B3:

Sub FillFormula2()
Dim Lrw As Long, Lcol As Long
Lrw = Columns(1).Find("*", , , xlByRows, xlPrevious).Row
Lcol = Rows(2).Find("*", , , xlByColumns, xlPrevious).Column
Range("B3", Cells(Lrw, Lcol)).Formula = Range("B3").Formula
End Sub

 

[288enzo]

Maybe:

Sub FillFormula()
Dim Lrw As Long, Lcol As Long
Lrw = Columns(1).Find("*", , , xlByRows, xlPrevious).Row
Lcol = Rows(2).Find("*", , , xlByColumns, xlPrevious).Column
Range("B3:B" & Lrw).FillDown
Range("B3", Cells(Lrw, Lcol)).FillRight
End Sub

Another way that fills the formula but not the format of B3:

Sub FillFormula2()
Dim Lrw As Long, Lcol As Long
Lrw = Columns(1).Find("*", , , xlByRows, xlPrevious).Row
Lcol = Rows(2).Find("*", , , xlByColumns, xlPrevious).Column
Range("B3", Cells(Lrw, Lcol)).Formula = Range("B3").Formula
End Sub

I appreciate the help, but neither worked for me.

Here is what the sheet looks like I'm working with.

Area 84 IND Sept-October.xlsx
	B	C	D	E	F	G
2	600336A3	600336PL	600336SSIFU	600336SSIVD	600336VIQS	601036F
3	8
4	1
5	2
6	4
7	1
8	1
9	1
10	0
11	0
12	0
13	0
14	0
15	0
report

Cell Formulas
Range	Formula
B3:B15	B3	=COUNTIFS(data!H:H,$A3,data!A:A,B$2)

Named Ranges
Name	Refers To	Cells
data!_FilterDatabase	=data!$A$3:$AI$3	B3:B15

I was thinking, from the above and a starting point of B3, create a loop, look +1 column and -1 row, if there is data then copy B3 to last row, paste +1 column.

Thoughts?

 

[Alex Blakenburg]

Try this, it's a modification of Joe's code. Since you have already done a fill down on column B, I have used column B for the last row to fill right.
(you can change it to A if you want to)

Sub FillFormula()
    Dim Lrw As Long, Lcol As Long
    Lrw = Cells(Rows.Count, "B").End(xlUp).Row
    Lcol = Cells(2, Columns.Count).End(xlToLeft).Column
    Range("B3", Cells(Lrw, Lcol)).FillRight
End Sub

 

[288enzo]

Try this, it's a modification of Joe's code. Since you have already done a fill down on column B, I have used column B for the last row to fill right.
(you can change it to A if you want to)

Sub FillFormula()
    Dim Lrw As Long, Lcol As Long
    Lrw = Cells(Rows.Count, "B").End(xlUp).Row
    Lcol = Cells(2, Columns.Count).End(xlToLeft).Column
    Range("B3", Cells(Lrw, Lcol)).FillRight
End Sub

Thank you very much, it worked perfectly.

 

[Alex Blakenburg]

Glad we could help & thanks for letting us know.