dynamically count number of columns in an excel sheet

Question

I have an excel sheet named InputData. I have 9 different columns present at the moment. I want to have a loop which counts the number of columns in the excel sheet. Also, for each value of the counter, I want to select that individual column and the last column (to be selected default for each iteration). Once I have selected the necessary columns, the idea is to do a pivot table and draw a bar chart. Then again dynamically goto the next column via the counter value and the last column(to be selected default for each iteration) and so on. I am not able to figure out how to go about it. Any help in this matter is highly appreciated. Thanks in advance!!

For i = 1 To lastColno
' Pivot Table Code
    Range("B1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
Sheets("BuildData").Select
Range("J1").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
Range("B1").Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "Sheet1!R1C1:R2641C2", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion _
    :=xlPivotTableVersion14
Sheets("Sheet2").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("_KOLA_5SX")
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Claim?")
    .Orientation = xlColumnField
    .Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Claim?"), "Count of Claim?", xlCount
Next i

Have you gotten anything so far? Any progress?

basodre
– basodre

2015-02-23 16:21:48 +00:00
Commented Feb 23, 2015 at 16:21 — basodre
– basodre, Commented Feb 23, 2015 at 16:21
Attach some code so we can review what you have attempted.

Kalel Wade
– Kalel Wade

2015-02-23 16:22:58 +00:00
Commented Feb 23, 2015 at 16:22 — Kalel Wade
– Kalel Wade, Commented Feb 23, 2015 at 16:22

Alejandro · Accepted Answer · 2015-02-23 16:37:12Z

1

I am going to assume you've attempted a few things know the basics of excel-vba (for future questions and maybe this one as well, show the work you've done so far).

I would create a formula cell that counts how many columns there. Put it in another sheet so the formula is =COUNTA('InputData'!1:1). COUNTA counts the number of cells that aren't empty in the range. Assuming there are no blank columns, this will give you the location of the last column. You can then use that in your VBA loop.

If your formula field is in cell "A1" then the loop might look like:

Dim lastColumn As Integer
lastColumn = Range("A2").Value
For i = 1 To lastColumn
    # rest of your code here
Next i

Try this vba tutorial for tips on how to grab the columns and go about making your pivot table. Good luck. Hope this helps you get started.

answered Feb 23, 2015 at 16:37

Alejandro

1,2169 silver badges11 bronze badges

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

Comments

Dubison · Accepted Answer · 2015-02-23 16:29:57Z

0

Unfortunately I will not be able to provide you your answer however following code can guide you through your start. Following code will find your last column and repeat your code until "i" hits your column number. In between you can add your code which you want to repeat.

Sub rowvscol()
Dim lastColno As Long
Dim lastCol As Range
lastColno = Cells(1, Columns.Count).End(xlToLeft).Column
Set lastCol = Range(lastColno & ":" & lastColno).EntireColumn
    For i = 1 To lastColno
    ' Pivot Table Code Goes here
    Next i
End Sub

Good luck.

answered Feb 23, 2015 at 16:29

Dubison

7665 silver badges12 bronze badges

1 Comment

Lone_Star Over a year ago

Hey Guys, thank you so much for helping me with this. I am a newbie in VBA and I worked on the suggestions you provided and so far have made good progress. The part where I am stuck right now is after the first loop iteration, the code is simply not able to plot the pivot table for the next column. I think the reason being is the worksheets which are not dynamic. I will post the code with the Pivot table. How do I fix this code to loop through the number of column iterations and spit out the pivot tables for each of these columns with the last column selected by default for the pivot plot.

Collectives™ on Stack Overflow

dynamically count number of columns in an excel sheet

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related