How to move up a cell in a loop

Sotongli

New Member
Joined
Aug 5, 2014
Messages
5
I'm kind of struggeling with VBA for excel. I want to consolidate data in one specific column B by removing blank cells. Unfortunately, I can't delete the entire rows as it will remove data in other columns.

Any idea how to loop through column B and move up non-blank cells?



incorrect.JPGresult.JPG
Dataset and expected result
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Hi, you can do it like this.
  1. Select column B and press F5
  2. Click the "Special" button
  3. Select "Blanks" in the options and click "OK"
  4. Click "Delete" > "Delete Cells" on the "Home" tab of the ribbon
  5. Choose "Shift cells up" in the options and click "OK".
If you really need VBA then you can record yourself carrying out these steps and you will get a very good basis for the code.
 
Upvote 0
Hi, thanks for your response. Unfortunately, this apprach doesn't work as the formulas in other columns produce errors when referrencing to column B.

Code:

Columns("B:B").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp


Is there a way in VBA using copy & paste?
 
Upvote 0
this apprach doesn't work as the formulas in other columns produce errors when referrencing to column B.

Hi, perhaps we need the fuller picture and an example data set that demonstrates what the before and after should look like when formulas are involved?

Try to use the XL2BB addin to post your example data to the forum so we can easily copy it for testing.

 
Last edited:
Upvote 0
I mean copying value 1 in above example to B2 from B4, value 2 to B3 from B8 etc using a loop.

The data input is given by the data source and data is used in formulas in other columns doing some calculations.

When I run the above code from the macro recorder the reference produces an error in the formula, i.e. from
=SUMPRODUCT((-SUMIF($B$2:$B$2,"<0")>SUMIF(OFFSET(B$1,,,ROW($B$1:$B$1)),">0"))*(-SUMIF($B$2:$B$2,"<0")-SUMIF(OFFSET($B$1,,,ROW($B$1:$B$1)),">0"))*(D$2:D2-N(+D$1:D1)))-SUM(E$1:E1)
to
=SUMPRODUCT((-SUMIF(#REF!,"<0")>SUMIF(OFFSET(B$1,,,ROW($B$1:B1)),">0"))*(-SUMIF(#REF!,"<0")-SUMIF(OFFSET($B$1,,,ROW(B$1:B1)),">0"))*(D$2:D2-N(+D$1:D1)))-SUM(E$1:E1)
 
Upvote 0
I mean copying value 1 in above example to B2 from B4, value 2 to B3 from B8 etc using a loop.

You could try this - although it might be more efficient to use the other method and re-apply the affected formulas programmatically.

VBA Code:
Sub shiftup()
Dim R As Range

Set R = Range("B1")

Do
    Set R = R.End(xlDown)
    If R.Row < Rows.Count Then
        If R.Offset(-1).Value = "" Then
            R.End(xlUp).Offset(1).Value = R.Value
            R.ClearContents
        End If
    Else
        Exit Do
    End If
Loop
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top