Select range

[Arne Hegefors]

Hi! I am writing a macro where I want to select a range. the first cell I
want to have in the selection is A1 but the last cell is
Range("a1").offset(i,0). Thus I cannot simple write Range("a1:A3").select.
how shall I write it? pls help!

 

[Mike H]

Try this

i = Cells(Cells.Rows.Count, "A").End(xlUp).Row

Range("a1:A" & i).Select

Mike

 

[Arne Hegefors]

thanks. but that does not work for me since A1 is not a fixed cell just an
example. I tried writing:

Range("a1" & Range("a1").Offset(i, 9)).Select

but that did not select the whole area but just the two cells. please please
can someone help me!

"Mike H" skrev:

 

[Mike H]

How is the first cell in the range determined

The first populated cell ? Try this

p = Range("A1").End(xlDown).Row
i = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("a" & p & ":A" & i).Select


Or in a loop? Try this

For x = 1 To 5
topcell = Range("A1").Offset(x).Row
bottomcell = Cells(Cells.Rows.Count, "A").End(xlUp).Row
Range("A" & topcell & ":A" & bottomcell).Select
Next

Mike