Dynamic Range

  • Thread starter Thread starter rayh
  • Start date Start date
R

rayh

Hello,

I am working on some VB for spreadsheet. Is there a way to make the
code to dynamically recognize the last cell with data in it?

I currently have my code hard coded (as below) and I wanted to make
the "G222" part dynamic to find the last cellw ith data in it.

Worksheets("Sheet1").Range("A3:G222").Sort _

Thank you very much in advance.
 
assumes a is your LONG row. Don't forget the . s

dim lr as long
with Worksheets("Sheet1")
.lr=.cells(rows.count,"a").end(xlup).row
.Range("A3:G" & lr).Sort
end with
 
Can you pick out a column that always has data in it when that row is used?

Dim LastRow as long
with worksheets("sheet1")
'I used column A
lastrow = .cells(.rows.count,"A").end(xlup).row
.range("A3:G" & lastrow).sort _
....
end with
 
Typo alert.

I bet Don didn't mean to include the . in front of lr, though.

..lr=.cells(rows.count,"a").end(xlup).row
should be
lr=.cells(rows.count,"a").end(xlup).row
 
Ray,

This will look upwards in column G for the bottom-most cell that contains data, and select
from A3 to that cell.

Range(Range("A3"), Cells(Rows.Count, 7).End(xlUp)).Sort _

--
Earl Kiosterud
www.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
My apologies for interrupting here, but I wonder if you could explain how
this works. I guess I'm confused with the row/column interchanges. And what
does the period do?
 
Ray,

This will look upwards in column G for the bottom-most cell that contains data, and select
from A3 to that cell.

Range(Range("A3"), Cells(Rows.Count, 7).End(xlUp)).Sort _

--
Earl Kiosterudwww.smokeylake.com

Note: Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...









- Show quoted text -
Click to expand...

Thanks guys, this is working great! =)
 
The dot in front of .cells() and .rows() means that it refers to the object in
the previous With statement. In this case, it's worksheets("sheet1").

It would be the same as writing:

lastrow _
= worksheets("sheet1").cells(worksheets("sheet1").rows.count,"A").end(xlup)).row

The .end(xlup) is the same thing as going to the last row in column A (A65536 in
xl2003 and below) and then hitting the End key (on the keyboard) followed by the
up arrow.

It stops on that last used cell.
 
Back
Top