Looping through worksheets and trying to count the data in columns

Question

I have a workbook of ten sheets. In sheet 1, I want to list out sheet names (sheets 3 thru 10), column heading values in the sheet (columns 8 and beyond only) and for that column the number of cells that have data in it.

My code works for two of these three requirements. On my sheet 1 (named: SheetName Columns) I get the Sheet Names in column A and Column Heading in column B, however not having any luck getting that sheet/columns number of data rows.

On my sheet 1, column A gets duplicated per number of columns after column 7 on that sheet and that is fine.

Sub ListColumnHeadings()

Dim cNbrs As Long, i As Integer, tr As Long, tc As Long, wst As Worksheet
Dim charList(300, 300) As String

Dim ws As Worksheet, OutputRow As Long
Dim myRange As Range
Dim NumRows As Integer
Dim colNbr As Range

Set shSkip1 = ThisWorkbook.Sheets("SheetName Record Cnt")
Set shList = ThisWorkbook.Sheets("SheetName Columns")

OutputRow = 1
On Error Resume Next

For Each ws In Worksheets
  If ws.Name <> shList.Name And ws.Name <> shSkip1.Name Then

    cNbrs = ws.Range("A1").CurrentRegion.Columns.Count

    For i = 8 To cNbrs
      shList.Cells(OutputRow, "A").Value = ws.Name
      shList.Cells(OutputRow, "B").Value = ws.Cells(1, i)

      Set myRange = ws.Columns(i).Select

      NumRows = ws.Application.WorksheetFunction.CountA(myRange)
      If NumRows > 0 Then
        shList.Cells(OutputRow, "C").Value = NumRows
      End If

      OutputRow = OutputRow + 1
    Next i

  End If
Next ws


End Sub

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

It's because of your use of Set myRange... You don't need to .Select it. Just change that line to Set myRange = ws.Columns(i)

If you want to leave .Select, then the next line should be NumRows = ws.application.worksheetfunction.counta(selection), but it is highly recommended you avoid using .Select, this is just for your info.

edited May 23 '17 at 12:14

Community

1
1

answered Nov 03 '15 at 22:09

BruceWayne

22,923
15
65
110

1

It's worth noting that if OP hadn't used `On Error Resume Next`, the error would have been a lot more easily visible and debuggable. – nwhaught Nov 03 '15 at 22:16
@nwhaught - I knew there was a reason I don't use those statements (they can hide some info for debugging), good call! – BruceWayne Nov 03 '15 at 22:24
1

Awesome, this small tweak makes it work perfectly. I'm just learning this VBA and seems like a lot of functions and properties to figure out how they work. Thanks again, and I did remove that "on error resume next" statement... – DavidKn Nov 04 '15 at 15:43
@DavidKn - I found that using the macro recorder greatly helps in learning VBA. Record some mundane tasks, and try to change it up a bit...since the macro recorder records your steps exactly, in the code, try to learn how to change it and make it more dynamic. And of course, Google and SO is a great resource when you're stuck. – BruceWayne Nov 04 '15 at 16:05
1

@BruceWayne - will use the macro recorder and debugger. thanks again for your quick response and help! – DavidKn Nov 04 '15 at 22:32
@DavidKn - also, you can press F8 when in a macro to go through it line by line. The only time I do use `.Select` is when walking through a macro, I'll use say `Range(Cells(1,1),Cells(x,y)).Select`, then I can see my range being selected, to confirm if it's working or not. Then in the "final version", I comment out that line. – BruceWayne Nov 04 '15 at 22:34

Looping through worksheets and trying to count the data in columns

1 Answers1