# Converting from Ranges to Arrays to do calculations

#### JeffGrant

##### Board Regular
Hi All,

I am relatively new to arrays and find this a bit confusing.

I have a table, starting in cell A2, variable depth, but with constant width of 62 columns wide.

From Column N (col 14), I create a list of unique values in Column BL (col 64) -> all good

The idea is to count each incidence in Column N of the unique value in Column BL and output the count result to Column BM (col 54) -> all good, but slow.........

Enter Arrays..... because I am trying to loop one array (unique values), inside another (the raw data), I cant seem to get the syntax right. I am probably doing something most silly...

This is the basic code with all the extra bits removed.

Would somebody, please be so kind as to add the extra bits to turn this into an array sub, so I can compare it against my fruitless efforts.

VBA Code:
``````Sub DualAcceptanceTest()

Dim rngData As Range, rngUniqueData As Range
Dim lngRow As Long, RowCount As Long, lngCount As Long

'Select active sheet
Sheet5.Select 'Input Import

'Set up Data to search
Set rngData = Range("N3", Range("N3").End(xlDown))

'Set up Unique values to look for
RowCount = Range("N4").End(xlDown).Row
Range("BL3").Formula2 = "=unique(N3:N" & RowCount & ")"
Set rngUniqueData = Range("BL3", Range("BL3").End(xlDown))

With rngUniqueData

For lngRow = 1 To .Rows.Count

lngCount = Application.WorksheetFunction.CountIf(rngData, .Cells(lngRow, 1).Value)
.Cells(lngRow, 2).Value = lngCount

If lngCount > 3 Then
.Cells(lngRow, 2).Interior.ColorIndex = 6
End If

Next lngRow
End With

End Sub``````

#### Akuini

##### Well-known Member
You're welcome, glad to help & thanks for the feedback.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### JeffGrant

##### Board Regular
Hi Akuini, the work we did last night was nothing short of bloody excellent. Thanks man.
However, this morning it has created an unforeseen situation.

There are 63 incidents today of count >6, that require manual intervention.
So here is a a cheeky challenge for you, if you are interested .

If the value in Column BM is >=6 then
find the value in Column BL in Column N (which it always will because Col BL is the unqiue values of column N)
then if the value in Col S is found in the sting in Col BJ
delete range(a:bj)

Feel free to tell me to go away if you like.

 Col N Col S Col BJ Col BL Col BM horse name horse number scratched Unique horse name count of horse name Bajan Gold 5​ 15;16;17;18;19 Spiderbait 3​ Bajan Gold 5​ 15;16;17;18;19 Royal Inn 3​ Bering Sea 6​ 15;16;17;18;19 Ferdinand 3​ Bering Sea 6​ 15;16;17;18;19 Brother Max 3​ Bering Sea 6​ 15;16;17;18;19 Lowry 3​ Play Me Now 7​ 15;16;17;18;19 Inca Warrior 3​ Play Me Now 7​ 15;16;17;18;19 Ladies Man 3​ Play Me Now 7​ 15;16;17;18;19 Plushenko 3​ Buster Block 8​ 15;16;17;18;19 Swiss Kitty 6​ Buster Block 8​ 15;16;17;18;19 Tickle Me Almo 3​ Buster Block 8​ 15;16;17;18;19 Flavius 3​ Live The Moment 9​ 15;16;17;18;19 Hit The Switch 3​ Live The Moment 9​ 15;16;17;18;19 Tellmesomethingboy 3​ Live The Moment 9​ 15;16;17;18;19 Atomic Kitten 3​ Stubai 10​ 15;16;17;18;19 Taumalolo 3​ Stubai 10​ 15;16;17;18;19 Dance 'n' Sing 1​ Stubai 10​ 15;16;17;18;19 Mianjo 3​

Last edited:

#### Akuini

##### Well-known Member
I have a table, starting in cell A2, variable depth, but with constant width of 62 columns wide.
1. So it is an actual table not just a range, right? What's the table name?

then if the value in Col S is found in the sting in Col BJ
delete range(a:bj)
2. Do you want to delete the row or just the content?

3. Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive? And then share the link here.
This will make it easier to test & find a solution.

#### JeffGrant

##### Board Regular
Hi Akuini,

1. yes it is a table. It is a result of a PQ. Table name is "input"
2. Only want to delete the range(a:bj) because the unique data and count are to the right of the table in columns BL & BM
3. I can up load the data no problem.

#### JeffGrant

##### Board Regular

just trying to work out how to share a dropbox link - sorry

#### JeffGrant

##### Board Regular
Ah Got it.. because off of the problems I had until last weekend, Dropbox was not enabled at startup. But anyway, here it is.....

#### JeffGrant

##### Board Regular

With the range(a:bj), if the information to the right was elsewhere, then there is no problem deleting the entire row, but at the end of the day, all of the content in that row needs to be deleted. I cant have blank lines in this data because the procedures that follow this step use .currentregion property quite a lot.

#### JeffGrant

##### Board Regular
Hi Akuini,

here is an easy way of doing it.

1. In a helper column add the formula =IFERROR(IF(FIND(S3,BJ3,1)>0,"Delete",""),"")
2. Autofilter on the word DELETE
3. Delete the Visible cells only.

That works easy enough, hey?

That gets rid of any horse that is scratched anyway..

#### Akuini

##### Well-known Member
Ok, glad you found a working solution.

Replies
0
Views
48
Replies
3
Views
253
Replies
7
Views
142
Replies
0
Views
99
Replies
40
Views
2K

1,147,560
Messages
5,741,830
Members
423,689
Latest member
Jords998

### 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.

### Which adblocker are you using?

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

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