Delete rows where row numbers are stored in array

  • Re: Delete rows where row numbers are stored in array


    Thank you to mention the crosspost
    Can you attach a sample of your file to show how arrays are?

    Triumph without peril brings no glory: Just try

  • Re: Delete rows where row numbers are stored in array


    Assuming row number in array as number try

    Triumph without peril brings no glory: Just try

  • Re: Delete rows where row numbers are stored in array


    Quote from Craggs82;4592874

    Are the numbers in ascending order? if so you could perhaps loop through the array and keep a counter to account for the offset caused by removing rows.


    They're in numerical order, but not sequential.


    I have some code that loops through a variant array (called DataRange, which in itself was populated by reading a sheet into it, as I believe it's quicker to loop through an array than loop through the rows on the sheet).


    One of the bits of code in this loop identifies rows for deletion:


    Code
    If booFlag = True Then
                      rowsToDelete = IIf(rowsToDelete = vbNullString, intRow, rowsToDelete & "," & intRow)
     End If


    (booFlag is set earlier in the loop, if the row is identified as one to be deleted.


    So what I have now is a string of row numbers, separated by a comma.


    I then a have a string array, called deleteArray, populated using:


    Code
    deleteArray = Split(rowsToDelete, ",", -1, vbBinaryCompare)


    I then write my original DataRange array back to the worksheet, then delete the rows using:


    Code
    For j = UBound(deleteArray) To LBound(deleteArray) Step -1
                 .Range("A" & deleteArray(j)).EntireRow.Delete
             Next j


    It SEEMS to work. Can anyone see anything wrong with this approach?


    Thanks

  • Re: Delete rows where row numbers are stored in array


    No, it is a good way.
    To speed up and avoid to go from bottom to top, you can use the UNION and delete in shot
    For example

    Triumph without peril brings no glory: Just try

  • Re: Delete rows where row numbers are stored in array


    Quote from PCI;774829

    No, it is a good way.
    To speed up and avoid to go from bottom to top, you can use the UNION and delete in shot
    For example


    I'm not sure how I would apply that to my code. Could you break it down please?

  • Re: Delete rows where row numbers are stored in array


    HI..


    Quote

    I have an array loaded with row numbers that I'd like to delete.


    Can you change that to an array of row numbers you would like to keep?


    If so.. you could load all the values from your sheet into a seperate array and use these ' rows you want to keep' array(2d array) in the 2nd argument of the Application.Index method..


    All done in memory.. faster than me saying goodbye to the 'cook' and goin to the pub.. ;)


    Like PCI says. attach a file for us to play with.

  • Re: Delete rows where row numbers are stored in array


    Quote from apo;774840

    HI..




    Can you change that to an array of row numbers you would like to keep?


    Easily, yeah, sure.


    Quote

    If so.. you could load all the values from your sheet into a seperate array and use these ' rows you want to keep' array(2d array) in the 2nd argument of the Application.Index method..


    Not sure I follow this bit...

  • Re: Delete rows where row numbers are stored in array


    I think I have the below working:


    Code
    Set deleteRange = .Range("A" & CInt(deleteArray(LBound(deleteArray))))
            
            For j = LBound(deleteArray) + 1 To UBound(deleteArray)
                Set deleteRange = Union(deleteRange, .Range("A" & deleteArray(j)))
            Next j
  • Re: Delete rows where row numbers are stored in array


    Replace

    Code
    For j = UBound(deleteArray) To LBound(deleteArray) Step -1 
        .Range("A" & deleteArray(j)).EntireRow.Delete 
    Next j


    by

    Code
    Set DelRg = Cells(Rows.Count, 1).End(3).Offset(1, 0) 
         
        For Each WkR In deleteArray
            Set DelRg = Union(DelRg, Cells(WkR, 1)) 
        Next 
        DelRg.EntireRow.Delete


    NOT TESTED

    Triumph without peril brings no glory: Just try

  • Re: Delete rows where row numbers are stored in array


    If your data is in a continuous block (i.e. no empty cells in the column that holds the value that is used to determine if a row is to be deleted) then there is no need to have code that first makes an array of those row numbers, via your boolean variable and building of the string. The entire operation can be carried out with this:


    The code will check all the cells in the column that holds the cell value used to determine row deletion, replace that value with an empty cell, then delete all rows which have an empty cell in the determining column.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Re: Delete rows where row numbers are stored in array


    Quote

    If so.. you could load all the values from your sheet into a seperate array and use these ' rows you want to keep' array(2d array) in the 2nd argument of the Application.Index method..
    Not sure I follow this bit...


    Something like this:



    There is a way to create the array of rows to be 'kept' without a loop using a formula within the Evaluate method... search through jindons posts to see that magic..

  • Re: Delete rows where row numbers are stored in array


    Neat code, apo :)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!