How to define the range based on the last used value

[ThrottleWorks]

I am using a loop. I am taking a range from A1 to A5000.

I do not need the range till A5000 but since I am not able to define the last used cell value in the range I am taking A5000.


I am using following line to define the range


Set rng = range(“a1:a5000”)


I can get last used cell by using xlendup but I am not able to replace this value with A5000.

How can I replace A5000 with the last used cell in the column, can anyone please help me in this.

 

[majay1973]

Hi Sachin..


if you have written code for last used as..

lastused = Range("B" & Rows.Count).End(xlUp).Row


then use this..


set rng = Range("A1:A"&lastused) (just concatenate principle)..


i am referring to the word "lastused" .. (you might have used something else)

 

[ThrottleWorks]

Hi Majay, thanks a lot for the help. Have a nice day.

 

[sdsurzh]

Hi Sachin,


Can you try my idea:


'it will go to the range A5000

Range("A5000").Select

'it will select the last data cell from range A5000

Selection.End(xlUp).Select


Thanks,

Suresh Kumar S

 

[ThrottleWorks]

 

[Marc L]

 

Another way :  

Set rng = Range([A1], Columns(1).Cells(Rows.Count).End(xlUp))

Edit :   Avoid using .Select in your code because it slows down …

 

[ThrottleWorks]

Hi Marc, thanks for the insight.

 

[SirJB7]

Hi, sachinbizboy!


A bit later maybe but if still needed you might give a look to this topic:

http://chandoo.org/forums/topic/macro-to-conditionally-highlight-a-tab-when-a-comment-is-entered-into-a-column


In the second procedure, this part checks for the last column used for a particular row, no matter if empty cells in the middle:

-----

[pre]

' detect last non-empty column
lColumnPrevious = 0
lColumnActual = 1
Do Until lColumnPrevious = lColumnActual And lColumnPrevious <> 0
lColumnPrevious = lColumnActual
lColumnActual = .Cells(I, lColumnPrevious).End(xlToRight).Column
DoEvents
Loop
If .Cells(I, lColumnActual).Value = "" Then
lColumnActual = .Cells(I, lColumnActual).End(xlToLeft).Column
End If

[/pre]
-----


Regards!

 

[ThrottleWorks]

Hi SirJB7, thanks a lot for the help, it will certainaly help me.


Have a nice day.

 

[SirJB7]

Hi, sachinbizboy!

Glad if it helps you solve it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

 

[Krishnakumar]

I don't think you need a loop to find the last non empty cell of a particular row


cells(row_no,columns.count).end(xltoleft).column


would give you the last non empty cell of row_no


Kris

 

[SirJB7]

@Krishnakumar

Hi!

I agree with you, I too think you don't need a loop, but I adapted that snippet from a procedure that looked for all embedded empty cells. You should replace my Do...Loop structure by your statement, but you'd have to keep the If...End part for if last column is used, otherwise you'd be returning a 1 (or another value if there are empty cells in the row).

Regards!