Store data in array
 

Hello,

I have a workbook with hidden rows. How can I modify the following
code to store the hidden columns in an array?

<CODE BEGIN

Sub ArrayStore()
Dim i As Long 'Integer
Dim rng As Range

'1. Find last column with data
Set rng = Cells.Find(What:="*", After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious)
colnum = rng.Column + 1
'2. Store Hidden Columns
Dim hCol()
Dim hColI As Integer
For hColI = 1 To colnum
If Columns(hColI).EntireColumn.Hidden = 0 Then hCol() =
Array(hColI & ",")
Next

For i = LBound(hCol) To UBound(hCol)
MsgBox i & ", " & hCol(i)
Next

End Sub

<CODE END

Thank you for your help,
jfcby

Store data in array
 

Option Explicit ' First line in module!

Sub ArrayStore_R1()
Dim rng As Range
Dim hCol() As Long
Dim hColI As Long

'Declare all variables.
Dim colNum As Long
Dim N As Long

'1. Find last column with data
Set rng = Cells.Find(What:="*", After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious)
colNum = rng.Column + 1

'2. Store Hidden Columns
ReDim hCol(1 To colNum)

For hColI = 1 To colNum
If Columns(hColI).EntireColumn.Hidden = True Then
N = N + 1
hCol(N) = hColI
End If
Next
ReDim Preserve hCol(1 To N)

'Use column A to display the array contents...
Range("A1", Cells(N, 1)).Value = Application.Transpose(hCol)
End Sub
--
Jim Cone
Portland, Oregon USA




"jfcby"
wrote in message
Hello,
I have a workbook with hidden rows. How can I modify the following
code to store the hidden columns in an array?

<CODE BEGIN

Sub ArrayStore()
Dim i As Long 'Integer
Dim rng As Range

'1. Find last column with data
Set rng = Cells.Find(What:="*", After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious)
colnum = rng.Column + 1
'2. Store Hidden Columns
Dim hCol()
Dim hColI As Integer
For hColI = 1 To colnum
If Columns(hColI).EntireColumn.Hidden = 0 Then hCol() =
Array(hColI & ",")
Next

For i = LBound(hCol) To UBound(hCol)
MsgBox i & ", " & hCol(i)
Next

End Sub

<CODE END
Thank you for your help,
jfcby

Store data in array
 

Jim,

Thank you for your help.

I do not need the array data put in cells but to be able to hide the
rows back after I sort the worksheet.

How can I read the array stored data to hide the rows?

Thank you for your help,
jfcby

On Apr 7, 11:01*pm, "Jim Cone" wrote:
Option Explicit ' First line in module!

Sub ArrayStore_R1()
*Dim rng As Range
*Dim hCol() As Long
*Dim hColI As Long

'Declare all variables.
*Dim colNum As Long
*Dim N As Long

*'1. Find last column with data
* * Set rng = Cells.Find(What:="*", After:=Range("IV65536"), _
* * * LookIn:=xlFormulas, _
* * * LookAt:=xlPart, _
* * * SearchOrder:=xlByColumns, _
* * * SearchDirection:=xlPrevious)
* * *colNum = rng.Column + 1

*'2. Store Hidden Columns
* * *ReDim hCol(1 To colNum)

* * * *For hColI = 1 To colNum
* * * * * *If Columns(hColI).EntireColumn.Hidden = True Then
* * * * * * * N = N + 1
* * * * * * * hCol(N) = hColI
* * * * * *End If
* * * *Next
* * ReDim Preserve hCol(1 To N)

* *'Use column A to display the array contents...
* * Range("A1", Cells(N, 1)).Value = Application.Transpose(hCol)
End Sub
--
Jim Cone
Portland, Oregon *USA

"jfcby"
wrote in message
Hello,
I have a workbook with hidden rows. How can I modify the following
code to store the hidden columns in an array?

<CODE BEGIN

Sub ArrayStore()
*Dim i As Long 'Integer
*Dim rng As Range

