Finding the last column
 

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?

Finding the last column
 

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?

Finding the last column
 

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?



.