CODEWITHSUNDEEP
excelvba
Lucas Wong
Lucas Wong

Reputation: 3

Excel VBA how to set formula use The Last Row logic

Case: I inserted a new columns name:(Date) and (Time) to column D and E. But I can't define the last low in column D and E, because of there have no data under D and E.

What i want to do: column C 1/8/2016 8:24:08

I want to get date 1/8/2016 to column D. and time 8:24:08 to column E. I found some information about how to find the last row or last column. http://www.thespreadsheetguru.com/blog/2014/7/7/5-different-ways-to-find-the-last-row-or-last-column-using-vba But i still not understand how to let vba know the last row. Can please someone can help me know, how am i need to define it. Thank you.

LastColumn = ??
Range("D2:D" & LastColumn).Formula = "=LEFT(C2,(FIND("" "",C2,1)-1))"

My excel page

Upvotes: 0

Views: 1892

Answers (1)

user3598756
user3598756

Reputation: 29421

as to your specific issue

since you already have a "complete" date in column "C" cells, you just need to use TEXT() function to parse it down to the wanted part, as follows:

....FormulaR1C1 = "=TEXT(RC3,""gg/m/aaaa"")"

....FormulaR1C1 = "=TEXT(RC3,""hh:mm:ss"")"

where you'd also use .FormulaR1C1 property to adopt R1C1 style address notation which is more useful for the current purpose letting you write RC3 and refer to column "3" (i.e. column "C") cell in the same row of where you're writing the formula in

as for the lastRow issue

to get the last non empty cell in a given column you'd write something like:

lastRow = Cells(Rows.Count, "C").End(xlUp)).Row '<--| get column "C" last non empty cell row

but please note that should the given column be empty then it'd return 1 as if cell in row 1 were not empty. so you may want to add a check like the following:

lastRow = Cells(Rows.Count, "C").End(xlUp)).Row '<--| get column "C" last non empty cell row 
If .Cells(GetLastRow, "C") = "" Then lastRow = 0 '<--| return 0 if empty column

and handle the case of a return "zero" value

furthermore note that the above code implicitly assumes currently active workbook and worksheet reference, which can often be not safe should you (or your code) make any worksheet/workbook "jumping".

so you'd better add explicit worksheet (and workbook) references like follows:

lastRow = Workbooks("MyWorkbookname").Worksheets("MyWorksheetName").Cells(Workbooks("MyWorkbookname").Worksheets("MyWorksheetName").Rows.Count, "C").End(xlUp)).Row '<--| get column "C" last non empty cell row 
If Workbooks("MyWorkbookname").Worksheets("MyWorksheetName").Cells(GetLastRow, "C") = "" Then lastRow = 0 '<--| return 0 if empty column

where you HAVE to qualify those workbook/worksheet references in EVERY range reference

now, you can take advantage of the With keyword to both lessen the burden of typing all that jazz and make code more readable and maintainable, as follows:

With Workbooks("MyWorkbookname").Worksheets("MyWorksheetName")
    lastRow = .Cells(.Rows.Count, colIndex).End(xlUp).row
    If .Cells(lastRow, colIndex) = "" Then lastRow = 0
End With

so that you can finally type your following GetLastRow() function:

Function GetLastRow(sht As Worksheet, colIndex As Long) As Long
    With sht '<--| refer to the passed worksheet
        GetLastRow = .Cells(.Rows.Count, colIndex).End(xlUp).row '<--| get its passed column last non empty cell
        If .Cells(GetLastRow, colIndex) = "" Then GetLastRow = 0 '<--| check for empty column
    End With
End Function

getting it all together you may may come up to the following code

Sub main()
    With Workbooks("MyWorkbookName").Worksheets("MyData") '<--| refer to your worksheet (change "MyData" to your actual sheet name)
        With .Range("C1", .Cells(.Rows.Count, "C").End(xlUp)) '<--| refer to its column "C" range from row 1 down to last non empty cell
            .Offset(, 1).FormulaR1C1 = "=TEXT(RC3,""gg/m/aaaa"")" '<-- write date in column "C"
            .Offset(, 2).FormulaR1C1 = "=TEXT(RC3,""hh:mm:ss"")" ' <-- write hour in column "C"
            With .Offset(, 1).Resize(, 2)
                .value = .value
            End With
        End With
    End With
End Sub

you may then want to change it and use the GetLastRow() function

Upvotes: 1

Related Questions