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!

 

[JLGWhiz]

You can try this untested procedure.

Sub t()
Dim c As Range
    With ActiveSheet
        For Each c In .Range("N2", .Cells(Rows.Count, "N").End(xlUp))
            If c.Value <> ""  And c.Offset(, 1).Value = "" Then
                c.Offset(, 1) = c.Offset(, 1).End(xlDown).Value
            End If
        Next
    End With
End Sub

 

[DMO123]

Thanks - it dose not seem to work - it changes all values to the same for some reason.

 

[Akuini]

Could you post some data sample? showing the "before & after" data.
And it isn't clear, do you want to move or copy (the value below up from column O)?
Move & copy are 2 different things.

 

[DMO123]

Sure:

Before:

1 N O
2 Test 1
3 --------Listed
4 Test 2
5 --------Listed
6 Test 3
7 --------Listed
8 Test 4
9 --------Listed
10 Test 5 Listed

After:
1 N O
2 Test 1 Listed
3
4 Test 2 Listed
5
6 Test 3 Listed
7
8 Test 4 Listed
9
10 Test 5 Listed

so as you can see the values in O have moved up one row but as per line 10 not all will be blank

 

[Akuini]

Ok, try this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1083797b()
[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
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR]
[/FONT]

 

[DMO123]

Thank you - i have tested this and nothing seems to happen it gets to

[COLOR=Royalblue][FONT=&quot]For[/FONT][/COLOR][COLOR=#333333][FONT=&quot] i = LBound(vb, [/FONT][/COLOR][COLOR=crimson][FONT=&quot]1[/FONT][/COLOR][COLOR=#333333][FONT=&quot]) [/FONT][/COLOR][COLOR=Royalblue][FONT=&quot]To[/FONT][/COLOR][COLOR=#333333][FONT=&quot] UBound(vb, [/FONT][/COLOR][COLOR=crimson][FONT=&quot]1[/FONT][/COLOR][COLOR=#333333][FONT=&quot]) - [/FONT][/COLOR][COLOR=crimson][FONT=&quot]1[/FONT][/COLOR]

and goes straight to End.

 

[Akuini]

deleted, wrong table

 

[Akuini]

Don't know why that happen. It worked using your sample.
Try using your sample first.

Using your data sample

Book1
	N	O
1	Test 1
2		Listed
3	Test 2
4		Listed
5	Test 3
6		Listed
7	Test 4
8		Listed
9	Test 5	Listed
Sheet4

The result:

Book1
	N	O
1	Test 1	Listed
2
3	Test 2	Listed
4
5	Test 3	Listed
6
7	Test 4	Listed
8
9	Test 5	Listed
Sheet4

 

[DMO123]

My apologies it is working but only when i remove excess data, the example i gave above was how i wanted the data to be displayed but the actual data has multiple rows in like so:

Cause	Det
Not Related	Listed
	Listed
	Listed
	Listed
Not Assessable
	Unlisted
	Unlisted
	Unlisted
Not Related
	Unlisted
	Unlisted
	Unlisted
Not Related
	Listed
	Listed
	Listed
Not Assessable
	Unlisted
Not Assessable
	Unlisted
Not Related
	Listed
	Listed
	Listed

<tbody>
</tbody>