FILTER formula that will hide blank rows.

[lite4d]

I have a spreadsheet that I want to filter data based on dates and the data will range from 3 rows to 120 rows based on the dates that are chosen. What I need to do is add something in the formula to shrink the rows to just the ones with data and expand with the data as required. My current formula is below:

=FILTER('Random Receipts'!A2:Q120,('Random Receipts'!K2:K120>=C3)*('Random Receipts'!K2:K120<=D3))

Right now I have to have 120 rows to cover for the data that "could" be there. I want it to shrink and grow as required. What do I need to add to my formula to get that to work?

I have tried a few things but nothing is working correctly. I just get the $spill error.

Thanks for the help as always!!

 

[lite4d]

Are you saying that the spill range shows exactly what you want, rather than what you want plus empty rows inside the spill?

The spill shows what I want but I don't want the empty space in between. I didn't think that it could be done because I've tried everything I know but I know that there are some new things in 365 that I have had to use yet so I figured that I would ask if there was a way to do it.

 

[felixstraube]

For future questions... Some images/XL2BB of what you have and what you want would be useful and save us a lot of time

 

[lite4d]

For future questions... Some images/XL2BB of what you have and what you want would be useful and save us a lot of time

Yeah I know...I have to do this from my work computer and I can't put XL2BB on it.(GOV) and a lot of the pics were too big and my wording about what I wanted could have been a lot better! Thanks for the help!

 

[felixstraube]

You could use something like this:

Sub HideRows()
'
' Hide Rows Macro
'

'
    Cells.Select
    Selection.EntireRow.Hidden = False
    Range("B14").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Range("B134")).Select
    Selection.EntireRow.Hidden = True

End Sub

An add a button to your sheet to start the macro or you could trigger it with the change of the content of a cell.
If you want to trigger the macro with the cell change here is some instructions on how to achieve this:

Run a Macro When a Specific Cell Changes in Excel - TeachExcel.com.

 

[lite4d]

You could use something like this:

Sub HideRows()
'
' Hide Rows Macro
'

'
    Cells.Select
    Selection.EntireRow.Hidden = False
    Range("B14").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Range("B134")).Select
    Selection.EntireRow.Hidden = True

End Sub

An add a button to your sheet to start the macro or you could trigger it with the change of the content of a cell.
If you want to trigger the macro with the cell change here is some instructions on how to achieve this:

Run a Macro When a Specific Cell Changes in Excel - TeachExcel.com.

Thanks I'll try that. Anything to hide and unhide the empty spaces. I want to print the sheet with just the data and the empty spaces take up alot of room on the page.

Thanks again for all the help!