CODEWITHSUNDEEP
vbaexcel
Katie Sharp
Katie Sharp

Reputation: 3

Hide Rows based on Date in Column

I've searched and searched the internet and all of the forums and I've been piecing together code and still can't figure this out. I've tried For loops and For Each loops and still can't get it right. In my sheet, I have all of my dates in Column D. I want to hide rows by month. I want to be able to click a macro button and only show dates in January, or February, or etc.

This is what I currently have:

Sub January()
'
'
'
Dim cell As Range
For Each cell In Range("Date")
    If cell.Value = "" Then
        cell.EntireRow.Hidden = False
    End If
    If cell.Value < "1/1/2018" Or cell.Value > "1/31/2018" Then
        cell.EntireRow.Hidden = True
    End If
Next cell
End Sub

When I run this, it just hides anything that isn't an empty cell. I've cycled between defining cell as a Range and as a Variant and it's the same either way.

ETA:

It is working now and it took help from everybody. I really appreciate it! Here's what I ended with..

Sub January()
'
'
'
Dim cell As Range
For Each cell In Range("Date")
    If cell.Value = "" Then
        cell.EntireRow.Hidden = False
    ElseIf cell.Value < CDate("1/1") Or cell.Value > CDate("1/31") Then
        cell.EntireRow.Hidden = True
    End If
Next cell
End Sub

I removed the years from the code so that I don't have to change any coding for future years.

Upvotes: 0

Views: 2475

Answers (5)

TotsieMae
TotsieMae

Reputation: 835

Ultimately, I believe this is the code you're looking for:

Sub January()
Dim cell As Range

Application.ScreenUpdating = False

For Each cell In Range("Date")
 'If date falls on or after January 1, AND on or before January 31, don't hide the row
  If cell.Value >= CDate("1/1/2018") And cell.Value <= CDate("1/31/2018") Then
    cell.EntireRow.Hidden = False
  Else
   'If the cell doesn't contain anything or isn't in January, hide the row
    cell.EntireRow.Hidden = True
  End If
Next cell

Application.ScreenUpdating = True
End Sub

You need to use And logic, not Or logic. Or logic always returns TRUE unless both expressions are false or there is a null involved. Because of this, the code stopped looking at your logical statement once it evaluated to true since every date you had - I'm assuming - fell after January 1, 2018. This in turn caused the rows to hide unexpectedly.

Additionally, I would convert the strings you have into dates using CDate. It helps Excel understand what is going on a bit better and makes your code easier to understand to outsiders. Another good practice to work on is adding comments to code. I think we've all learned the hard way by leaving comments out of code at some point or another.

One last thing: if you're planning to have buttons for each month, consider doing one procedure for all of them and having variables populate the date ranges, potentially using input boxes to get the values from the user. It'll save you a lot of headaches if you ever decide to change things up in the future.

Upvotes: 1

L42
L42

Reputation: 19727

I will actually go with Slicers and Table.
But if you call VBA your neat solution then I'd say abandon the loop.
Have nothing against it but if Excel already have the functionality, then use it.
It is like a discount or a promotion that we need to take advantage of.
So instead of loop, why not just filter?

Dim lr As Long, r As Range
With Sheet1 '/* sheet where data reside */
    .AutoFilterMode = False '/* reset any filtering already applied */
    lr = .Range("D" & .Rows.Count).End(xlUp).Row '/* get the target cells */
    Set r = .Range("D1:D" & lr) '/* explicitly set target object */
    '/* filter without showing the dropdown, see the last argument set to false */
    r.AutoFilter 1, ">=2/1/2018", xlAnd, "<=2/28/2018", False 
End With

Above is for February of this year, you can tweak it to be dynamic.
You can create separate sub procedure for each month of you can just have a generic one.

Upvotes: 0

Brent
Brent

Reputation: 98

Your current setup would qualify all dates as either < or > the respective date comparison.

If you are trying to hide rows for January in this code, then you need to use AND instead of OR

And be sure you use >= & <= to include those first and last dates.

If cell >= "1/1/2018" AND cell <= "1/31/2018" Then

If you are trying to hide rows not January then your < and > are transposed:

If cell < "1/1/2018" OR cell > "1/31/2018" Then

Upvotes: 1

jeffreyweir
jeffreyweir

Reputation: 4824

Alternative approach: If you've got Excel 2013 or later, simply add a Table Slicer and filter on a MONTH column generated with =DATE(YEAR([@Date]),MONTH([@Date]),1) as shown below:

enter image description here

Or otherwise use a PivotTable and a Slicer: enter image description here

To see how easy it is to set up a PivotTable, see VBA to copy data if multiple criteria are met

Upvotes: 1

chillin
chillin

Reputation: 4486

Untested, written on mobile. I am just providing an alternative approach which tries to use MONTH and YEAR. Some may find this approach easier to understand.

Option Explicit

Sub January()

Dim cell As Range

For Each cell In Range("Date")
    If cell.Value = "" Then
        cell.EntireRow.Hidden = False
    Else
    cell.EntireRow.Hidden = (Month(cell.Value) = 1) and (year(cell.Value) = 2018)
End if

Next cell

End sub

Upvotes: 0

Related Questions