• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 30
  • Last Modified:

VBA - Loop Each header to get non blank cells and paste result

Experts - I have alot of data in "Sheet1". Now, I have 615 columns. this may increase / decrease in future.

Is there any macro which can create a new "Sheet2" next to "Sheet1", copy all headers from Sheet1(first row) and paste it in transverse in "Sheet2" and give me the non blank counts for each column?
0
Manju
Asked:
Manju
  • 3
  • 2
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try
Sub macro()
Sheets("Sheet1").Activate
Set Rng = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
Rng.Copy
Sheets.Add after:=Sheets("Sheet1")
Set sh = ActiveSheet
sh.Range("A1").PasteSpecial Transpose:=True
For Each c In Rng ' not counting header
    sh.Cells(c.Column, 2) = WorksheetFunction.CountA(c.EntireColumn) - 1
Next
End Sub

Open in new window

Regards
0
 
ManjuIT - Project ManagerAuthor Commented:
Hi - This works perfectly fine. Just need 1 tweak though. In sheet2, how can i start the data from 2nd row onwards?
0
 
Rgonzo1971Commented:
then try
Sub macro()
Sheets("Sheet1").Activate
Set Rng = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
Rng.Copy
Sheets.Add after:=Sheets("Sheet1")
Set sh = ActiveSheet
sh.Range("A2").PasteSpecial Transpose:=True
For Each c In Rng ' not counting header
    sh.Cells(c.Column+1, 2) = WorksheetFunction.CountA(c.EntireColumn) - 1
Next
End Sub

Open in new window

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
ManjuIT - Project ManagerAuthor Commented:
Apologies again:

Worksheets(1).Activate
Set Rng = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft))
Rng.Copy
Sheets.Add after:=Worksheets(1)
Set sh = ActiveSheet
sh.Range("A2").PasteSpecial Transpose:=True
For Each c In Rng ' not counting header
    sh.Cells(c.Column + 1, 2) = WorksheetFunction.CountA(c.EntireColumn) - 1
Next

I changed the sheet1 to worksheets(1) as the name of the sheet will change from file to file. but i get class error.
0
 
ManjuIT - Project ManagerAuthor Commented:
Perfect 10
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may also give it a try...
Sub CountDataInColumns()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lc As Long, c As Long, r As Long
Set ws1 = Sheets("Sheet1")

On Error Resume Next
Set ws2 = Sheets("ValueCount")
ws2.Cells.Clear
On Error GoTo 0

If ws2 Is Nothing Then
    Set ws2 = Sheets.Add(after:=ws1)
    ws2.Name = "ValueCount"
End If

lc = ws1.Cells(1, Columns.Count).End(xlToLeft).Column
r = 2
For c = 1 To lc
   ws2.Cells(r, 1) = ws1.Cells(1, c)
   ws2.Cells(r, 2) = Application.CountA(ws1.Columns(c))
   r = r + 1
Next c
End Sub

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now