How to CountA for Column Where Header = Something

Hey Guys,

I've been trying to make a macro that sota summarizes the counts of certain Columns that have certain names.

NO no based on position on the cells but Column Header Name.

Sub Summarize()

Dim a As Long
a = Application.WorksheetFunction.CountA(Cells(2, 1).Resize(9, 1))
MsgBox Cells(1, 1) & Chr(10) & a

End Sub

This code works on the first column but I want it to be Column Header Specific..

likeso: counta for column where column_name="SKUs"

Thanks

Alfred

hi
try this

Sub Summarize()

Dim a As Long, b As Long
Dim h As String

h = InputBox("choose header")
b = Application.WorksheetFunction.Match(h, Rows(1), 0)

a = Application.WorksheetFunction.CountA(Cells(2, b).Resize(9, 1))
MsgBox Cells(1, b) & Chr(10) & a

End Sub

I am not sure that this is what You want but try on your example this code

Sub Summarize()

Dim a As Long, b As Long
Dim h As Integer

For b = 1 To 4
a = Application.WorksheetFunction.CountA(Cells(2, b).Resize(9, 1))
MsgBox Cells(1, b) & Chr(10) & a
Next

End Sub

next try

Sub Summarize()
Dim a As Long, b As Long
Dim c As Range
Dim last As Long
last = Cells(1, Columns.Count).End(xlToLeft).Column
For Each c In Range(Cells(1, 1), Cells(1, last))
If c.Value Like "SKUs" Or c.Value Like "Description" Or c.Value Like "header1" Or c.Value Like "header2" Then
b = c.Column
a = Application.WorksheetFunction.CountA(Cells(2, b).Resize(9, 1))
MsgBox Cells(1, b) & Chr(10) & a
End If
Next
End Sub

Sub Summarize()
Dim a As Long, b As Long
Dim c As Range
Dim last As Long
Dim x As String
last = Cells(1, Columns.Count).End(xlToLeft).Column

For Each c In Range(Cells(1, 1), Cells(1, last))
If c.Value Like "SKUs" Or c.Value Like "Description" Or c.Value Like "header1" Or c.Value Like "header2" Then
b = c.Column
a = Application.WorksheetFunction.CountA(Cells(2, b).Resize(9, 1))
x = x & Chr(10) & Cells(1, b) & Chr(10) & a
End If
Next
MsgBox x
End Sub

last row in column 1 you can find this way:

rlast = Cells(Rows.Count, 1).End(xlUp).Row