# Array Values based on IF THEN condition

#### anshikam

##### Board Regular
Hello All!

Is it possible to create an array from a database based on a specific value in a column.

I have a excel spreadsheet with 2500 rows and 100 columns.
I need to create an array for all columns but rows only if col x has certain values.

Since its a large no of coloumns and rows I am using the below to store data in the array vs assigning values manually.

myArray = myTable.Range

Is it possible and if yes how can it be done?

Anshika

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Norie

##### Well-known Member
Does myArray currently store all the data?

#### Norie

##### Well-known Member
You could try something like this.

1 Create a new array with the same dimensions as myArray but transposed.

2 Loop through myArray, checking the relevant column as you go.

3 If you find one of the values you are looking for in the column add the row to the new array, keeping a count of the no of rows being added.

4 Resize the new array using the count from 3.

5 Transpose the new array to get rows/columns the 'right' way round.

#### anshikam

##### Board Regular

You could try something like this.

1 Create a new array with the same dimensions as myArray but transposed.

2 Loop through myArray, checking the relevant column as you go.

3 If you find one of the values you are looking for in the column add the row to the new array, keeping a count of the no of rows being added.

4 Resize the new array using the count from 3.

5 Transpose the new array to get rows/columns the 'right' way round.
Thanks though would you have some code on this ?

#### anshikam

##### Board Regular
Thanks though would you have some code on this ?
So i am able to loop trough he first Column and check for values however the value i need to check is the 7th col and would like to add the entire row only if the value corresponds to 4 diff values else skip the row. I have not found anything on the internet to define the 4 values and check for them .......

#### Norie

##### Well-known Member
You wouldn't loop through the columns, you would loop through the rows and check the relevant column.

Something like this.
VBA Code:
``````Dim myArray() As Variant
Dim newArray() As Variant
Dim cnt As Long
Dim idxCol As Long
Dim idxRow As Long

myArray = myTable.Range.Value

ReDim newArray(1 To UBound(myArray, 2), 1 To UBound(myArray, 1))

For idxRow = LBound(myArray, 1) To UBound(myArray, 1)

' check value in column 7
Select Case myArray(idxRow, 7)
Case "Value1", "Value2", "Value3", "Value4"
cnt = cnt + 1
For idxCol = LBound(myArray, 2) To UBound(myArray, 2)
newArray(idxCol, cnt) = myArray(idxRow, idxCol)
Next idxCol
Case Else
' do nothing
End Select
Next idxRow

If cnt > 0 Then
ReDim myArray(1 To UBound(myArray, 2), 1 To cnt)
myArray = Application.Transpose(myArray)
End If``````

Replies
2
Views
114
Replies
6
Views
354
Replies
4
Views
88
Replies
5
Views
117
Replies
0
Views
32

1,127,753
Messages
5,626,667
Members
416,199
Latest member
Gautamsunil

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back