Move cells up based on cell above

[cfp14]

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!

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

 

[igold]

How about something like this... Please test on a backup copy of your data. You should be able to leave your formulas in place...

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

 

[Snakehips]

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??

 

[Snakehips]

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
	C	D	E	F
6	Item	Form Reference #	Action Item	Linked CheckBox
7	1	FR-Row7	StuffE7	TRUE
8	2	FR-Row8	StuffE8	TRUE
9	3	FR-Row9	StuffE9	FALSE
10	4	FR-Row10	StuffE10	TRUE
11	5	FR-Row11	StuffE11	FALSE
12	6	FR-Row12	StuffE12	TRUE
13	7	FR-Row13	StuffE13	FALSE
14	8	FR-Row14	StuffE14	FALSE
15	9	FR-Row15	StuffE15	FALSE
16	10	FR-Row16	StuffE16	TRUE
17	11	FR-Row17	StuffE17	FALSE
18	12	FR-Row18	StuffE18	FALSE
19	13	FR-Row19	StuffE19	FALSE
20	14	FR-Row20	StuffE20	FALSE
21	15	FR-Row21	StuffE21	FALSE
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
	C	D	E
6	Item	Form Reference #	Action Item
7	1	FR-Row7	StuffE7
8	2	FR-Row8	StuffE8
9	3	FR-Row10	StuffE10
10	4	FR-Row12	StuffE12
11	5	FR-Row16	StuffE16
12	6
13	7
14	8
15	9
16	10
17	11
18	12
To Do List

Cell Formulas
Range	Formula
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.

 

[cfp14]

How about something like this... Please test on a backup copy of your data. You should be able to leave your formulas in place...

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

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.

 

[cfp14]

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

	C	D	E	F
6	Item	Form Reference #	Action Item	Linked CheckBox
7	1	FR-Row7	StuffE7	TRUE
8	2	FR-Row8	StuffE8	TRUE
9	3	FR-Row9	StuffE9	FALSE
10	4	FR-Row10	StuffE10	TRUE
11	5	FR-Row11	StuffE11	FALSE
12	6	FR-Row12	StuffE12	TRUE
13	7	FR-Row13	StuffE13	FALSE
14	8	FR-Row14	StuffE14	FALSE
15	9	FR-Row15	StuffE15	FALSE
16	10	FR-Row16	StuffE16	TRUE
17	11	FR-Row17	StuffE17	FALSE
18	12	FR-Row18	StuffE18	FALSE
19	13	FR-Row19	StuffE19	FALSE
20	14	FR-Row20	StuffE20	FALSE
21	15	FR-Row21	StuffE21	FALSE

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</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

	C	D	E
6	Item	Form Reference #	Action Item
7	1	FR-Row7	StuffE7
8	2	FR-Row8	StuffE8
9	3	FR-Row10	StuffE10
10	4	FR-Row12	StuffE12
11	5	FR-Row16	StuffE16
12	6
13	7
14	8
15	9
16	10
17	11
18	12

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

To Do List​

Array Formulas Cell Formula 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),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

No need for vba. Hopefully, the principle is good but you will likely need to edit for applicable ranges.

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!

 

[igold]

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.

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.

 

[igold]

Perhaps the cells in column D are not truly blank but contain some sort of non-printing character...

 

[cfp14]

Perhaps the cells in column D are not truly blank but contain some sort of non-printing character...

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)

 

[igold]

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.

 

[Snakehips]

Excel 2010
	E	F	G
6	Linked CheckBox	Form Reference #	Action Item
7	TRUE	FR-Row7	StuffE7
8	TRUE	FR-Row8	StuffE8
9	FALSE	FR-Row9	StuffE9
10	TRUE	FR-Row10	StuffE10
11	FALSE	FR-Row11	StuffE11
12	TRUE	FR-Row12	StuffE12
13	FALSE	FR-Row13	StuffE13
14	FALSE	FR-Row14	StuffE14
15	FALSE	FR-Row15	StuffE15
16	TRUE	FR-Row16	StuffE16
17	FALSE	FR-Row17	StuffE17
18	FALSE	FR-Row18	StuffE18
PSSR

Excel 2010
	C	D	E
6	Item	Form Reference #	Action Item
7	1	FR-Row7	StuffE7
8	2	FR-Row8	StuffE8
9	3	FR-Row10	StuffE10
10	4	FR-Row12	StuffE12
11	5	FR-Row16	StuffE16
12	6
13	7
14	8
To Do List

Cell Formulas
Range	Formula
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.

????????????//