macro Excel Move Cells upward

hello,

I have an Excel spreadsheet that I need to move evrerything up 2 cells

Starting at B3 to H3 to the bottom of the spreadsheet (which could contain 2000 cells downward

Hope that makes sence.

Is there a macro I could run that could do this?
Ernest GroggAsked:
Who is Participating?
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please give this a try...

Sub ShiftDataUp()
Dim lr As Long
Dim Rng As Range
Application.ScreenUpdating = False
lr = Cells(Rows.Count, 2).End(xlUp).Row
On Error Resume Next
For Each Rng In Range("B1:B" & lr).SpecialCells(xlCellTypeConstants, 3).Areas
    Rng.Resize(, 7).Cut Rng.Cells(1).Offset(-2)
Next Rng
Range("B1:B" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Application.ScreenUpdating = True
End Sub

Open in new window

0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please upload a small sample workbook with original data on one sheet and desired output on another sheet (name it "Desired") so that it would be easy to visualize your requirement more clearly.
0
 
Ernest GroggAuthor Commented:
Sure...

I am attaching
Sample.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
NorieVBA ExpertCommented:
Try this.
Sub MoveThings()

    With Sheets("Original")
        .Range("B1:H2").Delete xlShiftUp
        .Columns(2).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        With .UsedRange
            .WrapText = False
            .Columns.AutoFit
        End With
    End With
    
End Sub

Open in new window

0
 
Ernest GroggAuthor Commented:
thanks to both.  Both work...

You are awesome.
0
 
Ernest GroggAuthor Commented:
umm...so I guess this doesn't split it now?  Plus I need to know how to change the selection...anyone know?
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I guess you have selected the wrong post as assisted solution. You may click on Report Question link under your question and request a moderator to reopen the question and then you can choose the correct post as an assisted answer again.
1
 
NorieVBA ExpertCommented:
So now my solution is not even accepted as an assisted solution.:)
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Norie

I think he is having hard time in splitting the points.
I thought he raised the question as he accepted the wrong post of mine as assisted solution and which was true and that post is still accepted as an assisted solution.
Please raise the report the question again so that he can once again accept your answer as the best answer.
0
 
NorieVBA ExpertCommented:
Subodh

It's not a problem, I think I've seen other people having problems splitting points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.