SarahMS1

New Member
Joined
Jan 29, 2020
Messages
20
Office Version
2016
Platform
Windows
hello everyone,

i need a button that hides and unhides rows based on cell value of the column (No. Date) if the date is more than 6 days ago it becomes hidden, and whats left is the recent ones which are this week. but i can hide and unhide it whenever i want that’s important

it’s important to note that i use table and my data increases everyday so the range gets bigger
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
93
Office Version
2010
Platform
Windows
We do not know anything about the conditions: neither the location of the data, nor the first and last values, nor whether there are other filters. Therefore, the option with only two buttons.
VBA Code:
Private Sub Hide_Click()
Dim dc As Range, c As Range, lr&
Application.ScreenUpdating = False

lr = [C1].CurrentRegion.Rows.Count + [C1].CurrentRegion.Row - 1 'replace here C1 with the first cell of column with dates
Set dc = Range("C1:C" & lr) 'replace here C1:C with your address of the first cell with date, then colon and name of the column with dates
    For Each c In dc
        If Date - c.Value > 6 Then c.EntireRow.Hidden = True
    Next
    
Application.ScreenUpdating = True
End Sub


Private Sub Show_Click()
Application.ScreenUpdating = False
[C1].CurrentRegion.Rows.Hidden = False  'replace here C1 with the first cell of column with dates
Application.ScreenUpdating = True
End Sub
 

ATonchev

New Member
Joined
Mar 26, 2020
Messages
6
Office Version
365, 2019
Platform
Windows
Hello,

I have similar problem. I have a table with two columns (min value and max value) and I want to filter the rows typing in the desired range.
I have achieved to make it for the first column. It is as follows:


Sub SearcRange()

'Hide
Dim Cell As Range
Var = Range("C1").Value
Application.ScreenUpdating = False
Range("AL9:AL500").End(xlDown).Select
ActiveCell.Offset(-1, 0).Select
For Each Cell In Range(ActiveCell, "AL9:AL500")
Cell.EntireRow.Hidden = Cell.Value >= Var
Next

Application.ScreenUpdating = True

End Sub

Actually, I need to add the result to be filtered again according column AM9:AM500 taking the valua from cell C2.

Can you help me please?

Cheers,
Anton
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
40,675
Office Version
365
Platform
Windows
@ATonchev
Please start a thread of your own, rather than "hijacking" somebody else's. Thank you
 

Watch MrExcel Video

Forum statistics

Threads
1,099,081
Messages
5,466,529
Members
406,484
Latest member
kaksolver

This Week's Hot Topics

Top