PDA

View Full Version : Solved: extend range of cells



danovkos
04-01-2009, 12:12 AM
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
04-01-2009, 05:35 AM
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
04-01-2009, 05:49 AM
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
04-01-2009, 12:36 PM
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
04-01-2009, 01:49 PM
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.

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
I always find the third method to be most useful

Hope this helps

danovkos
04-01-2009, 11:03 PM
ok, it works fine
thanke you all very much