Finding last column and then finding the last row

2019-08-10 17:05发布

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:

enter image description here

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条回答
霸刀☆藐视天下
2楼-- · 2019-08-10 17:41

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 
查看更多
登录 后发表回答