VBA to move a cell value up if blank

[DMO123]

HI, im looking for a VBA code that will help me with an issue. i have data in column N but if in column O the cell is blank i need it to move the value below up so the cell in column O is no longer blank.

essentially this if IF column N has a value and column O is blank copy the value below up from column O.

can someone help, been searching online and cant find much. i can get it working for a formula but i need to to run via a button and i needs to run on 10000 cells.

thanks!

 

[Akuini]

Ok, using your last example, how should the result look like?
Using my code, here's the result:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>N</th><th>O</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Cause</td><td style=";">Det</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Not Related</td><td style=";">Listed</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style=";">Listed</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style=";">Listed</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style=";">Listed</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Not Assessable</td><td style=";">Unlisted</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style=";">Unlisted</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style=";">Unlisted</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Not Related</td><td style=";">Unlisted</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style=";">Unlisted</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style=";">Unlisted</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Not Related</td><td style=";">Listed</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style=";">Listed</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;"></td><td style=";">Listed</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Not Assessable</td><td style=";">Unlisted</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Not Assessable</td><td style=";">Unlisted</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Not Related</td><td style=";">Listed</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style="text-align: right;;"></td><td style=";">Listed</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style="text-align: right;;"></td><td style=";">Listed</td></tr></tbody></table><p style="width:4,8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet5</p><br /><br />

 

[DMO123]

Yes that is correct it should look how you have it - using the code below nothing happens?

[COLOR=Royalblue][FONT=&quot]Sub[/FONT][/COLOR][COLOR=#333333][FONT=&quot] a1083797b()[/FONT][/COLOR][FONT=&quot][I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1083797-vba-move-cell-value-up-if-blank.html[/COLOR][/I]
[COLOR=Royalblue]Dim[/COLOR] rng [COLOR=Royalblue]As[/COLOR] Range, va, vb, i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], n [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
     
    n = Range([COLOR=brown]"N:O"[/COLOR]).Find([COLOR=brown]"*"[/COLOR], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    vb = Range([COLOR=brown]"N1:N"[/COLOR] & n)
    [COLOR=Royalblue]Set[/COLOR] rng = Range([COLOR=brown]"O1:O"[/COLOR] & n)
    va = rng
   
    [COLOR=Royalblue]For[/COLOR] i = LBound(vb, [COLOR=crimson]1[/COLOR]) [COLOR=Royalblue]To[/COLOR] UBound(vb, [COLOR=crimson]1[/COLOR]) - [COLOR=crimson]1[/COLOR]
       
        [COLOR=Royalblue]If[/COLOR] vb(i, [COLOR=crimson]1[/COLOR]) <> [COLOR=brown]""[/COLOR] [COLOR=Royalblue]And[/COLOR] va(i, [COLOR=crimson]1[/COLOR]) = [COLOR=brown]""[/COLOR] [COLOR=Royalblue]Then[/COLOR]
            va(i, [COLOR=crimson]1[/COLOR]) = va(i + [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR])
            va(i + [COLOR=crimson]1[/COLOR], [COLOR=crimson]1[/COLOR]) = [COLOR=brown]""[/COLOR]
        [COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
       
    [COLOR=Royalblue]Next[/COLOR]
 
rng = va 
End Sub[/FONT]

 

[Akuini]

Yes that is correct it should look how you have it - using the code below nothing happens?

I don't understand why that happen. I used the code and that was the result I got.

Let's try something fresh.
Copy your data sample in your post (#10), then paste to a new sheet then run the code. See what happen.

 

[DMO123]

So have done the above - it works but what is really strange is if i copy the data from #10 it works if i copy the data from my spreadsheet the code dose nothing but they are identical?

i put all this in a new document and still get the same issue, i ran the data from #10 first then swapped it for the data from my spreadsheet and the code did nothing?

so in short the code is working but i have an issue somewhere, any help would be appreciated!

thank you!

 

[Akuini]

Hm, without the actual workbook, I can only guess, maybe the blanks in col O are not really blank?
Try something like this:
- copy-paste the data to a text editor (notepad)
- copy the data from that text editor
- paste back to a new sheet
- run the code

If it still don't work, maybe you can upload part of your workbook (without sensitive data) somewhere (uptobox.com or google drive) then put the link here.

 

[DMO123]

Thank you - its working now i didn't realise the cells were not blank my mistake! i didn't think they would be populated as its come from a database but anyway thank you so much for your help!

 

[Akuini]

You're welcome & thanks for the feedback.