copy cell paste to last row when last row and last column are ever changing

[288enzo]

I'm sure everyone is tired of my posts ?

Working on a column that is ever changing is really testing me.

Back to this -

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

Cell Formulas
Range	Formula
B3:G15	B3	=COUNTIFS(data!$H:$H,$A3,data!$A:$A,B$2)
H3	H3	=SUM($B3:F3)

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

This is my code

    Dim Lrw As Long, lcol As Long
    Dim rng As Range
    
    Set rng = Range("A2")
    
    Lrw = Cells(Rows.Count, "B").End(xlUp).Row
    lcol = Cells(2, Columns.Count).End(xlToLeft).Column
    
    Range("B3", Cells(Lrw, lcol)).FillRight

    rng.Offset(0, lcol).Value = "Total"
    
    rng.Offset(1, lcol - 1).FormulaR1C1 = "=sum(rc2:rc[-1])"

What is the best way to copy the last cell on row 3 and copy it all the way down to the last row? Does that even make sense?

Thanks

 

[bstory84]

Possibly add these lines to your VBA

Dim mCol As Long
Dim mRow As Long
'====Get last row number - offset by 1 down.
mRow = Cells(1048576, 2).End(xlUp).Offset(1, 0).Row
'====Get Furthest right column number
mCol = Cells(3, 2).End(xlToRight).Column
'====Take value from Row3 furthest column Right and move it down to last row same column.
Cells(mRow, mCol).Value = Cells(3, mCol).Value

 

[288enzo]

Possibly add these lines to your VBA

Dim mCol As Long
Dim mRow As Long
'====Get last row number - offset by 1 down.
mRow = Cells(1048576, 2).End(xlUp).Offset(1, 0).Row
'====Get Furthest right column number
mCol = Cells(3, 2).End(xlToRight).Column
'====Take value from Row3 furthest column Right and move it down to last row same column.
Cells(mRow, mCol).Value = Cells(3, mCol).Value

Oddly, that copied the value in H3 and pasted it in H16.

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

Cell Formulas
Range	Formula
B3:G15	B3	=COUNTIFS(data!$H:$H,$A3,data!$A:$A,B$2)
H3	H3	=SUM($B3:G3)

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

I think, and forgive me I'm new, that a loop would be good somehow.

Using this

    Dim lcol As Long
    Dim rng As Range
    
    Set rng = Range("A2")
    
    lcol = Cells(2, Columns.Count).End(xlToLeft).Column
    
    rng.Offset(1, lcol).FormulaR1C1 = "=sum(rc2:rc[-1])"

something like if rng.Offset(i, lcol).FormulaR1C1 = "=sum(rc" & i +1 & ":rc[-1])"

I can't figure out the part where it would look in rng.Offset(i, lcol -1).

 

[288enzo]

I came up with something that almost gets me to the finish line.

    Dim LR As Long
    
    Dim lcol As Long
    Dim rng As Range
    
    Set rng = Range("A2")
    
    lcol = Cells(2, Columns.Count).End(xlToLeft).Column
    
    rng.Offset(1, lcol - 1).FormulaR1C1 = "=sum(rc2:rc[-1])"
    
    'LR = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 1 To 100
        If rng.Offset(i, lcol - 2).Value > -1 Then
        rng.Offset(i, lcol - 1).FormulaR1C1 = "=sum(rc2:rc[-1])"
        End If
    Next i

The snag is I want to stop the loop at the last row. I have 1 to 100 to test it. I need help on how to replace the 100 with finding the last row.

I tried another way using If rng.Offset(i, lcol - 2).Value = "" Then End If, I thought that could stop the loop.

 

[288enzo]

I don't know if there was a better way, but this worked

    Dim lcol As Long
    Dim rng As Range
    Dim lrow As Long
    
    Set rng = Range("A2")
    
    lcol = Cells(2, Columns.Count).End(xlToLeft).Column
    lrow = Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 1 To lrow - 2
        If rng.Offset(i, lcol - 2).Value > -1 Then
        rng.Offset(i, lcol - 1).FormulaR1C1 = "=sum(rc2:rc[-1])"
        End If
    Next i