Move cells up based on cell above

C

cfp14

New Member
Joined
Jul 10, 2019
Messages
9
Hi All,

I need some major help haha. I'm a total newbie to VBA, started out of necessity. I need to write a function that when data from sheet 1 is inserted to sheet 2 it looks at that new data and if the cell above it is empty it moves the cell up until no empty cells remain. So essentially top justifying the cells, if that makes any sense. I don't want to just delete the empty rows as I have a set amount of 50 and need it to stay at 50. I'm not sure what the best way to go about this is but I'll give you what I have. For all I know (not a whole lot) this is all wrong. Any help is greatly appreciated!

Code: 
Sub Move_Cell_Up()
    Dim rng As Range
    Dim cell As Range
    Set rng = ActiveSheet.Range("D7:D56,E7:E56")
    For Each cell In rng
        If ActiveCell.Offset(1, 0) = " " Then
            Selection.Offset(1, 0).Select
        Else
            Selection.Offset(0, 0).Select
        End If
    Next cell
End Sub
 
Sort by date Sort by votes
How about something like this... Please test on a backup copy of your data. You should be able to leave your formulas in place...

Code: 
Sub SkipBlanks()


    Dim rng As Range
    Dim arr(1 To 50, 1 To 2)
    Dim i As Integer, x As Integer
    
    Set rng = Range("D7:E56")
    For x = LBound(rng.Formula) To UBound(rng.Formula)
        If Not rng.Formula(x, 1) = "" Then
            i = i + 1
            arr(i, 1) = rng.Formula(x, 1)
            arr(i, 2) = rng.Formula(x, 2)
        End If
    Next
    Range("D7:E56").ClearContents
    Range("D7").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    
End Sub
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Are you able to give me the formulas which you have in D7 and E7, which I assume are then copied down to row 56??
 
Upvote 0
OK, given some assumptions on my part.....
You have sheet 'Options' which includes 50 checkboxes which are used to select line options.
You have a column linked to the checkboxes. Assumed column F

So similar to below.


Excel 2010
CDEF
6ItemForm Reference #Action ItemLinked CheckBox
71FR-Row7StuffE7TRUE
82FR-Row8StuffE8TRUE
93FR-Row9StuffE9FALSE
104FR-Row10StuffE10TRUE
115FR-Row11StuffE11FALSE
126FR-Row12StuffE12TRUE
137FR-Row13StuffE13FALSE
148FR-Row14StuffE14FALSE
159FR-Row15StuffE15FALSE
1610FR-Row16StuffE16TRUE
1711FR-Row17StuffE17FALSE
1812FR-Row18StuffE18FALSE
1913FR-Row19StuffE19FALSE
2014FR-Row20StuffE20FALSE
2115FR-Row21StuffE21FALSE
Options


You then want formulas in a "To Do List' sheet that bring in the selected items, top justified rather than spaced out as per the Options list.
Maybe like the array formula in D7 below. Entered using Ctrl + Shift +Enter then dragged across and down as required?


