#1
|
|||
|
|||
Last row used
Hello,
I am using this line to get the last row and column used: Code:
Set lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireRow Set lastColumn = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireColumn I am getting the last row so that I can use it as a range and then save it to an array so I can loop thru: Code:
Set lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireRow Set lastColumn = ws.Cells.SpecialCells(xlCellTypeLastCell).EntireColumn Set rRange = Range(Cells(1, 1), Cells(LastRow, LastCol)) ' Copy the Range to an Array myarray2 = rRange For r = 3 To UBound(myarray2) Last edited by abenitez77; 04-05-2019 at 04:38 AM. |
#2
|
||||
|
||||
May I remind you of post #3 from this thread ? https://www.msofficeforums.com/excel...tml#post140134
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Quote:
|
#4
|
|||
|
|||
Perhaps something along the lines of
Code:
Sub testing() Dim lastRow As Long, lastColumn As Long, desiredRow As Long Dim ws As Worksheet, rRange As Range, myarray2 As Variant Set ws = ActiveSheet '<<< alter to suit lastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row lastColumn = ws.Cells.SpecialCells(xlCellTypeLastCell).Column desiredRow = Range(Cells(1, 1), Cells(lastRow - 1, lastColumn)).Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row Set rRange = Range(Cells(1, 1), Cells(desiredRow, lastColumn)) myarray2 = rRange MsgBox "First dimension (rows)" & vbLf & _ " lower bound is " & LBound(myarray2, 1) & vbLf & _ " upper bound is " & UBound(myarray2, 1) & vbLf & vbLf & _ "Second dimension (columns)" & vbLf & _ " lower bound is " & LBound(myarray2, 2) & vbLf & _ " upper bound is " & UBound(myarray2, 2) End Sub |
#5
|
|||
|
|||
Thanks, I am using this which is what you have but just added "After:=Cells(18000,5)" It is working fine for me:
Code:
RowLast = Columns(5).Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious, After:=Cells(18000, 5)).Row |
Thread Tools | |
Display Modes | |
|