Free Microsoft Excel 2013 Quick Reference

Shift Cells Up

I have a couple of columns in which I am comparing data and a formula that
runs true or false on the right most column. Where it returns false I have
inserted a blank line to separate that cell entry, but I then need to shift
cells up or down to run the formula again in the rest of he spreadsheet,
however I am not getting the option when I right click to shift cells - can
anyone help?


Post your answer or comment

comments powered by Disqus
Hi

I setup a toolbar icon to automate the delete-shift cells up function but
sometimes it shifts cells up and other times it shifts them to the left, how
can i have it just do shift UP?

thanks

I tried deleting a cell and making the others shif up. When I click delete
the Shift cell up option is greyed out. Does anyone know how to delete a cell
and then shift up another way? Might this be a setting issue?

The before and after tables in sheet2 explain what i want to do.

worksheet change copy paste records example.xlsm

Sub Worksheet_Change(ByVal Target As Range)

Dim GBL As Double

GBL = Range("A3").Value

If Target.Address = ("$C$3") Then

Range("E3, G3").Copy Sheets("Records").Range("A" & Rows.Count).End(xlUp).Offset(1)

'heres the bit i cant do'

'If any value in "Records" is higher than the global value then delete those cells and shift cells up'

End If
End Sub


Hello I'm hoping someone can help me with the following as it's beyond my basic knowledge of VBA.

I have a range of cells ("C2:C24") which are merged, so the range consists of c2, c4, c6, c8 etc upto c24. Data is entered into these cells by the user.

What I want be able to do is when a button is pressed, delete the first two cells (C2 and C4) and shift the remaining cells up without affecting cells outside of the range C2:C24. I'd also need it to give an error message if the current active cell is outside the range c2:c24 so as to prevent other data being deleted.

many thanks for any assistance

Hi,

Quick question: How do I shift all the cells up labelled data, so that there are no blank rows in between? I tried using the ones found on the forum via search but it is stuck in an infinite loop. Thanks.

I'm trying to write a Macro that will delete a cell with the value of "---" and then shift all cells up. What I tried (and does not work) is...

If Cells(x, col) = "---" Then
Cells(x, col) = Delete.cell
End If

Thank you

p.s. It's painfully obvious I have no experience writing macro's

Hi everyone,

I am not sure if what I want to do is possible. I have a spreadsheet with 2 worksheets, ie:

- Parts Details: which contains a table of data
- Pivot Table: which is the pivot table based on data from the 'Parts Details' worksheet + additional details.

In the 'Pivot Table' worksheet, the pivot table is contained from columns A to F, then all data beyond column F (i.e. columns G, H and I in this case) are static, ie data which have been entered manually.

Rows are added to the 'Parts Details' worksheet on a weekly basis, the fact of refreshing the pivot table every week makes the pivot table "grows". This is fine however the cells from the columns which are not part of the pivot table (i.e. columns G, H and I) do not shift up or down according to their Product ID in column B.

Example:

I have provided an example in the attached spreadsheet. Basically copy cell range A2 to G7 from the 'Additional Data' worksheet, then paste the it to cell A21 of the 'Parts Details' worksheet (this is to simulate adding rows to the worksheet). Go to the 'Pivot Table' worksheet, right click on the pivot table and select REFRESH.

You can notice that the pivot table is properly updated, however I would have like the data which are not part of the pivot table (i.e. columns G, H and I) to remain associated to their Product ID present in column B. In this example, we can notice that cells G6, H6 and I6 remain in their original position instead of being shifted down respectively to position G9, H9 and I9.

Would anyone know what kind of macro could allow me to achieve the above?

I think the best way would be to (refer to 'Pivot Table 1' worksheet):

1) Copy the data from column B (i.e. Product ID field of the pivot table) to a static column such a column J (note that I could do this manually)
2) Add the data to the 'Parts Details' worksheet (this would be done automatically however you can use the data from the 'Additional Data' worksheet for this test)
3) Refresh the pivot table which adds rows (note that I'll do this manually)
4) Use a macro to:
a) Look for the value from Cell J3 in column B
b) Determine the row at which the value is found in column B
c) Shift cells G3, H3, I3 and J3 to the row determined in previous step
d) Carry on searching for the next value in column B (i.e. cell J4)
e) And go back to step a) above...
The trick would be not to overwrite the data in step c).

I have tried to explain what I would like to achieve, however I am not sure if that makes sense and if it is possible at all. Hopefully it will to someone who's reading this!

Thanks,
Ant

