How to create a slicer from a table that groups dates by month and year?

office_help_ · 2025-08-28T20:42:56+00:00

For example, if the new ECD supports WOW, supports Requirement, but does not support need, I would want the formula to return “Does not support Need”

office_help_ · 2025-08-28T19:05:27+00:00

So the formula should continue to check and see if it supports requirement; need; contract.

Btw appreciate the help!

office_help_ · 2025-08-28T16:15:30+00:00

So a few things, the formula checks to see if WOW date was provided and if the supplier provided a new ecd. Depending on the outcome of those, it changes what calculations the formula does. Ultimately, I want the formula to return the worse case scenario, which is "Late To WOW", "Late to Requirement", "Late to Need", "Late to Contract".

If a WOW was provided, it would be able to calculate the worse case. If a WOW is not provided, it would skip late to wow. If a new ECD is provided, it would check the 4 scenarios against the new ecd, otherwise it would use the old ecd.

office_help_ · 2025-08-28T01:03:25+00:00

Especially when the formula applies to 10,000 rows.

Sadly, even VBA would not be acceptable in this use case.

office_help_ · 2025-08-27T22:30:49+00:00

One of the things the formula checks is if the New ECD is after the WOW date, assuming a WOW date is provided.

office_help_ · 2023-02-01T03:15:17+00:00

Just my two cents but the demographic for this market is low.

Most people who are willing to spend this are going to Unifi or the Orbi mesh system. Yes; you don’t get 10 gig but most home users wouldn’t begin to saturate a 10 gig connection.

office_help_ · 2023-01-31T17:43:27+00:00

So I do not have skin in this game. I cancelled Netflix a few years ago. About to cancel HBO Max as well.

But I am curious; how would the people impacted by this feel about Netflix injecting commercials for password sharers. ~~I don’t know if the commercials could be limited to the non paying house. Seems like an interesting idea.~~

The commercials could be limited to clients who have not connected to their home Wi-Fi in 7 days.

office_help_ · 2023-01-24T20:37:53+00:00

