Relative selecting to the right (or left)?

[JKB]

Hi out there!

I have a simple problem, that must have a simple solution but i couldnt find one!

Usually when i want to select a range relatively (with that i mean all the way down, if the cell contains something) i use the following method:


sht.Range("A1:F" & Range("A1").End(xlDown).Row).ClearContents if i wanted to clear the contents of cells from A1 to F (and then all the way down until there is an empty cell).

But i would like to replace this "F" now, since i want to be able to expand the width inside the program. How do i do this?
Put in another way: How do i choose ctrl+shift+down AND THEN ctrl+shift+right and select in vba-language?

[SamT]

For specific Ranges

Range(Range("A1"), Cells(Range("B2").End(xlDown).Row, Range("C3").End(xlRight).Col))

The Row at the bottom of B and the column at the end of Row 3


Or

Range(Range("A1"), Range(Range("A1").End(xlDown).End(xlRight)))

The end of the Row at the bottom of Column A


Which is the very similar, but not the same as

Range("A1").CurrentRegion

The last continuous row and the last continuous column, starting at A1


But different than

Sheets("X").UsedRange

All used rows and all used columns.

[JKB]

Thank you very much!!! Lots of possibilites now!

[SamT]

I forgot the most commonly used

LastCol = Range("A1").End(xlToRight).Column
LastRow = Range("A1").End(xlDown).Row
Range(Range("A1"), Cells(LastRow, LastCol))

LastRow is probably the single most used of all these in VBA for Excel.