Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067

    Unanswered: Selecting Multiple Cells of Same Value

    Im Just Wondering if anyone can help on this one.
    Ive written a piece of VBA Code that moves rows of data when one of the fields is a specific value into a new worksheet.

    How it is working at the moment is that i select the range i look for then
    i replace the values i want with blanks
    i select the rows that have blank cells in them
    and then copy and paste them into a new worksheet
    then i replace the values back in.

    the code i use is
    Code:
    Lastrow = range("A1").end(xldown).row
    Myrange = "AB1:AB" & Latrow
    range(Myrange).replace MyVar, ""
    range(Myrange).specialcells(xlcelltypeblanks).entirerow.select
    selection.copy
    worksheets(DumpSheet).activate
    activecell.pastespecial
    Range("A1").end(xldown).offset(1,0).select
    Worksheets("Download").activate
    selection.delete
    This works relatively quickly but this needs to run about 4000 times.
    DumpSheet is a string Variable passed into the sub routine.

    The Whole process takes about 1/2 - 1 hour to run which is deadtime for the user as they can't use their PC while its happening

    Any Ideas
    David

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    Don't have a "solution" per se, but a couple of questions.

    Why are replacing the values, then pasting them back into the other sheet? I may not understand your needs, but that seems like an inefficient way to copy the data to another sheet. Are you wanting to copy that data to another sheet, then delete that data from the original sheet?

    Also, perhaps it is a typo, but in your second line of code, should that be?

    Code:
    Lastrow = range("A1").end(xldown).row
    Myrange = "AB1:AB" & Lastrow
    Normally if you select something, then do something with the selection you can omit the selection, so instead of:

    range(Myrange).specialcells(xlcelltypeblanks).enti rerow.select
    selection.copy

    Replace it with:
    range(Myrange).specialcells(xlcelltypeblanks).enti rerow.copy

    Finally, it is not always necessary to select something to do something with the range/object. Selection slows the process.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Thanks for the Reply

    Firstly im replaceing the Values of the cells so i can select all the cells of a specific value among a range of values for example find everything that equals A1ARI

    A1ARI
    A9ARI
    C7ARI

    through experimentation the replace of the values to blanks then selecting all the blanks is by far the quickest way i have found of copying accross the data.

    The data set tends to be about 50000 rows long and quite a mess
    The values get moved onto a seprate sheet so a lot more processing can get done on the removed rows i.e. counting unique values under certain conditions, find new instances of various factors and clearing out redundant data. I found that the processes i put in place to do this took far too long on the original data set and th operators PC would not be usuable for a long time.

    Also, perhaps it is a typo, but in your second line of code, should that be?



    code:--------------------------------------------------------------------------------
    Lastrow = range("A1").end(xldown).row
    Myrange = "AB1:AB" & Lastrow--------------------------------------------------------------------------------
    sorry that is a typo.

    finally ive just had an idea how things could be improved if i cut the data out of the sheet then sort i could get rid of the lengthy deletes as long as nothing dodgy goes on. I need to clear the dataset of all used rows to find new instances of anything that has appeared so new records can be updated within other systems(Sounds like verbal diarrhoea i know)
    This has been a work in progress for almost a year now with continual updates.

    Any other suggestions would be most welcome ill try to see if i can ditch any of the select statements to see if the speed picks up

    Thanks
    David

  4. #4
    Join Date
    Dec 2003
    Location
    Östersund Sweden
    Posts
    60
    Hi David et al,

    Before I start to discuss the perfomance-issue I wonder if You have had or will have problems with the SpecialCells-method.

    By design it is limit to 8192 non-contiguous cells.

    There exist several approaches that may decrease the time for running it but it is subject how the data is stored in the worksheet.

    Is it possible for You to upload an example - Just to have a better understanding.

    Thansk in advance,
    Dennis
    Kind regards,
    Dennis

Posting Permissions

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