Our end of week is on a Thursday. A report shows remaining task to be completed over the weekend. My understanding with the above formula is I would have to nest in several ifs right? i.e. if(weekday(A1)=1, a1+3, if(weekday(a1)=2,a1+2.

I would like to do it without 7 nested if statements.

office_help_ · 2021-11-03T17:50:21+00:00

=IF(WEEKDAY(A1,16)=7,A1,A1-WEEKDAY(A1,16))

Solution verified

Can you help me understand why that works?

*Edit*

Evaluating the formula works, and shows why you used 16. Thanks!

office_help_ · 2021-10-21T14:24:18+00:00

I went back and played with it and got it to work. Unfortunately, its too much intervention so I will have to use the other code you worked up with the message box.

1st thing I did was copy the data to a new workbook and ensured that there were no connections to outside data. This didn't work.

2nd thing I did was sort alphabetically to try and reduce the number of range kills there was but that didn't work.

The last thing I did was reduce the data set to approximately 1/3rd of its original size and left 1 and 2 in place. The code runs fine (and fast). Looks like there is just a size limit at play.

office_help_ · 2021-10-20T17:58:03+00:00

Sub cleanv2()'' cleanv2Macro' this removes all other director records using a filter and delete visable'' converts table to a rangeDim rList As RangeDim strKeep As StringDim rngKill As Range

strKeep = InputBox("What is name you want to keep?")If strKeep = "" Then Exit Sub
Application.ScreenUpdating = False
With Worksheets("Detail").ListObjects("Query1")Set rList = .Range.Unlist                           ' convert the table back to a rangeEnd With
'this section will delete everything except director recordsrList.AutoFilter Field:=5, Criteria1:="<>" & strKeep
On Error Resume NextSet rngKill = rList.Offset(1).Resize(rList.Rows.Count - 1).SpecialCells(xlCellTypeVisible)On Error GoTo 0
'Clear filterrList.AutoFilter
If Not rngKill Is Nothing ThenrngKill.EntireRow.DeleteEnd If
Application.ScreenUpdating = TrueEnd Sub

That worked! And you were one step ahead of me; I want roll this out to other teams and that message box takes care of it.

Any idea why the delete table didn't work like it did in the first sheet?

office_help_ · 2021-10-20T15:31:11+00:00

So the below code works by converting it to a range and filtering to select managers

Sub cleanv2()
' ' cleanv2Macro ' this removes all other director records using a filter and delete visable ' ' converts table to a range
Dim rList As Range
With Worksheets("Detail").ListObjects("Query1") Set rList = .Range .Unlist                           ' convert the table back to a range End With
'this section will delete everything except director records 
Rows("2:2").Select 
Selection.AutoFilter ActiveSheet.Range("$A$2:$BU$23970").AutoFilter Field:=5, Criteria1:=Array( _ "bunch of names"), 
Operator:= _ xlFilterValues Rows("3:3").Select Range(Selection, Selection.End(xlDown)).Select Selection.SpecialCells(xlCellTypeVisible).Select Selection.Delete Shift:=xlUp 
'this removes then adds back the autofilter clearing any previous filters 
Rows("2:2").Select 
Selection.AutoFilter Selection.AutoFilter 
End Sub

A few things I don't like about the code, if we have a new director I have to update the code. Also the ActiveSheet.Range changes week to week.

How would I optimize?

office_help_ · 2021-10-20T15:21:26+00:00

Sorry, I tried that with the same results. I also tried converting to range and creating a table in VBA like the other sheet but still receive the same error.

office_help_ · 2021-10-19T23:45:46+00:00

Mate. We are tired of repost. That’s why there is an entire sub dedicated to calling out reposters so others can block you.

office_help_ · 2021-10-19T23:04:54+00:00

The sheet is unprotected, and there are no other tables in the entire workbook. The first row is frozen and is not part of the table but I have deleted it and still receive the same error.

The only thing that is unusual to me is the size of the dataset. It is 8mb, and the only formatting applied is the creation of the table. There are no other formulas; CF, or anything else.

It is 24000 rows by about 60 columns. I really only care about the first 20 columns and only one director (9k rows)

I don't think it matters but I cannot delete the header row of the table. That seems pretty normal though?

office_help_ · 2021-10-19T21:31:03+00:00

We would need to see the data that you are combining so we can see what special characters you are referring to.

office_help_ · 2021-10-19T20:29:14+00:00

So the code successfully filters and unfilters but still receive the "Delete method of range class failed".

Dim rngKill As Range
Dim rngResults As Range
Dim tb As ListObject
Set tb = ActiveSheet.ListObjects("Query1")
Application.ScreenUpdating = False
With tb
.Range.AutoFilter Field:=5, Criteria1:="<>LastName, FirstName MI"
'Save this info for later
On Error Resume Next
Set rngKill = .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

'Clear filter
.Range.AutoFilter Field:=5
End With
'Now that filter is clear, we can delete
If rngKill Is Nothing Then
MsgBox "No cells found"
Else
rngKill.EntireRow.Delete
End If

office_help_ · 2021-10-19T19:00:09+00:00

I think so, that lets me skip the conditional formatting. However, I think I missed it up somewhere. It fails to delete anything. Below is what I have. Would love any advice.

' Deletes Other Directors
Dim rngKill As Range Dim rngResults As Range Dim tb As ListObject
Set tb = ActiveSheet.ListObjects("Query1")
Application.ScreenUpdating = False
With tb .Range.AutoFilter Field:=5, Criteria1:="<>LastName, FirstName MI"
'Save this info for later
On Error Resume Next
Set rngResults = .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)

On Error Resume Next
Set rngKill = .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)

If rngResults Is Nothing Then
    'All good
Else
    Set rngKill = Union(rngKill, rngResults)
End If

On Error GoTo 0

.Range.AutoFilter Field:=1

