Moving Numbers ONLY

[Hyflex]

Hey Guys,

Here is a copy of the macro I've been trying to get working, could someone help make it better and fix it for the bug what I've come across

Basically, I'm trying to move any cell in the ADM:ADV range what has a number in it across to columns at the left (M to V) for example)

Thanks in advance
- Hyflex

Please Login or Register  to view this content.

[zbor]

Will LARGE function help withou need of the loop if your new values can be in descending order? Or SMALL for ascending

[AB33]

No wonder why you getting errors!
I have to admit this is the first time in my life to see Range("ADW19:ADW")
I have never been to this part of excel. My limit has been two digits range
The code works, but not if a cell is empty. I kept getting errors until I put some dummy data ine each cell. I think you will have a fun of using this code.
You could also change the lastrow line in to

Please Login or Register  to view this content.

[tigeravatar]

Hyflex,

It's difficult to test this without a sample workbook, but here's a revamped version of that code you provided, which should work quicker and be error free:

Please Login or Register  to view this content.

[Hyflex]

Will LARGE function help withou need of the loop if your new values can be in descending order? Or SMALL for ascending

Hi Zbor,

I'm slightly confused by what you said :S

No wonder why you getting errors!
I have to admit this is the first time in my life to see Range("ADW19:ADW")
I have never been to this part of excel. My limit has been two digits range
The code works, but not if a cell is empty. I kept getting errors until I put some dummy data ine each cell. I think you will have a fun of using this code.
You could also change the lastrow line in to

Please Login or Register  to view this content.

The last row always has to be done by column A because of how my document is designed, I cant get around the issue where if the range is empty it throws up an error.

[zbor]

Well just make loop for i from 1 to COUNT values in A column with application.worksheetfunction.large("A1:A60000", i)

[AB33]

John,
I have put some dummy data in ADM19:ADW19(Was really good fun to go all the way to these columns) and the code works

[Hyflex]

Hey Tigeravatar,

Thanks for your reply, I'm really confused as to why yours has two ranges in RngCheck

[tigeravatar]

Hyflex,

It is because the code you posted in your original post skips column ADU. So you have AD.. M,N,O,P,Q,R,S,T and then V,W. Between T and V should be U, but your code doesn't have that, so the code I provided mirrors that, hence the two ranges "ADM:ADT,ADV:ADW"

[Hyflex]

Hey tigeravatar,

Ahh my bad, I clearly don't know my alphabet. It was 10 columns from ADM, so AD.. M, N, O, P, Q, R, S, T, U, V

I just tried to modify your macro but I can't get it to work now.

[tigeravatar]

Because it is now a continuous block of cells, the macro can be somewhat simplified:

Please Login or Register  to view this content.

[Hyflex]

Amazing, you never fail to make perfect macros.

One last change is I need to make all of the numbers turned into 0 decimal places. I tried the following:

Please Login or Register  to view this content.

but anything what isn't a number is removed for some reason

[Hyflex]

Still been unable to fix this last small bug, anyone have any ideas?

[tigeravatar]

Try:

Please Login or Register  to view this content.

[Hyflex]

Thanks tigeravatar, it seems like the issue is in the first part of the macro.

If over at: ADM19:ADV & LR is a number it should move it to the left but if it's blank it shouldn't do anything.

It wipes cells what have text in over at the left otherwise.

[tigeravatar]

So the cells at the left are not already blank, but have existing text in them, and the macro is overriding them to be blank cells when it brings the numbers over from the ADM:ADV range?

If that is the case, give this version a try:

Please Login or Register  to view this content.