Filtering all but todays date-CodePudding

I'm trying to filter out all but todays date using a macro (for the first time) I want to create a macro or two that will show only rows using the date in which it's viewed. I've tried using the below, but it hides all rows containing a date

Dim cell As Range
For Each cell In Range("A10:A1000")
If cell.Value <= Date Then
cell.EntireRow.Hidden = False
End If
Next
End Sub

CodePudding user response：

        holder.badg_massage.setVisibility(View.VISIBLE);

CodePudding user response：

Basically you don't need VBA to achieve this: you could use the filter functionality of Excel.

But if you want to do it via VBA this is a routine that does what you want:

Option Explicit

Public Sub hideRowsIfNotDate(rgToCheckDate As Range, dateToBeVisible As Date)

Dim c As Range
For Each c In rgToCheckDate.Cells
    If IsDate(c.Value) Then 'just to be on the safe side if there is no date
        If c.Value <> dateToBeVisible Then
            'hide rows with different date
            c.EntireRow.Hidden = True
        Else
            'show rows that match the date - just in case they were hidden before
            c.EntireRow.Hidden = False
        End If
    End If
Next

End Sub

Advantage of this solution:

you can pass different dates or ranges, therefore you can reuse the sub for different scenarios
when you call the sub from your main code you simply know by reading the subs name what it is doing (w/o reading the code itself) - someone else (or you in 3 months) will appreciate that :-)

You will call the routine from your main code like this:


Public Sub test_hideRows()

Dim dateToBeVisible As Date
dateToBeVisible = Date  '= today

Dim rgToCheck As Range
Set rgToCheck = ActiveSheet.Range("A10:A1000")

hideRowsIfNotDate rgToCheck, Date

End Sub

CodePudding user response：

Have you tried AutoFilter? I assume that your data are stored in Rows("10:1000"), dates - in the column A and you have some headers in Rows(9). With that in mind:

Set Source = Rows("9:1000")
Field = Range("A:A").Column
Value = Format(Date, Range("A10").NumberFormatLocal)
Source.AutoFilter Field, Value

If you have a custom date format, put that instead of ...NumberFormatLocal