Microsoft Office Forums Last row used

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-04-2019, 08:52 PM
abenitez77 abenitez77 is offline Last row used Windows 10 Last row used Office 2016
Novice
Last row used
 
Join Date: Apr 2019
Posts: 8
abenitez77 is on a distinguished road
Question 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
It is working but I have a sheet where the last row is row 18488. The problem is that between that row and the next row up is row 98. Everything in between is empty. The last row (18488) has a text "Please do not enter data beyond this row. The price entry limit is 20,000 rows." I want my last row to be row 98. How do I exclude the text in row 18488?

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.
Reply With Quote
  #2  
Old 04-04-2019, 11:40 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Last row used Windows 7 64bit Last row used Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,398
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

May I remind you of post #3 from this thread ? https://www.msofficeforums.com/excel...tml#post140134
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 04-05-2019, 04:39 AM
abenitez77 abenitez77 is offline Last row used Windows 10 Last row used Office 2016
Novice
Last row used
 
Join Date: Apr 2019
Posts: 8
abenitez77 is on a distinguished road
Thumbs up

Quote:
Originally Posted by Pecoflyer View Post
May I remind you of post #3 from this thread ? https://www.msofficeforums.com/excel...tml#post140134
fixed it...thanks
Reply With Quote
  #4  
Old 04-05-2019, 07:05 AM
NoSparks NoSparks is offline Last row used Windows 7 64bit Last row used Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 722
NoSparks will become famous soon enoughNoSparks will become famous soon enough
Default

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
Reply With Quote
  #5  
Old 04-05-2019, 07:33 AM
abenitez77 abenitez77 is offline Last row used Windows 10 Last row used Office 2016
Novice
Last row used
 
Join Date: Apr 2019
Posts: 8
abenitez77 is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes



All times are GMT -7. The time now is 04:49 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft