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.
By successively redefining the area you are referring to using With...End With statements, you can accomplish this without assigning a range.
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.