Hide Rows and Columns based on Date Input

[LROCCO]

Dates are listed in Column A, starting from Cell A3, and data appears
in the corresponding rows (across the page). Not every cell has data
in for the corresponding date.

For example: The date in cell A5 may have data in Cell B5, E5 & G5.

I would like to enter a date (using a command button and input box),
this will then hide all rows, except for the row which relates to the
date that has been entered. At the same time I would like it hide all
columns that do not have data in for the given date.

.... carrying on from example above.

If the user entered the date that appeared in cell A5, all other rows
with data in would be hidden. Also, rows with no date would be
hidden, i.e. C5, D5, F5

A button to show all columns/rows would also be of use.

Can anyone help with this?

My thanks in advance

Lui

 

[Dave]

Hi,
Will a specific date only ever appear once in Column A, or could there be
multiple entries of the same date?
Regards - Dave

 

[Don Guillett]

These should do it.

Sub FilterUnfilterToggle()
Columns.Hidden = False
Range("a1").AutoFilter
End Sub

Sub FilterDate()
mydate = Format(InputBox("Enter date"), Range("A2").NumberFormat)
Range("A1:a10").AutoFilter Field:=1, Criteria1:=mydate
lc = Cells(1, Columns.Count).End(xlToLeft).Column
mr = Columns(1).Find(What:=mydate, After:=Cells(2, 1), LookIn:=xlValues, _
lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
For i = 2 To lc
If Len(Trim(Cells(mr, i))) < 1 Then Columns(i).Hidden = True
Next i
End Sub

 

[LROCCO]

These should do it.

Sub FilterUnfilterToggle()
Columns.Hidden = False
Range("a1").AutoFilter
End Sub

Sub FilterDate()
mydate = Format(InputBox("Enter date"), Range("A2").NumberFormat)
Range("A1:a10").AutoFilter Field:=1, Criteria1:=mydate
lc = Cells(1, Columns.Count).End(xlToLeft).Column
mr = Columns(1).Find(What:=mydate, After:=Cells(2, 1), LookIn:=xlValues, _
        lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
For i = 2 To lc
If Len(Trim(Cells(mr, i))) < 1 Then Columns(i).Hidden = True
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software













- Show quoted text -

Thanks for the replies & the code

The dates in column A are unique.

I have tried the code and it seems to work fine until it hides the
columns. It seems to trip over on:
"mr = Columns(1).Find(What:=mydate, After:=Cells(2, 1),
LookIn:=xlValues, _"
I have changed the range to ("A4:A500") to adapt to my spreadsheet.
Not sure if I should change Range("A2") on second line of code?

 

[Don Guillett]

My code assumed a header row 1 and dates starting at row TWO. Adapt as
desired.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

These should do it.

Sub FilterUnfilterToggle()
Columns.Hidden = False
Range("a1").AutoFilter
End Sub

Sub FilterDate()
mydate = Format(InputBox("Enter date"), Range("A2").NumberFormat)
Range("A1:a10").AutoFilter Field:=1, Criteria1:=mydate
lc = Cells(1, Columns.Count).End(xlToLeft).Column
mr = Columns(1).Find(What:=mydate, After:=Cells(2, 1), LookIn:=xlValues, _
lookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
For i = 2 To lc
If Len(Trim(Cells(mr, i))) < 1 Then Columns(i).Hidden = True
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software













- Show quoted text -

Thanks for the replies & the code

The dates in column A are unique.

I have tried the code and it seems to work fine until it hides the
columns. It seems to trip over on:
"mr = Columns(1).Find(What:=mydate, After:=Cells(2, 1),
LookIn:=xlValues, _"
I have changed the range to ("A4:A500") to adapt to my spreadsheet.
Not sure if I should change Range("A2") on second line of code?

 

[LROCCO]

My code assumed a header row 1 and dates starting at row TWO. Adapt as
desired.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software







Thanks for the replies & the code

The dates in column A are unique.

I have tried the code and it seems to work fine until it hides the
columns.  It seems to trip over on:
"mr = Columns(1).Find(What:=mydate, After:=Cells(2, 1),
LookIn:=xlValues, _"
I have changed the range to ("A4:A500") to adapt to my spreadsheet.
Not sure if I should change Range("A2") on second line of code?- Hide quoted text -

- Show quoted text -

I've tried your code as per your assumptions and it works great.

Can you tell me what I would need to change if the header takes up the
first three rows (i.e. dates starts at cell A4)

Thanks

Lui

 

[Don Guillett]

Please TOP post for me.
cells(2,1) is the same as range("a2").

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

My code assumed a header row 1 and dates starting at row TWO. Adapt as
desired.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software







Thanks for the replies & the code

The dates in column A are unique.

I have tried the code and it seems to work fine until it hides the
columns. It seems to trip over on:
"mr = Columns(1).Find(What:=mydate, After:=Cells(2, 1),
LookIn:=xlValues, _"
I have changed the range to ("A4:A500") to adapt to my spreadsheet.
Not sure if I should change Range("A2") on second line of code?- Hide
quoted text -

- Show quoted text -

I've tried your code as per your assumptions and it works great.

Can you tell me what I would need to change if the header takes up the
first three rows (i.e. dates starts at cell A4)

Thanks

Lui