Help for Macro: Finding last cell and selecting it
 

Hi

I need help for a macro i am writing.

I need it to find the last used cell and select everything from e.g.
A2 to the last used cell. I can do the functions seperately but
cannot combine it.

Can anyone help? If you can, here's thanks to you

Help for Macro: Finding last cell and selecting it
 

Think i got it with this code:


ActiveSheet.Range("a1:" & ActiveSheet.Range("a1"). _
End(xlDown).Address).Select

On 28 Jan., 19:27, wrote:
Hi

I need help for a macro i am writing.

I need it to find the last used cell and select everything from e.g.
A2 to the last used cell. I can do the functions seperately but
cannot combine it.

Can anyone help? If you can, here's thanks to you

Help for Macro: Finding last cell and selecting it
 

Just be a bit wary that if you have a blank cell in the A column
before your last data row, then you will end up with the wrong range
(as xlDown will stop before the blank cell). To avoid this, you can
go the other way:

Set myRange = Range("A1:A" & Cells(Application.Rows.Count,
1).End(xlUp).Row)

Hope this helps!

Richard

On 28 Jan, 18:34, wrote:
Think i got it with this code:

ActiveSheet.Range("a1:" & ActiveSheet.Range("a1"). _
End(xlDown).Address).Select

On 28 Jan., 19:27, wrote:

Hi

I need help for a macro i am writing.

I need it to find the last used cell and select everything from e.g.
A2 to the last used cell. I can do the functions seperately but
cannot combine it.

Can anyone help? If you can, here's thanks to you

Help for Macro: Finding last cell and selecting it
 

Thanks

Does this mean that my macro should be:

Sub Test()


ActiveSheet.Range("a2:" & ActiveSheet.Range("e8"). _
End(xlToRight).Address).Select

Set myRange = Range("A1:A" & Cells(Application.Rows.Count,
1).End(xlUp).Row)

End Sub

Because if i only enter:

Set myRange = Range("A1:A" & Cells(Application.Rows.Count,
1).End(xlUp).Row)

i get an error message.

On 28 Jan., 19:40, "RichardSchollar"
wrote:
Just be a bit wary that if you have a blank cell in the A column
before your last data row, then you will end up with the wrong range
(as xlDown will stop before the blank cell). To avoid this, you can
go the other way:

Set myRange = Range("A1:A" & Cells(Application.Rows.Count,
1).End(xlUp).Row)

Hope this helps!

Richard

On 28 Jan, 18:34, wrote:

Think i got it with this code:

ActiveSheet.Range("a1:" & ActiveSheet.Range("a1"). _
End(xlDown).Address).Select

On 28 Jan., 19:27, wrote:

Hi

I need help for a macro i am writing.

I need it to find the last used cell and select everything from e.g.
A2 to the last used cell. I can do the functions seperately but
cannot combine it.

Can anyone help? If you can, here's thanks to you

Help for Macro: Finding last cell and selecting it
 

Hi Kasper

You shouldn't need anything else - the code should be fully functional
on its own.

What error do you receive?

By the way (and I don't think this is a problem for you) the way it is
written, the range references default to the Activesheet. Should you
want to specify a range on another sheet (eg Sheet2) then you would
need to write it like:

With Sheets("Sheet2")
Set myRange = .Range("A1:A" & .Cells(Application.Rows.Count,
1).End(xlUp).Row)
End With

Note the dot (.) before both Range and Cells (after the Set
statement).

Best regards

Richard

On 28 Jan, 18:55, wrote:
Thanks

Does this mean that my macro should be:

Sub Test()

ActiveSheet.Range("a2:" & ActiveSheet.Range("e8"). _
End(xlToRight).Address).Select

Set myRange = Range("A1:A" & Cells(Application.Rows.Count,

1).End(xlUp).Row)End Sub

Because if i only enter:

Set myRange = Range("A1:A" & Cells(Application.Rows.Count,

1).End(xlUp).Row)i get an error message.

On 28 Jan., 19:40, "RichardSchollar"
wrote:



Just be a bit wary that if you have a blank cell in the A column
before your last data row, then you will end up with the wrong range
(as xlDown will stop before the blank cell). To avoid this, you can
go the other way:

Set myRange = Range("A1:A" & Cells(Application.Rows.Count,
1).End(xlUp).Row)

Hope this helps!

Richard

On 28 Jan, 18:34, wrote:

Think i got it with this code:

ActiveSheet.Range("a1:" & ActiveSheet.Range("a1"). _
End(xlDown).Address).Select

On 28 Jan., 19:27, wrote:

Hi

I need help for a macro i am writing.

I need it to find the last used cell and select everything from e.g.
A2 to the last used cell. I can do the functions seperately but
cannot combine it.

Can anyone help? If you can, here's thanks to you- Hide quoted text -- Show quoted text -