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 GroggSecurity Management InfoSecAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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 GroggSecurity Management InfoSecAuthor Commented:
Sure...

I am attaching
Sample.xlsx
0
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

NorieAnalyst Assistant Commented:
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 GroggSecurity Management InfoSecAuthor Commented:
thanks to both.  Both work...

You are awesome.
0
Ernest GroggSecurity Management InfoSecAuthor 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
NorieAnalyst Assistant Commented:
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
NorieAnalyst Assistant Commented:
Subodh

It's not a problem, I think I've seen other people having problems splitting points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.