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?

Re: Delete rows where row numbers are stored in array

Are rows number in order in the array ??

Re: Delete rows where row numbers are stored in array

Assuming row number in array as number try

Option Explicit


Sub Treat()
Dim WkAr
Dim WkR
Dim objSortedList  As Object
Dim i  As Integer
   WkAr = Array(4, 6, 8, 10)
   Set objSortedList = CreateObject("System.Collections.Sortedlist")
   
   With objSortedList
      For Each WkR In WkAr
         objSortedList.Add WkR, Empty
      Next
      For i = .Count - 1 To 0 Step -1
          Cells(.GetKey(i), 1).EntireRow.Delete
      Next
   End With


End Sub

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

Sub Treat1()
Dim WkAr
Dim WkR
Dim DelRg  As Range
   WkAr = Array(4, 6, 8, 10)
   Set DelRg = Cells(Rows.Count, 1).End(3).Offset(1, 0)
   
   For Each WkR In WkAr
       Set DelRg = Union(DelRg, Cells(WkR, 1))
   Next
   DelRg.EntireRow.Delete


End Sub

Re: Delete rows where row numbers are stored in array

Can you send the complete macro you are using and better attach your file

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

Replace

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

by

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

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:

Sub DeleteRows()
    Dim x, i As Long
    
    With Sheets("sheet1").UsedRange.Columns("a")    '// Change sheet name and column to suit
        x = .SpecialCells(2)
        For i = 1 To UBound(x, 1)
            If x(i, 1) = "xxxxx" Then '// Determine if row is to be deleted, change to suit
                x(i, 1) = vbNullString
            End If
        Next
        .SpecialCells(2) = x
        .SpecialCells(4).EntireRow.Delete
    End With


End Sub

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.

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:

Private Sub CommandButton1_Click()
    Dim x, xx, i As Long, StrRows As String
    With [A1].CurrentRegion
        x = .Value: xx = Application.Transpose(.Columns(1).Value)
        For i = LBound(xx) To UBound(xx)
            If xx(i) = "valuetokeep" Then StrRows = StrRows & i & Chr(32)
        Next i
        .ClearContents
        [A1].Resize(UBound(Split(StrRows, Chr(32))), 2).Value = Application.Index(x, Application.Transpose(Split(Trim(StrRows))), [transpose(row(1:2))]) ' Change 1:2 as needed..  example shown is for 2 columns
    End With
End Sub

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