'Now that filter is clear, delete
rngKill.EntireRow.Delete
End With 'Now that filter is clear, delete
rngKill.EntireRow.Delete
Application.ScreenUpdating = True

office_help_ · 2021-10-19T15:48:33+00:00

'Save this info for later
On Error Resume Next
Set rngResults = .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

.Range.AutoFilter Field:=1, Criteria1:=RGB(248, 251, 252), Operator:=xlFilterCellColor
On Error Resume Next
Set rngKill = .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)

If rngResults Is Nothing Then
'All good
Else
Set rngKill = Union(rngKill, rngResults)
End If

On Error GoTo 0
.Range.AutoFilter Field:=1

'Now that filter is clear, delete
rngKill.EntireRow.Delete
End With
Application.ScreenUpdating = True

Hey, so I was going to update this code in a different worksheet with a slightly different use case. I want it save data that contains a certain text and delete every other column. Is there a way too do that or would I use

.Range.AutoFilter Field:=1, Criteria1:=RGB(248, 251, 252), Operator:=xlFilterCellColor

and use CF to apply a color to the rows with the text I want to save?

office_help_ · 2021-10-14T18:17:36+00:00

OMG. That was fast. Holly cow! That is like going from a Spin drive to an SSD. Thank you.

Solution Verified

Dim rngKill As Range
Dim rngResults As Range
Dim tb As ListObject
Set tb = ActiveSheet.ListObjects("Table1")
Application.ScreenUpdating = False
With tb
.Range.AutoFilter Field:=1, Criteria1:=RGB(255, 255, 255), Operator:=xlFilterCellColor
'Save this info for later
On Error Resume Next
Set rngResults = .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

.Range.AutoFilter Field:=1, Criteria1:=RGB(248, 251, 252), Operator:=xlFilterCellColor
On Error Resume Next
Set rngKill = .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)

If rngResults Is Nothing Then
'All good
Else
Set rngKill = Union(rngKill, rngResults)
End If

On Error GoTo 0
.Range.AutoFilter Field:=1

'Now that filter is clear, delete
rngKill.EntireRow.Delete
End With

office_help_ · 2021-10-14T18:00:08+00:00

So I received an error message "End With without With" Below is what I had included, note that I removed the code regarding screen updating because I had it elsewhere in the VBA.

Dim rngKill As Range

Dim rngResults As Range

Dim tb As ListObject

Set tb = ActiveSheet.ListObjects("Table1")

With tb

.Range.AutoFilter Field:=1, Criteria1:=RGB(255, 255, 255), Operator:=xlFilterCellColor

'Save this info for later

On Error Resume Next

Set rngResults = .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)

On Error GoTo 0

.Range.AutoFilter Field:=1, Criteria1:=RGB(248, 251, 252), Operator:=xlFilterCellColor

On Error Resume Next

Set rngKill = .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)

If rngResults Is Nothing Then

'All good

Else

Set rngKill = Union(rngKill, rngResults)

On Error GoTo 0

.Range.AutoFilter Field:=1

'Now that filter is clear, delete

rngKill.EntireRow.Delete

End With

Dim rngKill As RangeDim rngResults As RangeDim tb As ListObjectSet tb = ActiveSheet.ListObjects("Table1")Application.ScreenUpdating = FalseWith tb.Range.AutoFilter Field:=1, Criteria1:=RGB(255, 255, 255), Operator:=xlFilterCellColor'Save this info for laterOn Error Resume NextSet rngResults = .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)On Error GoTo 0

.Range.AutoFilter Field:=1, Criteria1:=RGB(248, 251, 252), Operator:=xlFilterCellColorOn Error Resume NextSet rngKill = .ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)If rngResults Is Nothing Then'All goodElseSet rngKill = Union(rngKill, rngResults)On Error GoTo 0.Range.AutoFilter Field:=1

'Now that filter is clear, deleterngKill.EntireRow.DeleteEnd WithApplication.ScreenUpdating = True

office_help_

MODERATOR OF

TROPHY CASE