How to specify "go to End" in an Excel macro 2

[CathodeRay]

I've got an Excel macro that runs just fine, except for one thing: One of its steps is supposed to "go to the last row in the data." When I record the macro, it goes to the last row... but when I view the VBA code, it has a hard-coded reference to the last row number. Obviously, if I have more or less rows, then this macro won't work.

How can I tell the macro to go to the last row of data regardless of how many rows there are?

 

[SkipVought]

Hi,

If your COLUMN of data is contiguous...

set r = SomeRangeInYourColumn.End(xldown)

r is now the range object for the last row of data.




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

faq707-2112
faq707-2115
faq707-4001

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CathodeRay]

Thanks, friends. I will expose my inexperience here, but if I use this code to find the last row and column of a block of data,

Function FindLastRow()
r = ActiveSheet.UsedRange.Rows.Count
c = ActiveSheet.UsedRange.Columns.Count
End Function


how can I move the cursor over to the next empty column to the right?

Said more comprehensively - basically, I've got a block of data, and I need to go to the last row of data, then go to the next empty column in that row. How can I do that?
Thanks!!!!

 

[SkipVought]

    SUB FindLastRow()
    r = ActiveSheet.UsedRange.Rows.Count
    c = ActiveSheet.UsedRange.Columns.Count
    ActiveSheet.cells(r, c+1).select
    End SUB

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[CathodeRay]

Thanks, Skip, that was just what I needed. For some reason (inexperience), I just couldn't get the syntax right. Thank you VERY much.

 

[GlennUK]

Just to note, be aware that such code could give unexpected results if the UsedRange doesn't start in column A ( if column A is blank and never been populated ).

Usedrange is a rectangular reference, but doesn't have to start in the first column or row of a sheet if the leftmost used cell isn't in column A, or if the topmost used cell isn't in row 1 ... if that's the case, then the count of usedrange rows or columns won't point you to where you expect.

Taking this into account, this version of Skip's code would be safer:

    SUB FindLastRow()
    r = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
    c = ActiveSheet.UsedRange.Column + ActiveSheet.UsedRange.Columns.Count -1
    ActiveSheet.cells(r, c+1).select
    End SUB

Cheers, Glenn.

Beauty is in the eye of the beerholder.