Counting data across multiple sheets

Question

I want to count the number of cells with data in a column.

I have to count these cells from all the workbooks.

For example, I have 2 workbooks: WB1 and WB2.

Worksheet WS1 with data in A1, A2, A3, A4
Worksheet WS2 with data in A1, A2.
My output should show 6.

How can I count this?

Option Explicit

Private Sub CommandButton1_Click()

    Dim paths(), wbs() As Workbook
    Dim x As Integer
    Dim sh, rn, k

    paths = Application.GetOpenFilename(FileFilter:="Excel Files (*.XLSX), *.XLSX", _
      MultiSelect:=True, Title:="Please browse all the Rawdata files")

    For x = 1 To UBound(paths)
        ReDim wbs(UBound(paths))
        Set wbs(x) = Workbooks.Open(paths(x))

        Set sh = wbs(x).Sheets("Role ID - Description")
        Set rn = sh.UsedRange
        k = rn.Rows.Count + rn.Row - 1 - 1
        wbs(x).Close
    Next

End Sub

what kind of "trouble" do you have?

DisplayName
– DisplayName

2018-12-27 16:02:15 +00:00
Commented Dec 27, 2018 at 16:02 — DisplayName
– DisplayName, Commented Dec 27, 2018 at 16:02

Cyril · Accepted Answer · 2018-12-27 16:27:52Z

3

You are not maintaining your counter from sheet to sheet for a workbook (e.g., k = k + 1 to iterate by 1). Inside of your workbook you would want something such as:

dim i as long, k as long, lr as long
With Workbook("blah")
    For i = 1 to .sheets.count
        With .Sheets(i)
            lr = .cells(.rows.count,1).end(xlup).row
            k = k + lr
        End with
    Next i
    'output k to some destination
    k=0
End with

Edit1:

Adding counta scenario per Kubie's recommendation (untested):

dim i as long, k as long, cnta as long
With Workbook("blah")
    For i = 1 to .sheets.count
        With .Sheets(i)
            cnta = application.counta(.columns(1))
            k = k + cnta
        End with
    Next i
    'output k to some destination
    k=0
End with

edited Dec 27, 2018 at 16:27

answered Dec 27, 2018 at 16:05

Cyril

6,8291 gold badge21 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kubie Over a year ago

since the goal appears to be counting the number of cells with data, maybe should use CountA instead of finding last row for each sheet? (unless no rows are blank)

Cyril Over a year ago

@Kubie Good point; i had assumedfrom the OP that the lines were contiguous, per the example, which wouldn't necessarily be correct. The same scenario where k is maintained would exist... i'll add your scenario, too, just to be safe.

Collectives™ on Stack Overflow

Counting data across multiple sheets

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related