Run Regression on Filtered Data Set

[lakshman]

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

[p45cal]

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:

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.

[lakshman]

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.

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

[lakshman]

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.