VBA and selecting non-zero values..

[segran]

Hi,

I am performing regression analysis.

I created a button, and am running the following macro -


Sub Macro5()
'
' Macro5 Macro
'

'
ActiveSheet.Range("$o$16:$w$34").Clear
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("$h$15:$h$32"), _
ActiveSheet.Range("$i$15:$l$32"), False, True, , ActiveSheet.Range("$o$16") _
, False, False, False, False, , False
End Sub


Currently, I have defined Y-range as $h$15:$h$32, however, I want to automatically choose y- and x- range.

Currently $h$15:$h$32 is based on historical data. However, this range then has zero data, when another set of y-variable is chose (based on forecasted data). I would like to automatically choose all-non zero data in column h, and automatically chose the corresponding data in range i:l. For example, if the data range with non-zeros start in range ("$h$33:$h$40), then x-range will be ("$i$33:$l$40").

Thanking you in advance for your help.

Demand forecast Model Base Cur exp - Feb2010 ver18aa.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
16		D		1991	4008.1			3.603	0.00	2.11	0.00	0.00
17		E		1992	2511.4			3.400	0.00	2.13	0.00	0.00
18		F		1993	2486.9			3.396	0.00	2.14	0.00	0.00
19		G		1994	2841.9			3.454	0.00	2.18	0.00	0.00
20		H		1995	2800.5			3.447	0.00	2.21	0.00	0.00
21		I		1996	3184.7			3.503	0.00	2.23	0.00	0.00
22		J		1997	2726.1			3.436	0.00	2.26	0.00	0.00
23		K		1998	2243.8			3.351	0.00	2.28	0.00	0.00
24		L		1999	2113.4			3.325	0.00	2.29	0.00	0.00
25		M		2000	1944.5			3.289	0.00	2.31	0.00	0.00
26		N		2001	1977.0			3.296	0.00	2.33	0.00	0.00
27		O		2002	2349.2			3.371	0.00	2.33	0.00	0.00
28		P		2003	1895.2			3.278	0.00	2.49	0.00	0.00
29		Q		2004	1447.7			3.161	0.00	2.51	0.00	0.00
30		R		2005	1293.8			3.112	0.00	2.52	0.00	0.00
31		S		2006	1061.6			3.026	0.00	2.54	0.00	0.00
32		T		2007	1042.9			3.018	0.00	2.55	0.00	0.00
33		U		2008	1212.9	1212.9	16.3%	0.000	0.00	2.56	0.00	0.00
34		V		2009	1180.1	1180.1	-2.7%	0.000	0.00	2.57	0.00	0.00
35		W		2010	1155.8	1155.8	-2.1%	0.000	0.00	2.58	0.00	0.00
36		X		2011	1126.4	1126.4	-2.5%	0.000	0.00	2.59	0.00	0.00
37		Y		2012	1092.5	1092.5	-3.0%	0.000	0.00	2.61	0.00	0.00
38		Z		2013	1059.2	1059.2	-3.1%	0.000	0.00	2.62	0.00	0.00
39		AA		2014	1026.4	1026.4	-3.1%	0.000	0.00	2.64	0.00	0.00
40		AB		2015	994.1	994.1	-3.1%	0.000	0.00	2.65	0.00	0.00
41		AC		2016	962.3	962.3	-3.2%	0.000	0.00	2.66	0.00	0.00
42		AD		2017	930.9	930.9	-3.3%	0.000	0.00	2.68	0.00	0.00
43		AE		2018	900.1	900.1	-3.3%	0.000	0.00	2.69	0.00	0.00
44		AF		2019	882.4	882.4	-2.0%	0.000	0.00	2.70	0.00	0.00
45		AG		2020	866.4	866.4	-1.8%	0.000	0.00	2.71	0.00	0.00
Regression Analysis

 

[Andrew Poulsom]

This should give you the first and last rows:

Sub Test()
    Dim r As Integer
    Dim First As Long
    Dim Last As Long
    r = 16
    With ActiveSheet
        Do While .Range("H" & r) = 0
            r = r + 1
        Loop
        First = r
        Do Until .Range("H" & r) = 0
            r = r + 1
        Loop
        Last = r - 1
    End With
    MsgBox First & " " & Last
End Sub

You can use the variables in your code like this:

ActiveSheet.Range("H" & First & ":H" & Last)

You may need to change the starting value of r.

 

[segran]

Hi,

How do i enter this code into the macro?
Also, how does it chose the x-range?

Thank you

 

[Andrew Poulsom]

Just paste it at the beginning of your code (excluding the first and last lines). Then use the variables First and Last in your code like I showed you.

 

[segran]

Hi,

This is not working. Please help.


Sub Macro5()
'
' Macro5 Macro
'

'

Dim r As Integer
Dim First As Long
Dim Last As Long
r = 16
With ActiveSheet
Do While .Range("H" & r) = 0
r = r + 1
Loop
First = r
Do Until .Range("H" & r) = 0
r = r + 1
Loop
Last = r - 1
End With
MsgBox First & " " & Last


ActiveSheet.Range("$o$16:$w$34").Clear
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("H" & First & ":H" & Last), _
ActiveSheet.Range("H" & First & ":H" & Last), False, True, , ActiveSheet.Range("$o$16") _
, False, False, False, False, , False
End Sub

 

[segran]

Hi,

How do i include the lables for y and x -range columns?

Thank you

 

[Andrew Poulsom]

Your second:

ActiveSheet.Range("H" & First & ":H" & Last)

should be:

ActiveSheet.Range("I" & First & ":L" & Last)

 

[segran]

Hi,

I am sorry, but i am not getting the correct results.
For example, if the y-ranges starts at h22 : h27, the first should be 22 and last should be 27, but this is not working.

Please help.

Demand forecast Model Base Cur exp - Feb2010 ver18aaa.xlsm
	D	E	F	G	H	I	J	K	L
16	1991	0.0				1.50	2.11	0.00	0.00
17	1992	0.0				1.81	2.13	0.00	0.00
18	1993	0.0				1.92	2.14	0.00	0.00
19	1994	0.0				2.05	2.18	0.00	0.00
20	1995	0.0				2.07	2.21	0.00	0.00
21	1996	0.0				1.96	2.23	0.00	0.00
22	1997	203620.4			5.309	1.92	2.26	0.00	0.00
23	1998	146329.1			5.165	1.89	2.28	0.00	0.00
24	1999	164130.4			5.215	1.90	2.29	0.00	0.00
25	2000	341595.9			5.534	1.98	2.31	0.00	0.00
26	2001	360726.1			5.557	2.19	2.33	0.00	0.00
27	2002	309259.3			5.490	2.25	2.33	0.00	0.00
28	2003	0.0				2.23	2.49	0.00	0.00
29	2004	0.0				2.24	2.51	0.00	0.00
30	2005	0.0				2.29	2.52	0.00	0.00
31	2006	0.0				2.40	2.54	0.00	0.00
32	2007	0.0				2.53	2.55	0.00	0.00
Regression Analysis

 

[Andrew Poulsom]

When I ran the macro I posted with that data I got 22 and 27? In what way doesn't it work for you?

 

[segran]

Hi,

I get 15 and 32.

Here what I have :

Sub Macro5()
'
' Macro5 Macro
'

'

Dim r As Integer
Dim First As Long
Dim Last As Long
r = 15
With ActiveSheet
Do While .Range("H" & r) = 0
r = r + 1
Loop
First = r
Do Until .Range("H" & r) = 0
r = r + 1
Loop
Last = r - 1
End With
MsgBox First & " " & Last


ActiveSheet.Range("$o$16:$w$34").Clear
Application.Run "ATPVBAEN.XLAM!Regress", ActiveSheet.Range("H" & First & ":H" & Last), _
ActiveSheet.Range("i" & First & ":l" & Last), False, True, , ActiveSheet.Range("$o$16") _
, False, False, False, False, , False
End Sub