Solved: extend range of cells

[danovkos]

hi all,
pls. can you help me with one i belive easy question?
how can i extend range of cells in code (i think do in code „shift+up“)?

i know how can i move, how can i do ctrl+home, ctrl+shift+home but i can finding out how do i shift+up or shift+down in code.

please can you help me?

thx

[stanleydgrom]

danovkos,

Try:

 
Dim LR As Long, LC As Long
Dim rng As Range
LR = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = .Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
' This will set 'rng' from cell A1, to the LR LC cell in you worksheet.
Set rng = Range(Cells(1, 1), Cells(LR, LC))

'Or try:
Dim rng As Range
Set rng = ActiveSheet.UsedRange

Have a great day,
Stan

[danovkos]

THX, but it doesnt works.
the short vs. do nothing and the secon return error "Complile error - Invalid or Unqualified reference" and stops in debug on this code:

LR = .Cells.

[stanleydgrom]

danovkos,

Try:

 
Dim LR As Long, LC As Long
Dim rng As Range
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
LC = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByColumns).Column
' This will set 'rng' from cell A1, to the LR LC cell in you worksheet.
Set rng = Range(Cells(1, 1), Cells(LR, LC))

'Or try:
Dim rng As Range
Set rng = ActiveSheet.UsedRange

Have a great day,
Stan

[georgiboy]

Some examples of using shift to select cells/ranges...

Place some data in column A and run one of the below to see its effect.

[vba]Sub ShiftDownRange()
' to select a range using shift down from cell A1
Range(Range("A1"), Range("A1").End(xlDown)).Select

End Sub

Sub ShiftDownCell()
' or just to select a cell using shift down from cell A1
Range("A1").End(xlDown).Select

End Sub

Sub ShiftUpRange()
' to select a range using shift up from last cell in column
Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Select

End Sub[/vba]
I always find the third method to be most useful

Hope this helps

[danovkos]

ok, it works fine
thanke you all very much