Why does CountA produce an extra row

Question

My code produces an extra row, whenever I fill any data at row 4 column T.
If I leave row 4 column T empty, it will display data at row 5.

Sub All_N()
    Dim ws As Worksheet

    Sheets("sum").Activate
    Sheets("sum").Range("A5:T1048576").Select
    Selection.ClearContents
    Sheets("sum").Range("A5").Select

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "sum" Then
            cont1 = Application.WorksheetFunction.CountA(ws.Range("T3:T1048576")) + 2

            For a = 2 To cont1
                If ws.Cells(a, 20) = "N" Then
                    b = Application.WorksheetFunction.CountA(Sheets("sum").Range("T3:T1048576")) + 5
                    Sheets("sum").Cells(b, 1) = ws.Cells(a, 1)
                    Sheets("sum").Cells(b, 2) = ws.Cells(a, 2)
                    Sheets("sum").Cells(b, 18) = ws.Cells(a, 18)
                    Sheets("sum").Cells(b, 19) = ws.Cells(a, 19)
                    Sheets("sum").Cells(b, 20) = ws.Cells(a, 20)
                End If
            Next a
        End If
    Next
End Sub

Asger · Accepted Answer · 2019-06-01T08:07:31.203

2

I guess you want to loop over all used rows in all your sheets
and then fill in the next empty row in your "sum" sheet.

To find the last used row in column "T",
you may use .Cells(.Rows.Count, "T").End(xlUp).Row

Option Explicit

Sub All_N()
    Dim wsSum As Worksheet
    Dim ws As Worksheet
    Dim a As Long, b As Long

    Set wsSum = ActiveWorkbook.Sheets("sum")
    With wsSum
        .Range("A5:T" & .Rows.Count).ClearContents
        b = 5
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Name <> "sum" Then
                For a = 2 To ws.Cells(ws.Rows.Count, "T").End(xlUp).Row
                    If ws.Cells(a, "T") = "N" Then
                        .Cells(b, 1) = ws.Cells(a, 1)
                        .Cells(b, 2) = ws.Cells(a, 2)
                        .Cells(b, 18) = ws.Cells(a, 18)
                        .Cells(b, 19) = ws.Cells(a, 19)
                        .Cells(b, 20) = ws.Cells(a, 20)
                        b = b + 1
                    End If
                Next a
            End If
        Next
    End With
End Sub

It is not necessary to Select or Activate anything, see here.

edited Jun 01 '19 at 08:07

answered Jun 01 '19 at 07:34

Asger

3,822
3
12
37

> thanks for your input , if i would like to adjust the data display to start from row 5 , how can i do so , thanks again – Tan tan Jun 01 '19 at 08:04
Now I start with b = 5 and increment it as necessary. If it works for you, please consider to mark it as answer as described [here](https://stackoverflow.com/help/accepted-answer). If not, don't hesitate to comment here again. – Asger Jun 01 '19 at 08:08
when i click the button the initial data display showing at row 2 but when i click again the button the data showing at row 5 , the data showing at row 2 and row 5 is duplicate , so still got some minor correction needed, please help – Tan tan Jun 01 '19 at 08:18
Of course :) The code deletes everything starting in row 5 in sheet "sum", and then adds row-by-row starting in row 5. So everything above row 5 is old untouched data. So please decide: Where to start in "sum" (actually row 5) and where to start in all other sheets (actually row 2). – Asger Jun 01 '19 at 08:22
The sheet sum will display the result from row 5 onward but however data countstart from row 2 at others tab , as u can see my data at others tab data entry is start from row 2 onward , hope my explaination is not confuse – Tan tan Jun 01 '19 at 08:35
Then everything is fine now, as I did it so :) Just adapt your first 4 rows in "sum" manually once. They will remain untouched by my code. – Asger Jun 01 '19 at 08:36
Do u mean I need manually delete the row 2,3 data for the first time I click the button ? Anywhere to avoid that ? – Tan tan Jun 01 '19 at 08:52
Anyway to avoid the first initial data result showing at row 2 for the first time I click the button ? – Tan tan Jun 01 '19 at 09:48
I edited my code 3 hours ago directly after your first comment. Since then my code DOES NOT TOUCH "sum" ROWS 1 to 4. – Asger Jun 01 '19 at 11:41

Why does CountA produce an extra row

1 Answers1