Relative Excel Names. AutoFilter by Date and Time in VBA

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download THIS MONTHS SPECIALS
This month we have some truly great specials on Excel Templates and Add-ins. These are from the same developers of the ever
popular All Excel Templates Gold Edition . The above Template collection comes at a 80% discount off individual prices and now also includes an extra 30 FREE financial templates. Not enough? Ok, also get the Complete Excel Training Course AND the Add-ins Collection .5 Excel Add-Ins For Business Success Plus Instructional Workbooks || Operating Management Excel Add-ins

EXCEL TIPS AND TRICKS

The most common type of range name is an Absolute Range Name . An absolute range name refers to a specific cell or range as an absolute address like $A$1. This is because when you create a named range, you are usually creating a range to refer to a specific part of a worksheet.

A relative named range is a named range that refers to a range that is relative to the position of the cell pointer at the time they are created. This makes them perfect for formulas that are required to be moved around a worksheet and are easy to create. When you create a relative named range it is VERY important that you have the position of the cell pointer in the correct cell before naming.

Let’s say we have a spreadsheet and we want to create relative names for it so that we can create a running Total.

Column A contains invoice numbers and is called Inv. No. Column B contains Vendors names, and is headed Vendors, Column C is called Debit and column D is called Credit. In column E we want to place our formula. This column is titled Total. Assume there are a mixture of numbers and blank cells in columns C and D.

The first thing that we need to do is go to Insert>Name>Define. Under Names in Workbook type in Vendor. Now press the Tab key three times to jump to the Refers to: area of the dialog. In this area type =$B:$B, (absolute) then select Add, then Close.

We need to also add another three named ranges:

Go to Insert>Name>Define and add the following absolute named ranges:

Debit =$C:$C
Credit =$D:$D
Total =$E:$E

Now we need to create relative names for our rows in order for our formula to work. Select cell E2

Go to Insert>Name>Define and under Names in Workbook type in This, then jump to the Refers to box and type in the relative range: =2:2

We need to add one more relative named range:

Prior =1:1

Click Add, then Close.

Because we had the cell pointer firstly in row 2, This will always refer to the current row. Prior will always refer to the row above.

Now to write the formula.

In cell E2, type in the following formula:

=Debit-Credit+IF(Vendor=Prior Vendor, Prior Total)

Hit Enter, then with your fill handle , copy down the page.

See Also Links Below if Not Familiar With AutoFilter in VBA

Excel VBA & AutoFilter AutoFilter provides us with a MUCH faster alternative to loops of all kinds
Excel VBA AutoFilter Criteria How to add the criteria for AutoFilter in VBA.
Excel VBA AutoFilter Dates Using dates in AutoFilter can be tricky if not using the US date format.

AutoFilter by Date & Time With VBA

The 3 example codes below show how we can AutoFilter by date and time with VBA code .

Sub ByDateTimeGreater()
Dim dDate As Date, dTime As Date
Dim strDate As String
Dim lDate As Long, dblTime As Double
Dim dDateTime As Double

    dDate = DateSerial(2006, 9, 12)
    lDate = dDate
    
    dTime = TimeSerial(12, 8, 0)
    dblTime = dTime
    
    dDateTime = lDate + dblTime
    Range("A1").AutoFilter Field:=1, Criteria1:=">" & dDateTime
End Sub


Sub ByCellDateTimeGreater()
Dim dDate As Date, dTime As Date
Dim strDate As String
Dim lDate As Long, dblTime As Double
Dim dDateTime As Double
Dim rDate As Range, rTime As Range

Set rDate = Sheet1.Range("G1") 'Cell housing date & time

    If Not IsDate(rDate) Then 'Check if valid
      MsgBox "Non valid date and time in Sheet1 G1)"
      Exit Sub
    End If
    
    dDate = DateSerial(Year(rDate), Month(rDate), Day(rDate))
    lDate = dDate
    
    Set rTime = rDate
    dTime = TimeSerial(Hour(rTime), Minute(rTime), Second(rTime))
    dblTime = dTime
    
    dDateTime = lDate + dblTime
    Range("A1").AutoFilter Field:=1, Criteria1:=">" & dDateTime
End Sub


Sub ByDateTimeEqual()
Dim dDate As Date, dTime As Date
Dim strDate As String
Dim lDate As Long, dblTime As Double
Dim dDateTime As Double

    dDate = DateSerial(2006, 9, 12)
    lDate = dDate
    
    dTime = TimeSerial(12, 8, 0)
    dblTime = dTime
    
    dDateTime = lDate + dblTime
    
    '= to does not work for non US date systems. So as with dates, we use
    'Greater than or Equal to and Less than or Equal to the SAME date & time.

    Range("A1").AutoFilter Field:=1, Criteria1:=">=" & dDateTime, Operator:= _
        xlAnd, Criteria2:="<=" & dDateTime
End Sub

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

OzGrid Excel and VBA Newsletter September 2006

Newsletter Index

EXCEL TIPS AND TRICKS

EXCEL VBA TIPS AND TRICKS