Last Row and Last column

[vmjan02]

I have this code and idea is clear to apply formula till the last row and last column, i am not sure as what is wrong with this code.

Sub Countdata()
    Dim dERow As Double
    Dim lECol As Long
    
    dERow = Cells(10000, 4).End(xlUp).Row
    lECol = Cells(10000, 5).End(xlUp).Row
                
    Cells(4, 5).Formula = "=COUNTIFS('Actual DailyData'!$B:$B,$D4,'Actual DailyData'!$A:$A,E$3)"
    Cells(4, 5).AutoFill Destination:=Range(Cells(4, 5), Cells(4, 5 + lECol - 2)) '- 1))
    Range(Cells(4, 5), Cells(4, 5 + lECol - 2)).AutoFill Destination:=Range(Cells(5, 5), Cells(dERow, 5 + lECol - 2))
End Sub

the range of columns and rows are dynamic

Date	Emp1	Emp2
09-02-2022​	6​	8​
13-02-2022​
13-02-2022​

 

[Saurabhj]

Are you using lECol variable to get total number of coloumns?

Then it should be

lECol = Cells(1, columns.count).End(xltoleft).column

 

[vmjan02]

lECol = Cells(1, columns.count).End(xltoleft).column

Kool, worked will

 

[vmjan02]

@Saurabhj

Have modified the script

Sub Countdata()
    Dim dERow As Double
    Dim lECol As Long
    
    dERow = Cells(10000, 4).End(xlUp).Row
    'lECol = Cells(10000, 5).End(xlUp).Row
    lECol = Cells(1, Columns.Count).End(xlToLeft).Column
            
    Cells(4, 5).Formula = "=COUNTIFS('Actual DailyData'!$B:$B,$D4,'Actual DailyData'!$A:$A,E$3)"
    Cells(4, 5).AutoFill Destination:=Range(Cells(4, 5), Cells(4, 5 + lECol))  '- 1))
    
    On Error Resume Next
    Range(Cells(4, 5), Cells(4, 5 + lECol)).AutoFill Destination:=Range(Cells(4, 5), Cells(dERow, 5 + lECol))
End Sub

Its is not working for a new column entry, it is only working on 2 cloumns

eg:- function is applied to only Emp 1 and Emp 2 (column) and not applied to Emp 3

 

[Saurabhj]

Its is not working for a new column entry, it is only working on 2 columns

Are you saying that after entering a new data if you run the code it's not auto filling the formula for new data.

 

[vmjan02]

yes, row wise it is working but not column wise

 

[Saurabhj]

Kindly share your data using XL2BB tool. It would help to understand the issue.

 

[vmjan02]

I have shared the table in my 1st post

 

[vmjan02]

Have fond the solution.

Sub Countdata()
Dim last_row, last_col As Long

last_row = Cells(Rows.Count, 4).End(xlUp).Row
last_col = Cells(3, Columns.Count).End(xlToLeft).Column
Range(Cells(4, 5), Cells(last_row, last_col)).Formula = "=COUNTIFS('Actual DailyData'!$B:$B,$D4,'Actual DailyData'!$A:$A,E$3)"

End Sub