Selecting Range to last row with data

[dagindi]

I cannot figure out the correct code that will replace

Please Login or Register  to view this content.

with the code that will select everything in column B starting with B2 and go all the way to the absolute last row with data (not just stop at the first row that might be blank).

[Marcol]

Try this

Please Login or Register  to view this content.

Cheers

[dagindi]

Marcol,

Worked like a charm! Thanks.

Can you explain to me how the code knows to continue search for data even if it comes across a blank row?

[dmaesj]

Please Login or Register  to view this content.

[Marcol]

It doesn't

LastRow = works up from the last cell in the column (LastRow is just a variable name)

Assuming that iit is blank it will find the first cell with data and get the row number.

1/. Rows.Count is the number of rows in a sheet
Therefore
Range("B" & Rows.Count).=Range("B65536"). in Excel 2003

2/. .End(xlUp) is the direction to go this can also be (xlDown), (xlToRight) or (xlToLeft)

3/. .Row is the row number


Hope this helps.

If you need any further information, please feel free to ask further

However

If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
Also
If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.

[boringg]

you can aslo use

lastrow=activecell.SpecialCells(xlCellTypeLastCell).Row

Range("B2:B" & lastrow).select

[dagindi]

So if I am using Excel 2007 will the code start at Row 1048576?

[dmaesj]

If you use

Please Login or Register  to view this content.

It will find the last used row. You can try either way and see which is faster for your macro

[Marcol]

boringg and dmaesj

Your solutions will work on some occasions

They are both based on UsedRange

Lets say Column B has 30 rows of data and your sheet also has Data in say Range("A500")

Your solution will highlight Range("B2:B500") not Range("B2:B30")

The solution I offered is almost universally used among Excel Users, best go with it.

dagindi

So if I am using Excel 2007 will the code start at Row 1048576?

Yes it will work with any version of Excel (Except the recent Mac Versions)