Store data in array

[jfcby]

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

 

[Jim Cone]

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" <[email protected]>
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

 

[jfcby]

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

 

[James F Cooper]

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>

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