Excel-XP / Excel 2002...
- The toolbar button DELETE CELLS is programmed to (Delete + shift cells
LEFT).
- I am looking for a way to select a varying range of cells...(use a toolbar
button to delete those cells...then shift cells UP).
- I tried with a macro...but macro will only delete the specific range of
cells in the macro formula.
Any suggestion appreciated.
Hopeful Regards - Texas-Ron

Hi all,

I have a question about shifting cells up in VBA. I have seen alot of posts while searching, with descriptions on how to hide whole rows, or deleting cells but what I need to do is this...

I have a blank column A and then parts listed in column B,C,D and E.

When I click on the Print button (macro that I have created) I want it to HIDE cells in B,C,D,E and shift the cells UP so that no blanks are visible. Then Print out

Can this be done???????

Thanx heaps

I have a workbook that has times in columns D and E, What im trying to do is if the time diff in columns D and E is less than 2 mins I need the row deleting and then the cells shifting up. I.E if D22 and E22 time diff was less than 2 mins I need line 22 deleted and then line 23 shifted up. Can anyone help. Thanks

Can anyone help with a macro that I need.
What im trying to do is where data in column B has the following words "Coventry, Nhamp NJ, Watford Jn,New St" I need that row deleted and then the cells shifting up one row.

Is anyone able to help.

Thanks

I have the following workbook
******** ******************** ************************************************************************>Microsoft Excel - UNITDIAGSRS.xls___Running: xl2002 XP : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA4=
ABCDEFG4*12/09/2007MO1Bletch*CS*03+115K025*12/09/2007MO1Bletchley03+16**6*12/09/2007MO1Bletchley*03+215K027*12/09/2007MO1M*Keynes03+263.312K028*12/09/2007MO1Euston4.385.242N079*12/09/2007MO1Nhampton6.4971Y06Sheet1*
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

What im trying to do is to be able to work right the way down the worksheet starting with cells F and G5 delete them and shift the next 2 up. This will leave row 6 blank of which I need the whole row deleted.

Can anyone help.

1. I am trying to create a Powerpoint Presentation from an Excel sheet which
is linked to a MS-SQL database. When the queries return no data Excel will
shift the cells where the data should go up on the sheet throwing off the
links to the charts. This is UNBELIEVEABLY ANNOYING. How do I stop the
shifting of the cells. I've tried to get around by using a case statement in
the SQL but instead of retuning zeros I get nothing back and the cells shift.
I tried to use SQL.REQUEST but can't seem to get the function installed.

2. How do I set up queries in Excel that don't constantly ask me for the
password to the database?

Thanks

Joe B

I have data in cells A1:A4. I want if cell A2 is deleted using the button delete, the cell will automatically shift up. Can a macro do this?

Is there a way in VBA to shift data upward within a range of cells if the cell above it is blank without physically shifting all cells below it up as well? I need to be able to do this within (6) different ranges.

I belive my example better explains my question.

Dear experts,

Do you know any macro that can move values of cells up if there is any empty cell above, without deleting or shifting the cells up or down.

Thanks and regards
Nemesis

Am wondering if there is a way to stop Excel from shifting cells for MS Query data. This is really messing things up for me.

Thanks

David

Is there any way to shift cells to replace cells that I have deleted.
If I have 3 columns, and I delete an entry in column 1, is there any
way to get the data to shift up 1 cell, and have the cell on top of
column 2, automatically move to the bottom of row 1, and the top cell
on column 3, move to the bottom of column 2.

For examle, 3 columns, and 5 rows
Column A Column B Column C
Row 1) Jim John Frank
Row 2) Ryan Jamal Anne
Row 3) Burt Andy Tom
Row 4) Miguel Kris Paul
Row 5) Todd Missy Dianne

If I delete Burt out of Row A-3, I want Miguel to move up to Row A-3,
Todd to A-4, and John to Column A-5, Jamal to B-1 and Frank to B-5,
Anne to C-1, and so on...

I know you could just delete the Cell A-3, and then drag B-1 to A-5,
and C-1 to B-5, but I'm looking for an easier way.
Thanks for your help.

Good Afternoon,
I have to match up accounts and payments that my company receives with the information from our client which can be different.

There may be different payments for the same account or none on one side but some on the other.

I yanked this code from another site and tried to modify it. The If greater portion is the part I tried to change. The second part still works. I'd like to change the code to shift a range of cells down so that they match up with the appropriate account instead of color the accoutns colors. Please let me know if you need clarification or can provide any help.

Thanks in advance!

