How to count and copy entries with Excel VBA

Question

currently I am working with an Excel report that has 135.000 rows. There are assets listed in it and I want to count each asset and write it into another worksheet.

I have tried to write a VBA script, which you can find below. It just copies one entry but does not iterate over each row of the worksheet.

Sub assetVulnerabilityCount()
    With Sheets("tblExport")
        assetCount = 1
        rowMax = Sheets("tblExport").Cells(.Rows.Count, "F").End(xlUp).row
        currentAsset = Sheets("tblExport").Range("B" & row).Value
            For row = 2 To rowMax

                    If currentAsset = Sheets("tblExport").Range("B" & row).Value Then
                        Sheets("tblTarget").Range("B" & assetCount).Value = Sheets("tblTarget").Range("B" & assetCount).Value + 1

                        Sheets("tblTarget").Range("A" & assetCount).Value = currentAsset
                    Else:
                        currentAsset = Sheets("tblExport").Range("B" & row).Value
                        assetCount = assetCount + 1

                    End If

            Next Zeile
    End With
End Sub

Ideally, it would look like this:

Worksheet1:
Asset Names:
Laptop1337
Laptop1337
Laptop1337
PC420
PC420

Worksheet2:
Asset Name:    Amount:
Laptop1337     3
PC420          2

Worksheet1 is what I have and Worksheet2 is what I need.

Andreas Andreas · Accepted Answer · 2019-07-02T12:36:14

If we assume rowMax has the number of rows you need to iterate then

For row = 2 To rowMax

Should loop the rows if

Next Zeile

Is replaced with

Next row

Since it's row that is the variable in the loop.

How to count and copy entries with Excel VBA

1 Answers