Results 1 to 7 of 7

Remove long running loop with someting more efficient

Threaded View

  1. #1
    Forum Contributor JP Romano's Avatar
    Join Date
    10-09-2008
    Location
    Princeton, NJ
    MS-Off Ver
    2010
    Posts
    500

    Red face Remove long running loop with someting more efficient

    Hi... I'm trying to make my spreadsheets run more efficiently, and the first goal I have is replacing as many loops as I can with smarter code. Since I'm neither a programmer, nor all that bright, I'm anticipating some challenges doing so. But since I have more than 20 spreadsheets, each of which can be up to 1 million lines, the loops just aren't cutting it and I'd like to do it better.

    So, the question is, how can I replace something like the code below, which loops through column H and if the active cell contains the value "XXX", the value from 2 columns to the left (F) are copied over?

    Application.ScreenUpdating = False
    Range("H2").Select
    
    Do Until ActiveCell.Offset(0, -7).Value = ""
        If ActiveCell.Value = "" Then
           ActiveCell.Value = ActiveCell.Offset(0, -2).Value
        End If
        ActiveCell.Offset(1, 0).Select
    Loop
    I've tried using filters, but I can't seem to get to ONLY the visible cells - so I kind of gave up on that approach.

    Thanks for any guidance!
    Last edited by JP Romano; 09-10-2009 at 01:40 PM.

Thread Information

Users Browsing this Thread

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

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