Dynamic way to end loop if selection is empty

[Joakim1]

Hi,

I am trying to get the following loop to end when Selection.End(xlToRight) is empty. It has to be a dynamic way of finding the last column containing figures and end if there are no ones containing values.

The full string that should be empty is:

Range("D3").Select
Selection.End(xlToRight).Select

Since this is the most dynamic way I found of going to the next column with a value

Can anyone help me with a way of doing this since the "Do Until" pasted below here does not work.

Many thanks!

Please Login or Register  to view this content.

[Saurabh guddu]

Hi,

This return last column including blank.
Try code this might work as you.

Mid(ActiveSheet.UsedRange.Address, InStr(ActiveSheet.UsedRange.Address, ":") + 2, 1)

Thanks

Saurabh

[Joakim1]

Thank you for your help. Unfortunately that does not work. Just using that line gives me error

13 - Type Mismatch

and inserting it in the IsEmpty formula just gives me the same result. It keeps on looping, getting to the end of the worksheet, copying all rows in that column and comes up with the "paste overlapping" error.

IsEmpty(Mid(ActiveSheet.UsedRange.Address, InStr(ActiveSheet.UsedRange.Address, ":") + 2, 1))

Your string is above my level so it might just have been that I do not know how to use it

Thanks

Joakim

[Saurabh guddu]

Hi Joakim,

Try below code, mic require L-Value that the reason you got 13-type mismatch error.

last_column = Mid(ActiveSheet.UsedRange.Address, InStr(ActiveSheet.UsedRange.Address, ":") + 2, 1)


Thanks

Saurabh

[Joakim1]

Hi,

Thank you so much!

I actually solved the problem now this morning with the following

Do Until IsEmpty(Selection.End(xlToRight))

This seems to pick up that when the column it comes to is blank it stops.

Thank you for your support!

Joakim