Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Store array ??? | Excel Worksheet Functions | |||
How to store the reference of Workbooks in an array. | Excel Programming | |||
Can you store a value in different levels of a multi array? | Excel Programming | |||
How Do You Take Data Off A Sheet and Store It In An Array | Excel Programming | |||
Lookup and store in array | Excel Programming |