VBA - ranges -

[heathball]

Im learning a little VBA, but I'm stuck with a range issue.

Im starting with columns A,B,C
and i want to achieve D

So my range is effectively in column A (each name change represents a new 'range' - and they are in alphabetical order down the page)
I'm currently using an 'average if' helper column in macros to help me with this, but the problem is i have big files (over 150,000 rows), and 'average if' takes a very long time (I do many of these during a macro)

within my code, im trying to use variations of 'dim' but I'm not getting anywhere.

Is there something i use with 'dim' to select column A, and use each name (each range begins with a name change moving down the column) as individual ranges, so that when i apply a function or sum in column D, and run it down the page in my macro, i can get the result as per the table below? then i can add this code ( i need this for many things, on different sheets and different workbooks, and if the range column "A" changes, i can change that reference in the code to suit the file )

I would like to use it with the various functions i use now ('if' 'and' 'or' etc) and I"m looking for something flexible that excludes specific object references to worksheet/column names etc.

Is something possible with this, or am i stuck with my slow average-if helper columns?

A NAME	B High	C Low	D high minus low
barry1000			6
barry1000	15		6
barry1000		9	6
barry2000		4	4
barry2000	8		4
barry3000			6
barry3000	12		6
barry3000			6
barry3000			6
barry3000		6	6
barry4000	26		17
barry4000		9	17

<tbody>
</tbody>

 

[heathball]

well - the result is false?

which means its not blank?

which is a bit of a mystery to me?

 

[heathball]

great - that 2nd code works fantastic!

Such a short code but such a time saver!

Its like magic!

i will attempt to use this within other macros

thanks for your time.

and thanks for the tip on the non-blank cells, i ran a 'delete leading and trailing spaces' and now they are blank. i might include that in my macro.

thanks for your great help!

 

[heathball]

with a code like this

is it possible to have a reference to columns

like if my 'range-array- column is 'M'

and the two calculation columns are "Y" and "Z"

and i would like the result to run down column "AA"

Is there a way to do that? - where i can change the columns to suit the need?

thanks

 

[Fluff]

Not really, as the size of the array will vary each time, as will the columns in the array that you need to reference.
This will look at cols M, Y:Z & output in AA

Sub GetDifference()
   Dim ary As Variant
   Dim i As Long
   
   ary = Range("M1", Range("M" & Rows.count).End(xlUp).Offset(, 14))
   With CreateObject("scripting.dictionary")
      For i = 1 To UBound(ary)
         .Item(ary(i, 1)) = .Item(ary(i, 1)) + val(ary(i, 13)) - val(ary(i, 14))
      Next i
      For i = 1 To UBound(ary)
         ary(i, 15) = .Item(ary(i, 1))
      Next i
      Range("AA1").Resize(UBound(ary)).Value = Application.Index(ary, , 15)
   End With
End Sub

 

[heathball]

that works perfectly for 40,000+ rows

thanks very much

I'm trying to work out the details....

I know it makes sense, and i will get to understand it eventually....

From what you say, i may have to ensure that these columns are always used for the calculation.


Cheers

 

[Fluff]

You can make it easier to change by using this

Sub GetDifference()
   Dim i As Long
   
   With CreateObject("scripting.dictionary")
      For i = 2 To Range("C" & Rows.count).End(xlUp).Row
         .Item(Range("C" & i).Value) = .Item(Range("C" & i).Value) + val(Range("D" & i).Value) - val(Range("F" & i).Value)
      Next i
      For i = 2 To Range("C" & Rows.count).End(xlUp).Row
         Range("H" & i).Value = .Item(Range("C" & i).Value)
      Next i
   End With
End Sub

BUT on 40,000+ rows it will take longer