Excel UsedRange property 1

[SkipVought]

Hi, Gabg!

Somewhere in the murky, lurky past year or so, I recall a discussion of the Used Range property and a caveat or two regarding its getting reset or not.

I am trying to identify the LAST ROW of data (not necessarily contiguous, otherwise, I'd be using CurrentRegion). But here's what's happnin'...

1. The last row of data is x
2. Data is entered in row x + 2
3. I use the following code to find x + 2...

    With ActiveSheet.UsedRange
        r2RT = .Rows.Count + .Row - 1
    End With

4. The data in row x + 2 is deleted via .ClearContents method (Del key)
5. r2RT has the value of x + 1. I would expect x!

Does anybody have a clue about what is happening???

Thanx!

Skip,
SkipAndMary1017@mindspring.com

 

[RobBroekhuis]

In a quick mockup of what you're doing, I get right back down to x after deleting the cell on x+2. Must be something specific about your worksheet.
Rob

 

[SkipVought]

I wonder.

I have a break right at the assign statement and that's where I am testing the value.

If I do a Delete Shift Up, I DO get the expected result.

Skip,
SkipAndMary1017@mindspring.com

 

[rmikesmith]

Skip,

This may be useful to you or others. The following function generalizes the technique I always use to determine the last row containing data:

Function GetLastUsedRow(ByVal StartCol As Integer, Optional ByVal EndCol) As Long
Dim Col As Integer
Dim LastRow As Long
Dim MaxLastRow As Long

  If IsMissing(EndCol) Then EndCol = StartCol
  MaxLastRow = 0

  For Col = StartCol To EndCol
    If IsEmpty(ActiveSheet.Cells(65536, Col).Value) Then
      LastRow = ActiveSheet.Cells(65536, Col).End(xlUp).Row
    Else
      LastRow = 65536
    End If
    If LastRow > MaxLastRow Then MaxLastRow = LastRow
  Next Col

  GetLastUsedRow = MaxLastRow

End Function

As you can see, supply the starting and ending column number over which you want to determine the last used row. This is helpful if you want to manipulate a range of data but the last used row may be vary by column. The function returns the last used row having the greatest row number. To check a single column, enter its number as the only parameter.


HTH
Mike

 

[SkipVought]

mike,

Thanx for the code. I have some similar. I was trying to find out what the problem might be with the UsedRange property. Seems that I read about some sort of quirk eons ago.

Skip,
SkipAndMary1017@mindspring.com

 

[xlbo]

Yup - usedrange CAN (but not always) be larger than the actual used range. This can happen when items are cleared out of cells or when formats are left applied

Ways round it:

use activesheet.usedrange.rows.count
or save the spreadsheet

Basically, any reference to the usedrange object will make it "re-examine" itself and correct for any extra rows / columns it may be carrying.

another thing to remember is that if, for whatever reason, your data doesn't start in row 1, the usedrange.rows.count will not return the last row used

eg

data starts in row 10 and there are 100 lines of data
Usedrange.rows.count will return 100 but the last line of data will be 110

I know that it is not good spreadsheet design but sometimes the "powers that be" demand certain formatting

HTH Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[SkipVought]

Geoff,

Thanx! That is what I had read but could not for the life of me remember.

As always, you distinguish yourself as an EXPERT among experts. Skip,
SkipAndMary1017@mindspring.com