PDA

View Full Version : Solved: Hide data based on date



Djblois
08-01-2006, 06:47 AM
I have a spreadsheet for open bids with 2 columns a start date and an end date. If today's date does not fall inbetween the two dates I want to hide the row. Can someone just give me a hint on how to do this? Will Autofilter work? I need to be able to view all the rows again after?

Daniel

Djblois
08-01-2006, 07:52 AM
This is the code I have so far:

Dim today As Variant
today = Date
Cells.AutoFilter Field:=15, Criteria1:
Intersect(ActiveSheet.UsedRange, _
Columns(1).SpecialCells(xlCellTypeVisible)).EntireRow.Hidden = True


I tried to tell it if it was greater than today with this code:

Cells.AutoFilter Field:=15, Criteria1: > today

but all I get is an error

jungix
08-01-2006, 08:00 AM
I don't think you can use Autofilter with a boolean condition. You could just compare the dates and then hide your row.

If today > entryDate And today < closeDate Then
...

Djblois
08-01-2006, 08:22 AM
Here is my newest code

Sub HideClosed()
Dim today As Variant
Dim startDate, endDate As Range
Set today = Date
Set startDate = Cells(65536, 14).End(x1up).Offset(1, 0)
Set endDate = Cells(65536, 14).End(x1up).Offset(1, 0)
If today > startDate And today < endDate Then
EntireRow.Hidden = True

I know the startDate and endDate variables will only select the last cell How do I get it to select from the last cell up to the 2 cell in the column.

Also should that work?

Daniel

jungix
08-01-2006, 09:11 AM
I don't think you can compare Date and Ranges. Use instead


If today > startDate.Value And today < endDate.Value


Don't forget that it is xlUp and not x1up. Then it should work, but it won't hide any rows unless you change the dates values (start and end are the same)

Djblois
08-01-2006, 10:35 AM
I am truly trying. lol Here is the my newest code:
Dim today As Date
Dim startDate, endDate As Range
Set today = Date
Set startDate = Cells(65536, 14).End(xlUp).Offset(1, 0)
Set endDate = Cells(65536, 15).End(xlUp).Offset(1, 0)
If today > startDate.Value And today < endDate.Value Then
EntireRow.Hidden = True
End If

Please explain to me why it isn't workin also. So I can learn from my mistakes.

jungix
08-01-2006, 11:13 AM
What error do you get and when?

Djblois
08-01-2006, 11:17 AM
Set today = Date

I get object required at that line

jungix
08-01-2006, 12:02 PM
Just remove the set: today = Date

Djblois
08-01-2006, 12:24 PM
Now it gives me no error but it doesn't do anything:

Please explain what is wrong don't just give me the fixed code

Dim today As Date
Dim startDate, endDate As Range
Set startDate = Cells(65536, 14).End(xlUp).Offset(1, 0)
Set endDate = Cells(65536, 15).End(xlUp).Offset(1, 0)
If today > startDate.Value And today < endDate.Value Then
EntireRow.Hidden = True
End If

jungix
08-01-2006, 12:38 PM
Your code is really weird now that I look at it more closely. I'm not familiar with the "EntireRow.Hidden = True", maybe that works if you have selected a row, but what I usually use to hide is:


Rows("1:1").EntireRow.Hidden = True


For this you would need to specify the row you want to hide using the row of your ranges (entryDate.Row).

Also, you have now a code that works, but it's probably not doing what you expected. When you used the .End(xlup) you got the last not empty row. I don't know if this is what you wanted, but in anycase your offset puts you again in the empty zone. So entryDate.Value="" in your case. You should explain what you're trying to do exactly (which row you want to hide for instance) and post a worksheet with an example because everything seems a little confused.

Djblois
08-01-2006, 12:54 PM
I want to hide any rows that todays date don't fall between the start date and end date columns. What I was trying to do is create a range for the 2 columns and say if today doesn't fall between the dates in those columns than hide (don't delete) those rows.

Djblois
08-01-2006, 01:07 PM
Dim startDate, endDate As Range
Set startDate = ActiveSheet.Range([N2], [N2].End(xlDown))
Set endDate = ActiveSheet.Range([O2], [O2].End(xlDown))
If Date > startDate.Value And Date < endDate.Value Then
EntireRow.Hidden = True
End If

My newest code and I get a type mismatch at the if today line

jungix
08-01-2006, 01:07 PM
I still don't understant why you are using ranges. Just use a simple loop:


today = Date

For i=1 To nbRows
If today < Cells(i,14) Or today > Cells(i,15) Then
Row(i & ":" & i).EntireRow.Hidden = True
'add this if you may unhide rows from another day:
'Else
'Row(i & ":" & i).EntireRow.Hidden = False
End If
Next i

Charlize
08-01-2006, 03:21 PM
Enclosed you will find a worksheet with two macros and a function. The macros are for hiding the unwanted data and restoring all the data. Also included is a function to find the last row in a sheet.

Please test before using it on real data.

I hope you can figure it out.

Djblois
08-04-2006, 07:16 AM
Charlize,

It works on your spreadsheet but when I try to get it to work on mine i get an error. Here is my code

Sub HideClosed()
'run this to hide the unwanted data
Dim startrow As Integer 'Start of the data
Dim endrow As Integer 'End of data
Dim actualrow As Integer 'rownumber during loop from start to end
Dim counting As Integer
startrow = 2
endrow = LastCell(Bids_06).Row 'lastcell is a function
actualrow = 2
For counting = startrow To endrow
If Date < Range("O" & actualrow) Then
actualrow = actualrow + 1
Else
Rows(actualrow).EntireRow.Hidden = True 'hide row if not in between dates
actualrow = actualrow + 1
End If
Next counting
End Sub

I get the error on the (Bids_06). The error is byRef type Argument mismatch.

Also does anybody know a place that will explain what each of the error messages mean?

lucas
08-04-2006, 09:16 AM
Daniel, Bids_06 is the name of a sheet and it must be exact.

Djblois
08-04-2006, 11:31 AM
Lucas I know that and it is exact even the case is exact. I even tried changing the name and changing it in the code exact again but it doesn't work. It gives me the same error.

jungix
08-04-2006, 12:14 PM
What is the argument of LastCell? The name of a worksheet as a string? In this case you need to use ""

Djblois
08-04-2006, 12:23 PM
Jungix,

It is a user created function I got from Charlize. It works correctly. Here it is:

Function LastCell(ws As Worksheet) As Range
Dim LastRow&, LastCol%
' Error-handling is here in case there is not any
' data in the worksheet
On Error Resume Next
With ws
' Find the last real row
LastRow& = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
' Find the last real column
LastCol% = .Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End With
' Finally, initialize a Range object variable for
' the last populated row.
Set LastCell = ws.Cells(LastRow&, LastCol%)
End Function

Charlize
08-05-2006, 02:26 PM
Well Djblois,

As lucas already said, the name of your sheet must be exactly the same. go with alt+f11 to the vbe. Click on the sheet and normally you should see the properties of the sheet. The name of the sheet must be Bids_06 (zero six). By default this would be sheet1 or sheet2 ...

Hope this helps.

ps. I made a kb about this. As a bonus you can let the items popup by means of a listbox.

Djblois
08-07-2006, 12:15 PM
Charlize,

Thank you,

I didn't know that in the VBA properties the name might be different than What I named my sheet in Excel. Now it works perfectly