Run Regression on Filtered Data Set

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.

Code:

---

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 think you may have to copy the filtered cells to their own contiguous ranges. Advanced filter will do this for you easily.
The following worked here:
Clear the Regression Output sheet completely (because I use .currentregion later) before running the macro.
Run this code:

Code:

---

LR1 = Sheets("Regression Data").Range("C" & Rows.Count).End(xlUp).Row ' determine extent of source data
'(note that I only use LR1 becuse if your LR2 is different from LR1 I suspect there will be a problem with the regression tool)
'Create a new table of filtered data; note that cell D3 of the Input sheet will need to be the same as the header in cell A1 of the Regression Data sheet.
Sheets("Regression Data").Range("A1:O" & LR1).AdvancedFilter Action:=xlFilterCopy, _
  CriteriaRange:=Sheets("Input").Range("D3:D4"), _
  CopyToRange:=Sheets("Regression Output").Range("A1"), Unique:=False
 
Set NewTableRange = Sheets("Regression Output").Range("A1").CurrentRegion 'find the extent of the new filtered data:
Set YValsRange = Intersect(NewTableRange, Sheets("Regression Output").Range("O:O")) 'find y values range
Set YValsRange = Intersect(YValsRange, YValsRange.Offset(1)) 'exclude the headers
Set XValsRange = Intersect(NewTableRange, Sheets("Regression Output").Range("C:M")) 'find x values range
Set XValsRange = Intersect(XValsRange, XValsRange.Offset(1)) 'exclude the headers
'Use the regression tool:
Application.Run "ATPVBAEN.XLAM!Regress", YValsRange, XValsRange, False, False, , Sheets("Regression Output").Range("$R$1"), False, False, False, False, , False

---

The results go to cell R3 of the Regression Output sheet.
Please check out the comments in the code too.
Of course, each time you could create a temporary new sheet to accommodate the new filtered table, put the regression data where you like on the Regression Output sheet, then delete that new sheet.

Hi. Thank you so much for responding to my post. I pasted your code in and like you said I have changed my filters in the input sheet to mimic the regression data format. The header is in D3 and the criteria is in D4. I then run your code. It worked the first time but following that, everytime I try to run it again it says "Regression - LINEST() function returns error. Please check input ranges again.". Here is the code I am using now.

Code:

---

Sub FilterRegress()

LR1 = Sheets("Regression Data").Range("C" & Rows.Count).End(xlUp).Row ' determine extent of source data


'Create a new table of filtered data; note that cell D3 of the Input sheet will need to be the same as the header in cell A1 of the Regression Data sheet.
Sheets("Regression Data").Range("A1:O" & LR1).AdvancedFilter Action:=xlFilterCopy, _
  CriteriaRange:=Sheets("Input").Range("D3:D4"), _
  CopyToRange:=Sheets("Regression Output").Range("A1"), Unique:=False
 
Set NewTableRange = Sheets("Regression Output").Range("A1").CurrentRegion 'find the extent of the new filtered data:
Set YValsRange = Intersect(NewTableRange, Sheets("Regression Output").Range("O:O")) 'find y values range
Set YValsRange = Intersect(YValsRange, YValsRange.Offset(1)) 'exclude the headers
Set XValsRange = Intersect(NewTableRange, Sheets("Regression Output").Range("C:M")) 'find x values range
Set XValsRange = Intersect(XValsRange, XValsRange.Offset(1)) 'exclude the headers
'Use the regression tool:
Application.Run "ATPVBAEN.XLAM!Regress", YValsRange, XValsRange, False, False, , Sheets("Regression Output").Range("$R$1"), False, False, False, False, , False


End Sub


Sub ClearContentsFormatting()


Sheets("Regression Output").Range("A:Y").ClearContents
Sheets("Regression Output").Range("A:Y").ClearFormats


End Sub

---

Hey actually I realised the issue was because of blank cells in my data set. This caused an error. I removed them and changed to code a little and it all works like a charm. Thank you so much.