Excel table greying out and adjust slicer size

[keinz]

Hi everyone,

Looking to get some assistance on this problem I cannot seem to solve...

I cleaned up a data dump into a Table so that I can apply a slicer to filter the data.

I am not using the Pivot table - as it cannot be adjusted to how I want the data to be viewed.



Anyways... for this table I have 2 slicers that I want to filter two types of data

1. Date
2. Department

The sheet has around 3000 rows and the slicer is roughly about 20 row tall and 3 rows wide.
Whenever I filter the data with the slicer and there are less rows than 20, it will resize the slicer.

In the event there are only 1 row of data - the slicer will then become unusable unless I manually resize it.
Whats weird is when the filters are applied, the bottom of the sheet is all greyed out - which is what I think is causing the slicer to resize?

I did format the slicer:
1. Disable resizing and moving (checked)
2. Don't move or size with cells (checked)
3. Locked (checked)


Yet it still happens...
I also tried protecting the worksheet, no change.

So my main question would be... how do I prevent the slicer from resizing?


Thank you in advance

 

[keinz]

Do you know if you have any event macros running ?
If you are not sure can you tell me if the file an xlsx file ? if not what is the file extension

it is a XLSM

there are macros enabled but for other sheets - not on the sheet im having issues with

 

[Alex Blakenburg]

With the sheet you are having issues as the activesheet. Go into the VBA editor.
In the Immediate Window (Ctrl+G if it is not visible).
Drop the below code into that window and hit enter.

activesheet.rows.hidden = false

 

[keinz]

With the sheet you are having issues as the activesheet. Go into the VBA editor.
In the Immediate Window (Ctrl+G if it is not visible).
Drop the below code into that window and hit enter.

activesheet.rows.hidden = false

didn't work,

it worked initially until I used my slicers and then the bottom is greyed out again

 

[Alex Blakenburg]

Right click on the sheet name and select view code.
If there is something there post it here.

 

[keinz]

Right click on the sheet name and select view code.
If there is something there post it here.

it is blank under "general"

under worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

 

[Alex Blakenburg]

Odd. Delete what is there, it is not doing anything except slowing down your spreadsheet.
What about if you double click on ThisWorkbook on the left in VBA ? Is there anything in that code window ?

If that doesn't show anything is there any way can share that sheet in your workbook via dropbox, google drive or some other sharing platform ? (ie make it available to anyone with the link and post the link here)

 

[keinz]

Odd. Delete what is there, it is not doing anything except slowing down your spreadsheet.
What about if you double click on ThisWorkbook on the left in VBA ? Is there anything in that code window ?

If that doesn't show anything is there any way can share that sheet in your workbook via dropbox, google drive or some other sharing platform ? (ie make it available to anyone with the link and post the link here)

deleted the portion

under "Thisworkbook" - same thing, nothing under general but under workbook - its here:
Private Sub Workbook_Open()

End Sub

 

[Alex Blakenburg]

I am pretty sure what you saw under Worksheet and now under ThisWorkbook is being created when you use the drop down box switching it from general to worksheet or in this case thisworkbook.
Just delete that again.

I take it that on the problem sheet ctrl+down arrow is even after the unhide, now back to only showing 3668 rows.

Do you think you can share you workbook with us so we can try to figure out what is going on ?

I have to go out soon but if you do manage to share the workbook I will have a look later.
PS: What time zone are you in ? (I am in Australia)

One other thing to check is if you have any add-ins running.

 

[keinz]

I am pretty sure what you saw under Worksheet and now under ThisWorkbook is being created when you use the drop down box switching it from general to worksheet or in this case thisworkbook.
Just delete that again.

I take it that on the problem sheet ctrl+down arrow is even after the unhide is back to only showing 3668 rows.

Do you think you can share you workbook with us so we can try to figure out what is going on ?

I have to go out soon but if you do manage to share the workbook I will have a look later.
PS: What time zone are you in ? (I am in Australia)

time zone is +8 PST,

unfortunately - cannot share the worksheet, i cant even move it out to my own local pc

 

[keinz]

fixed the issue somehow...
went into table tools -> resize table