*'1. Find last column with data
* * Set rng = Cells.Find(What:="*", After:=Range("IV65536"), _
* * * LookIn:=xlFormulas, _
* * * LookAt:=xlPart, _
* * * SearchOrder:=xlByColumns, _
* * * SearchDirection:=xlPrevious)
* * colnum = rng.Column + 1
*'2. Store Hidden Columns
* * Dim hCol()
* * Dim hColI As Integer
* * * *For hColI = 1 To colnum
* * * * * *If Columns(hColI).EntireColumn.Hidden = 0 Then hCol() =
Array(hColI & ",")
* * * *Next

* * For i = LBound(hCol) To UBound(hCol)
* * * * MsgBox i & ", " & hCol(i)
* * Next

End Sub

<CODE END
Thank you for your help,jfcby

Store data in array
 

Jim,

Thank you for your help.

Below is my full working code:

<CODE BEGIN

Option Explicit

Sub ArrayStore_R1()
Dim rng As Range
Dim hCol() As Long, hColI As Long, colNum As Long, N As Long, i As Long
Dim Msg As Variant

'1. Find last column with data
Set rng = Cells.Find(What:="*", After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious)
colNum = rng.Column + 1

'2. Store Hidden Columns
ReDim hCol(1 To colNum)
For hColI = 1 To colNum
If Columns(hColI).EntireColumn.Hidden = True Then
N = N + 1
hCol(N) = hColI
End If
Next
If N 0 Then ReDim Preserve hCol(1 To N)

'3. Display array contents example 1 in column A1 thru A?...
Range("A1", Cells(N, 1)).Value = Application.Transpose(hCol)

'4. Display array contents example 2 with msgbox single entry...
For i = LBound(hCol) To UBound(hCol)
MsgBox hCol(i)
Next i

'5. Display array contents example 3 with msgbox multi entry...
For i = LBound(hCol) To UBound(hCol)
If hCol(i) 0 Then Msg = Msg & hCol(i) & vbCrLf
Next i
If Msg = "" Then
MsgBox "No Hidden Columns Found!"
Else 'If Msg "" Then
MsgBox Msg
End If
End Sub

<CODE END

"jfcby" wrote in message
...
Jim,

Thank you for your help.

I do not need the array data put in cells but to be able to hide the
rows back after I sort the worksheet.

How can I read the array stored data to hide the rows?

Thank you for your help,
jfcby

On Apr 7, 11:01 pm, "Jim Cone" wrote:
Option Explicit ' First line in module!

Sub ArrayStore_R1()
Dim rng As Range
Dim hCol() As Long
Dim hColI As Long

'Declare all variables.
Dim colNum As Long
Dim N As Long

'1. Find last column with data
Set rng = Cells.Find(What:="*", After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious)
colNum = rng.Column + 1

'2. Store Hidden Columns
ReDim hCol(1 To colNum)

For hColI = 1 To colNum
If Columns(hColI).EntireColumn.Hidden = True Then
N = N + 1
hCol(N) = hColI
End If
Next
ReDim Preserve hCol(1 To N)

'Use column A to display the array contents...
Range("A1", Cells(N, 1)).Value = Application.Transpose(hCol)
End Sub
--
Jim Cone
Portland, Oregon USA

"jfcby"
wrote in message
Hello,
I have a workbook with hidden rows. How can I modify the following
code to store the hidden columns in an array?

<CODE BEGIN

Sub ArrayStore()
Dim i As Long 'Integer
Dim rng As Range

'1. Find last column with data
Set rng = Cells.Find(What:="*", After:=Range("IV65536"), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious)
colnum = rng.Column + 1
'2. Store Hidden Columns
Dim hCol()
Dim hColI As Integer
For hColI = 1 To colnum
If Columns(hColI).EntireColumn.Hidden = 0 Then hCol() =
Array(hColI & ",")
Next

For i = LBound(hCol) To UBound(hCol)
MsgBox i & ", " & hCol(i)
Next

End Sub

<CODE END
Thank you for your help,jfcby