Remove Blank Cells

[skillet]

This is probably not possible, I am on a Mac with multiple versions of MS Excel (including the latest version). I am trying to move the content of cells up into the blank cells based on a range of selection. I know I can

1. select a range and then
2. press F5 and
3. choose "Special..." and
4. choose the radial button "Blanks" and then
5. right click and right click and
6. choose delete
7. Select "Shift cells up" and
8. Press OK

However, I am not trying to delete cells just move the content of cells up so there is no blanks and they are all right next to each other. I have to do this with dozens of cells all the time daily and have been doing this for years but there has to be a better way.

 

[Fazza]

for testing. is this the sort of thing?

Sub moveup()


    Dim i As Long, k As Long
    
    With Selection
        For i = 1 To .Cells.Count
            If Len(.Cells(i).Value) > 0 Then
                k = k + 1
                .Cells(i).Cut Destination:=.Cells(k)
            End If
        Next i
    End With
                
End Sub

 

[skillet]

Thank you that is so very helpful, I have actually been spending hours on this trying to get a few things to work for me and watching tutorials but I am not having success. For some reason this messes up referenced cells when it moves it up and also removes cell borders. Any idea why this might be happening? I must say this is super exciting that this is even possible since I didn't think it was.
http://reference.StudioPrime.com/forums/mrexcel/Excel_Move_Cells_Up.gif

Here is an example file
http://reference.StudioPrime.com/forums/mrexcel/Move_Cells_Up.xlsx

 

[Fazza]

please give a specific example of the requirement

(to be able to write the VBA coding - which is by its nature very specific - an absolutely clear understanding of the requirement is needed. I don't have that.
it might help to have a before and after example. maybe that exists in the example file? say range ax:by looks like 'this' beforehand and changes to whatever afterwards
if there are special formulas or rules or anything, please describe these too

btw, the test code I pasted earlier moves cells up. this is NOT what you asked for, which was to move content.
I could simply change the code to move cell content, but would still need to know if that was just values, or formulas, or comments, etc. Might still not be correct for what you're after

 

[Fazza]

a further thought - I think this will be simpler

please describe the steps you use when you do this manually. such as, I'm thinking it is somehing like,
for a cell being moved, it is copied, and then paste special formulas to the 'moved up' position, and then the original position of it is cleared

cheers

 

[Peter_SSs]

Try this in a copy of your workbook.

Sub Move_Up()
  Dim rLength As Range, rVis As Range, rA As Range
  Dim sFirstAddr As String
  Dim k As Long
 
  Application.ScreenUpdating = False
  Set rLength = ActiveSheet.UsedRange.Find(What:="Length", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
  If Not rLength Is Nothing Then
    sFirstAddr = rLength.Address
    Do
      k = 0
      With rLength.Offset(, -1).Resize(rLength.Offset(, 1).End(xlDown).Row - rLength.Row + 1, 2)
        .AutoFilter Field:=2, Criteria1:="<>"
        Set rVis = .SpecialCells(xlVisible)
        ActiveSheet.AutoFilterMode = False
        For Each rA In rVis.Rows
          rA.Copy Destination:=.Rows(1).Offset(k)
          k = k + 1
        Next rA
        If k < .Rows.Count Then .Offset(k).Resize(.Rows.Count - k).ClearContents
      End With
      Set rLength = ActiveSheet.UsedRange.FindNext(After:=rLength)
    Loop Until rLength.Address = sFirstAddr
  End If
  Application.ScreenUpdating = True
End Sub

 

[skillet]

Wow Peter that is like a nuke to the all the cells on the spreadsheet and completely amazing that can even be done, thank you. I'll have to learn VBA in more depth to understand the magic and apply it to other pages.

Fazza, yes your macro did just what I asked, I didn't anticipate it somehow altering cells to the right of it, my apologies for that. As it stands it is still way better than the process I am currently going through and fixing the cells to the right is better then what I currently have to do.

Here is my step by step process.

1. Select cells
2. Copy cells
3. Clear selected cells with the clear key on a Mac keyboard
4. Move up to the next blank cells
5. Paste the copied cells
6. Repeat 1 - 5 over and over and over...

Here is an Excel spreadsheet with a before and after, titled as such in the columns.
http://reference.StudioPrime.com/forums/mrexcel/Before_and_After.xlsx

Here is an animated gif of my process.
http://reference.StudioPrime.com/forums/mrexcel/Excel_Move_Up_Before_and_After.gif

 

[Peter_SSs]

Wow Peter that is like a nuke to the all the cells on the spreadsheet and completely amazing that can even be done, thank you.

You are very welcome.

 

[Fazza]

howzthis? Just a little different from before

Sub moveup()


    Dim i As Long, k As Long
    
    Application.ScreenUpdating = False
    
    With Selection
        For i = 1 To .Cells.Count
            If Len(.Cells(i).Value) > 0 Then
                k = k + 1
                If k < i Then
                    .Cells(i).Copy Destination:=.Cells(k)
                    .Cells(i).ClearContents
                End If
            End If
        Next i
    End With
                
End Sub

 

[skillet]

That works perfectly, thank you so very much! I will be using this nearly every day, what a time saver!!