PDA

View Full Version : [SOLVED] Creating an Array from a Table with only one row in it



Chris Macro
03-17-2014, 03:59 PM
I am trying to populate an array with data from a table. The problem is that when there is only one row in the table, my array variant-type variable decides it wants to be an array. Now I can think of multiple ways to approach this but was wondering if there were any quick an dirty ways to get my variable to always want to be an array? The reason I want it to be an array is because I have some For Loops later on that use Lbound and Ubound. Any thoughts would be greatly appreciated!


myArray = ActiveSheet.Range("AlwaysShow_TBL")

Kenneth Hobs
03-17-2014, 08:40 PM
To change it from an array/vector, use Application.Transpose.

snb
03-18-2014, 03:02 AM
This only occurs when your range is 1 cell (not if it's 1 row)

Chris Macro
03-18-2014, 04:21 AM
This only occurs when your range is 1 cell (not if it's 1 row)

Sorry, yes you are correct. I am only using a single column table and my problem is occurring when the databodyrange is only one cell. Forgot got to mention that piece of the puzzle!

Kenneth Hobs
03-18-2014, 05:01 AM
If that is the case, Dim the array (1,1).

Chris Macro
03-18-2014, 05:36 AM
If that is the case, Dim the array (1,1).

I'm not sure that will work with how I want to read in the data to the array. If it is already dimensioned, I would have to write a loop to read in the data instead of just setting my array equal to my table range.

I guess I was looking for a solution that went more along the lines of this:


Sub Test()


Dim myArray As Variant


y = ActiveSheet.ListObjects("AlwaysShow_TBL").DataBodyRange.Rows.Count


myArray = ActiveSheet.Range("AlwaysShow_TBL")
ReDim Preserve myArray(y, 1)


For x = LBound(myArray) To UBound(myArray)


Debug.Print x


Next x


End Sub

However, this code errors out when I try to redim. Is something similar to this a possibility or am I going to have to deal with loops?

Kenneth Hobs
03-18-2014, 05:41 AM
The forum can garble copy/pasted text but you need to clean it up for us to see the code. When pasting code, use code tags. If you don't know how, click the # icon in the toolbar. Click the Go Advanced button if that icon is not there. Paste between code tags. Pasting to an Excel range first sometimes makes a copy/paste work better. It is the copy from a forum and then past that adds the html tags.

Kenneth Hobs
03-18-2014, 05:47 AM
To see how command words work, press F1 in or near the word. For Preserve, you can see that you can only preserve data by redimming the last dimension in an array. Put the 1 as the first dimension. Use the transpose trick. You may need to check the value of y to do some other action if there is just one row or one cell. I would just need to know your data and goals to say much more.

If you post your workbook or a simple example, and explain what you are trying to do, we can help better.

Chris Macro
03-18-2014, 06:10 AM
Thank you so much Kenneth, adding that transpose trick got it working properly (with a little tweaking). Here's my final example code that seems to be working as expected!


Sub Test()


Dim myArray2 As Variant


For Each sht In ThisWorkbook.Worksheets
If sht.Name <> "Sheet1" Then sht.Visible = False
Next sht


y = ActiveSheet.ListObjects("AlwaysShow_TBL").DataBodyRange.Rows.Count


myArray = ActiveSheet.Range("AlwaysShow_TBL")
myArray2 = Application.Transpose(myArray)
ReDim Preserve myArray2(y - 1)


For x = LBound(myArray2) To UBound(myArray2)


Worksheets(myArray2(x)).Visible = True


Next x


End Sub

Chris Macro
03-18-2014, 06:43 AM
It appears I got ahead of myself. The above code still cannot handle if the DataBodyRange only has one cell in it.

snb
03-18-2014, 08:02 AM
I have no idea what you are working with.
Please post your workbook !

Chris Macro
03-18-2014, 08:03 AM
It appears I got ahead of myself. The above code still cannot handle if the DataBodyRange only has one cell in it.


Here's the best solution I could come up with. Is there a more efficient way of doing this?


Sub Test()

Dim myArray2 As Variant

'Hide All Sheets Except Sheet1
For Each sht In ThisWorkbook.Worksheets
If sht.Name <> "Sheet1" Then sht.Visible = False
Next sht

'How Many Entries in Table Body
y = ActiveSheet.ListObjects("AlwaysShow_TBL").DataBodyRange.Rows.Count

myArray = ActiveSheet.Range("AlwaysShow_TBL")


'Test if String or Array
If TypeName(myArray) = "String" Then
ReDim myArray2(0)
myArray2(0) = myArray
Else
myArray2 = Application.Transpose(myArray)
ReDim Preserve myArray2(y - 1)
End If

'Loop Through Sheets I Want Visible
For x = LBound(myArray2) To UBound(myArray2)
Worksheets(myArray2(x)).Visible = True
Next x

End Sub

snb
03-18-2014, 10:20 AM
Sub M_snb()
For Each cl In Sheet1.ListObjects(1).DataBodyRange
If cl <> "" Then Sheets(cl).Visible = True
Next
End Sub

Chris Macro
03-18-2014, 10:38 AM
You are awesome snb! Thanks for that solution. I had to make one modification to the code by changing cl to cl.value; I was getting a Type Mismatch error. Other than that, it worked perfectly. Here's my final solution:


Sub Test()

Dim cl As Range
Dim sht As Worksheet

'Hide All Sheets Except Sheet1
For Each sht In ThisWorkbook.Worksheets
If sht.Name <> "Sheet1" Then sht.Visible = False
Next sht


'Unhide sheet names in Table
For Each cl In Sheet1.ListObjects(1).DataBodyRange
If cl <> "" Then Worksheets(cl.Value).Visible = True
Next

End Sub

Bob Phillips
03-18-2014, 11:52 AM
Sub Test()
Dim sht As Worksheet

Worksheets("Sheet1").Visible = True
For Each sht In ThisWorkbook.Worksheets

If sht.Name <> "Sheet1" Then sht.Visible = Not IsError(Application.Match(sht.Name, Sheet1.ListObjects(1).DataBodyRange, 0))
Next sht
End Sub

Chris Macro
03-18-2014, 12:20 PM
I like it James T! (just kidding :hi:) Thanks for condensing my code even more xld!