0

I have a question wonder if anyone can help me out.

I'm doing a project that requires to get a summary of tests result. My flow is as follow,

First, I would need to open my collected test results, then creating a pivot table, after setting accordingly to what i need to view, i will copy the table into another spreadsheet. Over at this spreadsheet, it will change some of the names to the required names and finally, it will be copied to the summary sheet. Then i will need to tabulate the summary in % also.

I'm facing with an issue here which is, for my collected test results, when i put it into the pivot table, i cant determine the number of columns that will appear. For example

In on example, we can have enter image description here

In another case, we can have

enter image description here

If u notice the second image has more columns used. Therefore, I was wondering if there is any way that you guys can share with me to determine the last available column with details to be copied.

And can i get the number of the last column so that i can get the summary as well since i need to put it in the form of %.

At the moment, i'm using "Range(A1:Z1000000)" which is quite impossible for me to do anything as i cant really find the % manually.

btw, i need it in VBA code.

Will appreciate for all the opinions and options provided! Thank you in advance!

Community
  • 1
  • 1
user1204868
  • 606
  • 6
  • 15
  • 31

2 Answers2

1

This should do the trick:

Sub test()

  Dim maxColumn As Long

  'Create the table here
  maxColumn = getMaxUsedColumnNumber("PivotTableSheet")
  'do what you need with the number

End Sub

Function getMaxUsedColumnNumber(sheetName As String) As Long
  With Sheets(sheetName)
    getMaxUsedColumnNumber = .Cells.Find(What:="*", after:=.Range("A1"), LookIn:=xlFormulas, _
            SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
  End With
End Function
assylias
  • 321,522
  • 82
  • 660
  • 783
  • hi, but i need it to run in a flow, i cant break the flow and add in the number, then it will just slow the whole process down... Is there any other way that i can determine it and still continue the copying flow without slowing down the pace? – user1204868 Feb 22 '12 at 13:51
  • Not sure I understand - the code I posted gives you the last column in your sheet. You can integrate that piece of code in your VBA code and do what you need with the result... – assylias Feb 22 '12 at 13:56
  • hmm.. but it is doing a search, but how do i compile it together with my other codes? i'm not very sure about it... – user1204868 Feb 22 '12 at 14:00
  • hi, does the function stay inside or outside the end sub? i cant compile it – user1204868 Feb 22 '12 at 14:09
  • i saw the error already, but another error surfaced, i copied my pivot table into another sheet named sheet1, but it cant work, so, the sheetname = sheet1 and the pivottablesheet? a runtime-13 error – user1204868 Feb 22 '12 at 14:12
  • Then you need to change the example I gave to: `maxColumn = getMaxUsedColumnNumber("Sheet1")` – assylias Feb 22 '12 at 14:30
  • oh.. ok.. thanks.. but can u show me how to implement it if i were to copy the table into another spreadsheet? i'm not sure how to do it... i have a set of codes to copy and paste the table into another sheet but i cant figure out a way that i can use your code and add it in. – user1204868 Feb 23 '12 at 00:39
0

You can use .SpecialCells() as follows:

ActiveSheet.UsedRange.SpecialCells(xlLastCell).Column

Alternatively, take a look at the ColumnRange property of the PivotTable object. It has a Count property which will return the number of columns in the Pivot Table which could also help.

markblandford
  • 3,153
  • 3
  • 20
  • 28