Solved

Excel macro cut/paste/delete do loop

Posted on 2013-05-20
2
819 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
2 Comments
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 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

ScreenConnect 6.0 Free Trial

Check out the updates in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI that improves session organization and overall user experience. See the enhancements for yourself!

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

831 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