Excel 2010
CDE
6ItemForm Reference #Action Item
71FR-Row7StuffE7
82FR-Row8StuffE8
93FR-Row10StuffE10
104FR-Row12StuffE12
115FR-Row16StuffE16
126
137
148
159
1610
1711
1812
To Do List
Cell Formulas
RangeFormula
D7{=IFERROR(INDEX(Options!D$7:D$56,MATCH(SMALL(IFERROR($C$7:$C$56/Options!$F$7:$F$56,"X"),$C7),IFERROR($C$7:$C$56/Options!$F$7:$F$56,"X"),0),1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.


No need for vba. Hopefully, the principle is good but you will likely need to edit for applicable ranges.
 
Upvote 0
igold said:
How about something like this... Please test on a backup copy of your data. You should be able to leave your formulas in place...

Code: 
Sub SkipBlanks()


    Dim rng As Range
    Dim arr(1 To 50, 1 To 2)
    Dim i As Integer, x As Integer
    
    Set rng = Range("D7:E56")
    For x = LBound(rng.Formula) To UBound(rng.Formula)
        If Not rng.Formula(x, 1) = "" Then
            i = i + 1
            arr(i, 1) = rng.Formula(x, 1)
            arr(i, 2) = rng.Formula(x, 2)
        End If
    Next
    Range("D7:E56").ClearContents
    Range("D7").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
    
End Sub
Click to expand...
That works exactly like I wanted it to thank you! At the moment its only working for 3 lines though, trying to figure that part out.
 
Upvote 0
Snakehips said:
OK, given some assumptions on my part.....
You have sheet 'Options' which includes 50 checkboxes which are used to select line options.
You have a column linked to the checkboxes. Assumed column F

So similar to below.

Excel 2010
CDEF
ItemForm Reference #Action ItemLinked CheckBox
1FR-Row7StuffE7
2FR-Row8StuffE8
3FR-Row9StuffE9
4FR-Row10StuffE10
5FR-Row11StuffE11
6FR-Row12StuffE12
7FR-Row13StuffE13
8FR-Row14StuffE14
9FR-Row15StuffE15
10FR-Row16StuffE16
11FR-Row17StuffE17
12FR-Row18StuffE18
13FR-Row19StuffE19
14FR-Row20StuffE20
15FR-Row21StuffE21

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]TRUE[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]18[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]19[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]20[/TD]

[TD="align: right"]FALSE[/TD]

[TD="align: center"]21[/TD]

[TD="align: right"]FALSE[/TD]

</tbody>
Options​



You then want formulas in a "To Do List' sheet that bring in the selected items, top justified rather than spaced out as per the Options list.
Maybe like the array formula in D7 below. Entered using Ctrl + Shift +Enter then dragged across and down as required?

Excel 2010
CDE
Form Reference #Action Item
FR-Row7StuffE7
FR-Row8StuffE8
FR-Row10StuffE10
FR-Row12StuffE12
FR-Row16StuffE16

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]6[/TD]
[TD="align: center"]Item[/TD]

[TD="align: center"]7[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]9[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]10[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]11[/TD]
[TD="align: center"]5[/TD]

[TD="align: center"]12[/TD]
[TD="align: center"]6[/TD]

[TD="align: center"]13[/TD]
[TD="align: center"]7[/TD]

[TD="align: center"]14[/TD]
[TD="align: center"]8[/TD]

[TD="align: center"]15[/TD]
[TD="align: center"]9[/TD]

[TD="align: center"]16[/TD]
[TD="align: center"]10[/TD]

[TD="align: center"]17[/TD]
[TD="align: center"]11[/TD]

[TD="align: center"]18[/TD]
[TD="align: center"]12[/TD]

</tbody>
To Do List​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D7[/TH]
[TD="align: left"]{=IFERROR(INDEX(Options!D$7:D$56,MATCH(SMALL(IFERROR($C$7:$C$56/Options!$F$7:$F$56,"X"),$C7),IFERROR($C$7:$C$56/Options!$F$7:$F$56,"X"),0),1),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



No need for vba. Hopefully, the principle is good but you will likely need to edit for applicable ranges.
Click to expand...
The formula I'm using is this =IF(PSSR!E7=TRUE,PSSR!F7," ") . It gets the true or false from another sheet. Ill give this formula a try, thank you!
 
Upvote 0
cfp14 said:
That works exactly like I wanted it to thank you! At the moment its only working for 3 lines though, trying to figure that part out.
Click to expand...

Can you describe the issue a little more. It should look at your entire range of D7:E56, and depending whether or not any cell in Column D is blank, condense and move the range up, starting at row 7.
 
Upvote 0
Perhaps the cells in column D are not truly blank but contain some sort of non-printing character...
 
Upvote 0
igold said:
Perhaps the cells in column D are not truly blank but contain some sort of non-printing character...
Click to expand...
It condences the first 3 lines but only those three. Everything else moves up but the spaces are still there. And I don't see why there would be. I tried clearing formatting and everything else and no luck. The formulas are all the same too (excluding sequential cell numbering)
 
Upvote 0
Perhaps a sample of your data would help us to figure out what is going on. When you say the formulas are all the same, is that good or bad. If you want the formulas to change their reference, that is not going to happen with this method.
 
Upvote 0

Excel 2010
EFG
6Linked CheckBoxForm Reference #Action Item
7TRUEFR-Row7StuffE7
8TRUEFR-Row8StuffE8
9FALSEFR-Row9StuffE9
10TRUEFR-Row10StuffE10
11FALSEFR-Row11StuffE11
12TRUEFR-Row12StuffE12
13FALSEFR-Row13StuffE13
14FALSEFR-Row14StuffE14
15FALSEFR-Row15StuffE15
16TRUEFR-Row16StuffE16
17FALSEFR-Row17StuffE17
18FALSEFR-Row18StuffE18
PSSR




Excel 2010
CDE
6ItemForm Reference #Action Item
71FR-Row7StuffE7
82FR-Row8StuffE8
93FR-Row10StuffE10
104FR-Row12StuffE12
115FR-Row16StuffE16
126
137
148
To Do List
Cell Formulas
RangeFormula
D7{=IFERROR(INDEX(PSSR!F$7:F$56,MATCH(SMALL(IFERROR($C$7:$C$56/PSSR!$E$7:$E$56,"X"),$C7),IFERROR($C$7:$C$56/PSSR!$E$7:$E$56,"X"),0),1),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.


????????????//
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
VBA - Intersect code being re-initiated
Replies
1
Views
189
Peter_SSs
Peter_SSs
J
  • Solved
Copy /past cells by inputbox on sheet 2 to sheet1 using VBA
2
Replies
12
Views
502
DanteAmor
DanteAmor
T
  • Solved
Help with VBA Code in Excel
Replies
2
Views
306
tixarah
T
M
  • Solved
Macro to move cells and delete lines
Replies
6
Views
351
Midas
M
C
  • Solved
Count number of non-empty lines in a multiline cell
2
Replies
11
Views
271
Fluff
Fluff

Forum statistics

Threads
1,223,908
Messages
6,175,304
Members
452,633
Latest member
DougMo

Share this page

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