Microsoft Office Forums

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: 9
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
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
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: 9
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: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
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: 9
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


Other Forums: Access Forums

All times are GMT -7. The time now is 08:41 AM.


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