PDA

View Full Version : Relative selecting to the right (or left)?



JKB
09-09-2014, 06:22 AM
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
09-09-2014, 07:02 AM
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
09-09-2014, 07:08 AM
Thank you very much!!! Lots of possibilites now!

SamT
09-09-2014, 07:24 AM
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.