redim preserve variant syntax

[Atom]

What is the syntax to redim a 1 dimensional array as follows?

dim MyArray as variant
set MyArray = range("A1:A300")
'now redim MyArray to size 100
redim preserve ????????????????

[maras_mak]

MyArray array is two-dimensional and should be done like this

Dim MyArray As Variant

MyArray = Range("A1:A300").Value
ReDim MyArray(1 To 100, 1 To 1)

Regards.

[Fluff13]

MyArray will be a 2D array not a 1D array
If you want it as a 1D array try

Please Login or Register  to view this content.

[MrShorty]

One correction: As written MyArray is not a 1 dimensional array, it is a 2D object variable referring to a spreadsheet range. Redim is used to change the dimensions of a VBA array, but cannot change a spreadsheet range.

It probably depends somewhat on how this little snippet fits into your project.

Assuming that you intended to use MyArray as a VBA array and it will always contain a single column of data, I would suggest that you find some way to convert the 2D range to a single dimension array. In my quick tests, Excel's Transpose function (accessed in VBA via the Application or WorksheetFunction objects https://docs.microsoft.com/en-us/off...n-visual-basic ) can be used to obtain a 1D VBA array from a 1D Excel range.

Please Login or Register  to view this content.

Again, remember that this assumes that you want to use MyArray as a VBA array, and not a range.

If your true intention is to use MyArray as a range object, then you would use the Resize method rather than the Redim Preserve https://docs.microsoft.com/en-us/off...l.range.resize

Please Login or Register  to view this content.

Solution really depends on what you are really trying to do with MyArray. Those two options should give you something to consider.

[Atom]

The spreadsheet i am pulling the data from contains formatted strings. The number of rows with important data is variable, so my plan was to simply read in all of the rows, or more of them than I need, and then discard any extraneous data (will always be at the end). Having extraneous data is not critical because I will be comparing parts of the data to see if it compares with known values, and then changing the corresponding cell background color which will identify that item by type. Then I'll need to count up the number of items of each type and write that out to a table. I already did this with a function, but it was very slow and limited the things I needed to do. To speed things up I'm doing this with arrays. I'm simply asking how truncate the array beyond the area where I have unneeded data. i could do this by looking at the range before I create the array, but I thought it would be easier once the data was in the array.

When I run code after putting the range into the array
For i = 1 To UBound(Cars)
Debug.Print Cars(i, 1)
Next i
I see the correct data; if I used transpose, I don't.
I still have my original question. How do I reduce the size of the array to discard the elements I want beyond some array index (the usable data is always contiguous), and retain the data of interest.

[MrShorty]

Must be missing some context or something, because I don't understand why you cannot see correct data after transpose. When I do something like Fluff13 suggests or my first suggestion using transpose, I see the assigned data in the array just fine.

If you put a stop statement (or breakpoint or other method of entering debug mode) before this latest loop, what is in Cars? Is Cars a Range or an array of string? Is Cars 1D or 2D?

[Fluff13]

Not sure how you determine what constitutes "Usable Data", but you could do something like this

Please Login or Register  to view this content.

If your data looked like
Latitude
51.750185
51.750259
51.721887
51.721887
51.729576
51.706427
51.702469
51.750239
51.76998
51.740198
51.760046
51.753759
51.762331
51.774636
51.800344
51.827018
51.811721

[Atom]

Since I am getting the data from column, I don't think it should have to be transposed. In any case, I am reading in a column (range), and when I look a the array afterwards, all the data is in the right place. I'm familiar with transpose and have used it at other times.

For the moment I have circumvented the resizing issue by determining the necessary size and the populating a second array which has only the needed data.

[protonLeah]

".... discard any extraneous data (will always be at the end) ..."
How do you recognize "extraneous" data? Can it be tested for and the loop and exited when the first one is found?

[Atom]

Here is the data as it appears in column A of the spreadsheet:
SHPX214357 T28
SHPX214287 T28
TCBX280061 T28
PROX 39461 T32
TILX302962 T32
GATX 70089 T22
GATX 69504 T22
GATX 69455 T22
GATX 69507 T22
GATX 69466 T22

here is how I get that data into my array:
Cars = RepInq.Sheets(1).Range("A12:A300")

Here is what I get when I print from Cars:
SHPX214357
SHPX214287
TCBX280061
PROX 39461
TILX302962
GATX 70089
GATX 69504
GATX 69455
GATX 69507
GATX 69466

using this:

Please Login or Register  to view this content.

This is a railroad car inventory and tracking system.

[Atom]

That's what I've done, after a fashion. See my other post.

During this I've added an Option Base 1 statement; just fyi. Not sure if that was part of problem or not.

[Fluff13]

With the way that the array has been populated Option Base 1 won't make any difference.
From the data you have shown why are you testing for a string length of 65?
Do you just want to get all the data upto the first line with "Sub-Tot"?

Can you supply a workbook?

[Atom]

The lines are strings of length 65, but the only data I really need for right now is the left 10 characters (the car number). it's more than one workbook, and I don't want to share the information in public sphere. As I said, I've gotten through the issue of only selecting the lines I want. Thanks for helping out. I would still like to understand why it works the way it does, so if I get a chance I'll create a test workbook that I can share.