Results 1 to 1 of 1

Cut paste delete loop, best way to code? (With example)

Threaded View

  1. #1
    Forum Contributor
    Join Date
    08-29-2017
    Location
    London England
    MS-Off Ver
    2016
    Posts
    194

    Cut paste delete loop, best way to code? (With example)

    Hi all,

    I have the following loop coded:

    Sub Resort()
    
    Application.ScreenUpdating = True
    
    Dim i As Long
    
    For i = Sheet4.ListObjects("SouthTable").DataBodyRange.Rows.Count + 1 To 2 Step -1
    
    'LOOP TESTER
    
    'Sheet4.ListObjects("SouthTable").Range(i, 1).Select
    
    'Application.Wait (Now + TimeValue("00:00:01"))
    
    If Sheet4.ListObjects("SouthTable").Range(i, Sheet4.ListObjects("SouthTable").ListColumns("Region").Range.Column).Value = "HQ" Then
    
    Sheet4.ListObjects("SouthTable").ListRows(i - 1).Range.Cut Sheet4.ListObjects("HQTable").ListRows.Add.Range
    Sheet4.ListObjects("SouthTable").ListRows(i - 1).Range.Delete
    
    End If
    
    Next i
    
    End Sub
    but I'm not convinced this is the best way to do this.
    I don't like the i +1, i-1 parts.. Is there a better way to write this loop?

    Context:
    I have data sorted by regions (countries are in different regions) but some countries can belong to 2 regions, and the user needs to revise the original sort, and change the regions for some data, and this loop (button) would loop through (in this example) the "SouthTable" and if a user changed the region in the South Table for Headquarters (HQ) then cut that row (and delete the empty row) and (add a row into and) paste it into the HQ table.

    Please see the example if its not understandable!

    Looking for any ideas?

    Thanks!!
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] copy, offset, paste, then delete loop
    By Spyderwoman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-22-2018, 05:15 PM
  2. [SOLVED] For next loop to delete empty row (advice my code)
    By jp16 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-28-2018, 09:17 PM
  3. Filtered Data Loop, Copy and Paste into New Workbook, delete first column and save
    By newbiegirl3113 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-20-2016, 03:44 AM
  4. [SOLVED] Code to Loop and Delete Relevant String
    By snaggle93 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-10-2015, 03:19 PM
  5. [SOLVED] VB Code To Delete Rows between 2 flags on loop
    By shauncol3 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-08-2012, 07:46 PM
  6. Loop Copy, Paste, Delete
    By johnsjg in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-07-2011, 02:05 PM
  7. [SOLVED] Adding a loop to conditional delete code
    By maw via OfficeKB.com in forum Excel - New Users/Basics
    Replies: 21
    Last Post: 08-15-2006, 11:15 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1