Finding last column and then finding the last row

问题:

I need to find the last column in a worksheet and then find the last row in that particular column. For last column I am using this:

lastcol = .Cells(1, .Columns.count).End(xlToLeft).Column
last_row = Range((Cells(Rows.count), lastcol).Find("*", after:=r, LookIn:=x1values, lookat:=x1part, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)

Here is snapshot of the worksheet:

Can some also help me with putting all the data I want to save in an array 2 dimensional and then pasting the data on a Excel sheet?

回答1:

Here is how:

lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
lastrow = .Cells(.Rows.Count, lastcol).End(xlUp).Row

.Cells(lastrow + 1, lastcol).Resize(UBound(myarray)) = myarray

.

Let's assume the batch data are sourced in Sheet1 beginning in cell A1, here is how to make the 2D array:

Dim myarray

myarray = Sheet1.[a1].CurrentRegion