Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel macro cut/paste/delete do loop

Posted on 2013-05-20
2
Medium Priority
?
960 Views
Last Modified: 2013-05-23
Wanting to create a Excel 2007 macro that loops, or auto repeats until it runs out of data to copy/paste.  I manually recorded the code below. It works, but depending on the data size (small sample file attached), I might have 1 iteration or 5000 to process.  

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B4").Select                   'The pattern is Select (the first step will always be B4)
    Selection.Cut                            'Cut
    Range("H3").Select                   'Select  (a cell up 1 and over 6 in this case H3)
    ActiveSheet.Paste                     'Paste  
    Rows("4:5").Select                    'Select  
    Selection.Delete Shift:=xlUp     'Delete
    Range("B5").Select                   'Repeat until there is no data in the next B cell
    Selection.Cut
    Range("H4").Select
    ActiveSheet.Paste
    Rows("5:6").Select
    Selection.Delete Shift:=xlUp
    Range("B6").Select                 'If empty then stop
End Sub
Sample-File.xlsx
0
Comment
Question by:InfoChase
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 39181749
I have redone it in another way

Sub macro1()
Range("G3:G" & Range("G" & Rows.Count).End(xlUp).Row).Offset(, 1).FormulaR1C1 = "=r[1]c[-6]"
ActiveSheet.UsedRange.Range("H:H").Value = Range("H:H").Value
ActiveSheet.UsedRange.Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Open in new window

0
 

Author Closing Comment

by:InfoChase
ID: 39191590
Perfect.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question