lakshman

04-03-2019, 07:59 AM

Hi everyone.

I am trying to filter a data set under the sheet "Regression Data" based on a filter criteria - Sheets("Input").Range("$D$4"). Then I want to run a regression based on this filtered data (only visible cells). My Y values are in column O and my X values are in columns C to M.

I feel that I have written the correct code but everytime I run the code I get a msgbox that says "Regression - X Range and Y Range cannot overlap.". I have checked several times the ranges I have specified don't seem to overlap as you can see below. I think there might be a simple solution to this that I am not seeing. I would really appreciate it if you guys can help me out on this. I can send in images of the file if you would like. The excel file is too big (100+ MB) though.

Thank you so much.

Sub Test1()'

' Test1 Macro

'

'

Sheets("Regression Data").Range("$A$1:$M$465779").AutoFilter Field:=1, Criteria1:= _

Sheets("Input").Range("$D$4")

'

Dim LR1 As Long

LR1 = Sheets("Regression Data").Range("C" & Rows.Count).End(xlUp).Row

Dim LR2 As Long

LR2 = Sheets("Regression Data").Range("O" & Rows.Count).End(xlUp).Row

'

Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Regression Data").Range("O2:O" & LR2).SpecialCells(xlCellTypeVisible) _

, Sheets("Regression Data").Range("C2:M" & LR1).SpecialCells(xlCellTypeVisible), False, False, , Sheets("Regression Output").Range( _

"$A$1:$G$31"), False, False, False, False, , False

End Sub

Sub ResetFilters()

'To clear the filter from a Single Column, specify the

'Field number only and no other parameters

Sheets("Regression Data").Range("$A$1:$M$465779").AutoFilter Field:=1

End Sub

I am trying to filter a data set under the sheet "Regression Data" based on a filter criteria - Sheets("Input").Range("$D$4"). Then I want to run a regression based on this filtered data (only visible cells). My Y values are in column O and my X values are in columns C to M.

I feel that I have written the correct code but everytime I run the code I get a msgbox that says "Regression - X Range and Y Range cannot overlap.". I have checked several times the ranges I have specified don't seem to overlap as you can see below. I think there might be a simple solution to this that I am not seeing. I would really appreciate it if you guys can help me out on this. I can send in images of the file if you would like. The excel file is too big (100+ MB) though.

Thank you so much.

Sub Test1()'

' Test1 Macro

'

'

Sheets("Regression Data").Range("$A$1:$M$465779").AutoFilter Field:=1, Criteria1:= _

Sheets("Input").Range("$D$4")

'

Dim LR1 As Long

LR1 = Sheets("Regression Data").Range("C" & Rows.Count).End(xlUp).Row

Dim LR2 As Long

LR2 = Sheets("Regression Data").Range("O" & Rows.Count).End(xlUp).Row

'

Application.Run "ATPVBAEN.XLAM!Regress", Sheets("Regression Data").Range("O2:O" & LR2).SpecialCells(xlCellTypeVisible) _

, Sheets("Regression Data").Range("C2:M" & LR1).SpecialCells(xlCellTypeVisible), False, False, , Sheets("Regression Output").Range( _

"$A$1:$G$31"), False, False, False, False, , False

End Sub

Sub ResetFilters()

'To clear the filter from a Single Column, specify the

'Field number only and no other parameters

Sheets("Regression Data").Range("$A$1:$M$465779").AutoFilter Field:=1

End Sub