h = 3
Data = Array (Color, Item, Scent)

For x = 1 To 2
If data(x) = 0 Then GoTo Nex
h = h + 1
With ActiveSheet.PivotTables(PvtableName).PivotFields(F ieldCodes(x + 5))
.Orientation = xlColumnField
' #2 .PivotItems("(blank)").Visible = False
.Subtotals = Array (_
False, False, False, False, False, False, False, False, False,
False, False, False)
' .ShowAllItems = True
End With
' h = 11
ST = FieldCodes(x + 5)

Nex:
Next x

How do I find the last column in a worksheet? I know that the total column
count in excel is 256.
e.g€¦.
Item 1(Color) in my array need 300 columns but excel is maxed out at 256.How
do I get it to ignore item 1(Color) because it will require more than 256
columns and go to the next?

This is one method to find the last column. It returns a long integer that
represents the column index number for the last column that contains data.

Sub dk()
Dim Lastcol As Long
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", After:=sh.Range("A1"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
On Error GoTo 0
MsgBox Lastcol
End Sub

To get the last column of the sheet then:

Sub sl()
Dim shLC As Long
shLC = ActiveSheet.Columns.Count
MsgBox shLC
End Sub

Excel 2007 and later has more than 256 columns in a worksheet.


"mju" wrote in message
...
h = 3
Data = Array (Color, Item, Scent)

For x = 1 To 2
If data(x) = 0 Then GoTo Nex
h = h + 1
With ActiveSheet.PivotTables(PvtableName).PivotFields(F ieldCodes(x +
5))
.Orientation = xlColumnField
' #2 .PivotItems("(blank)").Visible = False
.Subtotals = Array (_
False, False, False, False, False, False, False, False, False,
False, False, False)
' .ShowAllItems = True
End With
' h = 11
ST = FieldCodes(x + 5)

Nex:
Next x

How do I find the last column in a worksheet? I know that the total column
count in excel is 256.
e.g..
Item 1(Color) in my array need 300 columns but excel is maxed out at
256.How
do I get it to ignore item 1(Color) because it will require more than 256
columns and go to the next?

Thanks alot for your response.

How do i go about including it in my loop statement to check if data will
require more then 256 columns. if yes, it should go to nex x and if no, it
should add item to the pivot table.

"JLGWhiz" wrote:

This is one method to find the last column. It returns a long integer that
represents the column index number for the last column that contains data.

Sub dk()
Dim Lastcol As Long
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", After:=sh.Range("A1"), _
LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, MatchCase:=False).Column
On Error GoTo 0
MsgBox Lastcol
End Sub

To get the last column of the sheet then:

Sub sl()
Dim shLC As Long
shLC = ActiveSheet.Columns.Count
MsgBox shLC
End Sub

Excel 2007 and later has more than 256 columns in a worksheet.


"mju" wrote in message
...
h = 3
Data = Array (Color, Item, Scent)

For x = 1 To 2
If data(x) = 0 Then GoTo Nex
h = h + 1
With ActiveSheet.PivotTables(PvtableName).PivotFields(F ieldCodes(x +
5))
.Orientation = xlColumnField
' #2 .PivotItems("(blank)").Visible = False
.Subtotals = Array (_
False, False, False, False, False, False, False, False, False,
False, False, False)
' .ShowAllItems = True
End With
' h = 11
ST = FieldCodes(x + 5)

Nex:
Next x

How do I find the last column in a worksheet? I know that the total column
count in excel is 256.
e.g..
Item 1(Color) in my array need 300 columns but excel is maxed out at
256.How
do I get it to ignore item 1(Color) because it will require more than 256
columns and go to the next?



.