Paste the current date into a pre-defined range named within a cell

Question

I have the following formula, ="AI2:AI"&MATCH(REPT("z",255),ILS_IMPORT!A:A), which elicits a pre-defined column AI2:AI3576. In essence, the formula is providing me with the exact last row number in which data resides within column A. However, there may be a case whereby there are blanks in the end of the data set within column A, but in column B data goes down to B4000. The whole premise is to get the last row of data from columns A to AI, so that I can put the current date within the range AI2:AI3576 or AI2:AI4000 before importing the data set into the access database.

Two questions (Microsoft Excel 2010):
1) Does anyone have an easier formula that would capture the aforementioned range?
2) Once the range has been established, a simple paste of the current date into that predefined range using VBA would be preferred. I imagine this being very simple since we would have the range and we have the date Now().

As a caveat, I had this VBA code that will tell the user the row number of the last cell, but they would still have to do the copy and paste function. I would rather not have the user do these steps.

Sub LastRowofData()
Dim ws As Worksheet
Dim rng1 As Range
Set ws = Sheets("ILS_IMPORT")
Set rng1 = ws.Columns("A:AI").Find("*", ws.[a1], xlValues, , xlByRows, xlPrevious)
If Not rng1 Is Nothing Then
    MsgBox "The last cell with data is " & rng1.Address(0) & " , therefore copy the current date in AG2 down to the row indicated."
Else
    MsgBox ws.Name & " columns A:AI are empty", vbCritical
End If
End Sub

Your thoughts and creativity are much appreciated.

To be clear, 'the exact last row number in which data resides within column A' should read 'the exact last row number in which a text value resides within column A'. Finding the last number in a column is different. — user4039065
re: put the current date within the range AI2:AI3576 or AI2:AI4000. Your code is commented to say that the date should be in AG2. — user4039065

Unknown Unknown · Accepted Answer · 2015-06-10T23:16:18

By successively redefining the area you are referring to using With...End With statements, you can accomplish this without assigning a range.

Dim ws As Worksheet

Set ws = Sheets("ILS_IMPORT")

With ws
    With Intersect(.Cells(1, 1).CurrentRegion, .Columns("A:AI"))
        If CBool(Application.CountA(.Offset(1, 0))) Then
            With .Offset(1, 34).Resize(.Rows.Count - 1, 1)
                .Cells = Date
            End With
        Else
            MsgBox .Parent.Name & " columns A:AI are empty", vbCritical
        End If
    End With
End With

The primary boundaries of the data is first determined using the Range.CurrentRegion property, then progressive Range.Resize and Range.Offset commands shape the range to receive the current date.

Paste the current date into a pre-defined range named within a cell

1 Answers