SarahMS1

New Member
Joined
Jan 29, 2020
Messages
20
Office Version
  1. 2016
Platform
  1. 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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

LazyBug

Board Regular
Joined
Feb 28, 2020
Messages
159
Office Version
  1. 2010
Platform
  1. 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
  1. 365
  2. 2019
Platform
  1. 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
58,493
Office Version
  1. 365
Platform
  1. Windows
@ATonchev
Please start a thread of your own, rather than "hijacking" somebody else's. Thank you
 

Watch MrExcel Video

Forum statistics

Threads
1,133,453
Messages
5,658,860
Members
418,473
Latest member
shaztastic73

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top