Updated code: Macro now shifts the last mismatch down, but not all. Anyway to make this shift on each side until all accounts match up?
The way I see it. There would be an if one side is greater than the other then shift these cells down, but if the other side is greater shift the others down and do a loop. I can't figure it out though :/
Sub ShiftDown()
Dim active_sheet As Worksheet
Dim name1 As String
Dim name2 As String
Dim range1 As Range
Dim range2 As Range
Dim cells1 As Collection
Dim cells2 As Collection
Dim cell1 As Range
Dim cell2 As Range
Dim key As String
Dim no_match As Boolean
Dim greater As Boolean
Dim less As Boolean


    Set active_sheet = ActiveSheet
'    name1 = InputBox$("First Range Name:", "First Range",
' "")
name1 = "Range1"
    If Len(name1) = 0 Then Exit Sub
    Set range1 = active_sheet.Range(name1)

'    name2 = InputBox$("Second Range Name:", "Second
' Range", "")
name2 = "Range2"
    If Len(name2) = 0 Then Exit Sub
    Set range2 = active_sheet.Range(name2)

    ' Make normal collections holding the cells.
    Set cells1 = New Collection
    For Each cell1 In range1.Cells
        key = cell1.Row - range1.Row & "," & cell1.Column - _
            range1.Column
        cells1.Add cell1, key
    Next cell1

    Set cells2 = New Collection
    For Each cell2 In range2.Cells
        key = cell2.Row - range2.Row & "," & cell2.Column - _
            range2.Column
        cells2.Add cell2, key
    Next cell2

    ' Examine the cells in the first collection.
    For Each cell1 In cells1
        On Error Resume Next
        Err.Clear
        key = cell1.Row - range1.Row & "," & cell1.Column - _
            range1.Column
        Set cell2 = cells2(key)
        If Err.number <> 0 Then
            ' The second cell is missing.
            greater = True
        ElseIf cell1.Text > cell2.Text Then
            ' The cells don't match.
            greater = True
        Else
            greater = False
            
    
         End If
         ' Edit to shift Cells in S-U down one row
        If greater Then
            With cell2.Offset(0, -0).Range("a1:c1").Select
            Selection.Insert Shift:=xlDown
            End With
        Else
            With cell1.Interior
                .ColorIndex = xlNone
            End With
        End If
    Next cell1

    ' Examine the cells in the second collection.
    For Each cell2 In cells2
        On Error Resume Next
        Err.Clear
        key = cell2.Row - range2.Row & "," & cell2.Column - _
            range2.Column
        Set cell1 = cells1(key)
        If Err.number <> 0 Then
            ' The second cell is missing.
            less = True
        ElseIf cell2.Text > cell1.Text Then
            ' The cells don't match.
            less = True
        Else
            less = False
        End If

        ' Edit to shift Cells in A-Q down one row
        If less Then
            With cell2.Interior
                .ColorIndex = 35
                .Pattern = xlSolid
            End With
        Else
            With cell2.Interior
                .ColorIndex = xlNone
            End With
        End If
    Next cell2
End Sub


when pressing key Control and + , it will show up menu, it default selection on "Shift cell down", can i change to "Entire row" ? or set the F4 as the "Entire row"?

thanks.

Hi,
I nedd the help to delete blank cells of a particular column J. My data range is not fixed. If there is any deletion of a record the shift cells up formula will work. I don't need to delete the entire row.

I need to delete ALL black text cells, and only keep colored ones.

http://www.putfile.com/pic.php?img=5810668

Please ignore the circles, because I now want to delete ALL black text fields.

In addition, I need the cells shifted up. So it should do the same thing as Right Click --> Delete --> Shift Cells Up --> OK.

Is there any way I can do that with a command or macro? And if so, can you PLEASE tell me how to do that real quick?

Thanks so much, this would save me hours everyday.

I have tried searching and while I was able to find some similar posts, I do not believe my question has been answered. I have a range A2:C43. In column C, there are numbers. I want to delete the information in cells A:C if the result in C is a zero. I would like to do this conditionally, IF c2 is 0 then delete A2:c2 and shift cells up. Then go to the next one. I believe it would make most sense to start from the bottom. If anyone can help me with this I would appreciate it. I do NOT want to delete the entire row. Only the Cells in Columns A:C if C(x)=0. Thank you.

Hello,

I currently have a consolidated worksheet (thanks Bill!) called "Dashboard" that contains closed items that are marked by a validated column that can only contain "Closed, Open, or In-Progress." Is there a way to move the rows with a value of "Closed" to another worksheet called "Completed"? Also when this move is done, that row is no longer necessary in Dashboard and should be removed. So I'm guessing its a lot like a cut and paste and then a delete row/shift cells up?

Edit: here are some additional information:

The worksheet has a locked header that is 6 rows deep (the values for the "Status" column begins on row 7 and on.)

The "status" column is at column 11.

Thanks for your help in advance,
Henry


No luck finding an answer? You could always try Google.