Results 1 to 10 of 19

Thread: Delete One Row From A 2D Variant Array

Threaded View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13

    Delete One Row From A 2D Variant Array

    Working in VBA with a 2D array in memory that was formed from a worksheet range is much faster than trying to work with the range directly on the worksheet itself. The way the array is formed is quite simple... just assign the range to a Variant variable. So, if you wanted to create an array in memory for range A1:M1000, you would (should) declare a Variant variable, for example...

    Dim DataArr As Variant

    and then assign the range to it...

    DataArr = Range("A1:M1000")

    Okay, with that brief introduction out of the way, I have seen on more than one occasion requests to be able to delete a single specified row from such a 2D Variant array. Finally, I decided to tackle the problem and see what I could come up with... this non-looping function is the result. The function accepts a 2-D Variant array and a row number for its arguments and it returns a 2-D Variant array with that row number removed.

    Code:
    Function DeleteArrayRow(Arr As Variant, RowToDelete As Long) As Variant Dim Rws As Long, Cols As String Rws = UBound(Arr) - LBound(Arr) Cols = "A:" & Split(Columns(UBound(Arr, 2) - LBound(Arr, 2) + 1).Address(, 0), ":")(0) DeleteArrayRow = Application.Index(Arr, Application.Transpose(Split(Join(Application.Transpose(Evaluate("Row(1:" & (RowToDelete - 1) & ")"))) & " " & Join(Application.Transpose(Evaluate("Row(" & (RowToDelete + 1) & ":" & UBound(Arr) & ")"))))), Evaluate("COLUMN(" & Cols & ")")) End Function
    Note the last line of code is quite long. Here is a macro to test the function with that creates the 2-D Variant array from the range A1:AI1000 (1000 rows by 35 columns), which the macro will fill with data for you, and outputs the array (with row 35 removed) returned by the function to the range starting at cell AK1. Note that the test macro seeds the given range with some data for you so you don't have to do that on your own.

    Code:
    Sub Test()
      Dim Cell As Range, RemoveRow As Long, Data_Array As Variant, ArrLessOne As Variant
      
      ' Seed the range with some data
      For Each Cell In Range("A1:AI1000")
        Cell.Value = Cell.Address(0, 0)
      Next
      
      Data_Array = Range("A1:AI1000")
      RemoveRow = 35
      ArrLessOne = DeleteArrayRow(Data_Array, RemoveRow)
      Range("AK1").Resize(UBound(ArrLessOne, 1), UBound(ArrLessOne, 2)) = ArrLessOne
    End Sub
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://eileenslounge.com/viewtopic.php?p=317218#p317218
    https://eileenslounge.com/viewtopic.php?p=316955#p316955
    https://eileenslounge.com/viewtopic.php?p=316955#p316955
    https://eileenslounge.com/viewtopic.php?p=316940#p316940
    https://eileenslounge.com/viewtopic.php?p=316927#p316927
    https://eileenslounge.com/viewtopic.php?p=317014#p317014
    https://eileenslounge.com/viewtopic.php?p=317006#p317006
    https://eileenslounge.com/viewtopic.php?p=316935#p316935
    https://eileenslounge.com/viewtopic.php?p=316875#p316875
    https://eileenslounge.com/viewtopic.php?p=316254#p316254
    https://eileenslounge.com/viewtopic.php?p=316280#p316280
    https://eileenslounge.com/viewtopic.php?p=315915#p315915
    https://eileenslounge.com/viewtopic.php?p=315512#p315512
    https://eileenslounge.com/viewtopic.php?p=315744#p315744
    https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
    https://eileenslounge.com/viewtopic.php?p=315680#p315680
    https://eileenslounge.com/viewtopic.php?p=315743#p315743
    https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
    https://eileenslounge.com/viewtopic.php?p=314950#p314950
    https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
    https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
    https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
    https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 05-20-2024 at 04:11 PM.

Similar Threads

  1. Replies: 6
    Last Post: 03-26-2014, 03:04 PM
  2. Replies: 1
    Last Post: 02-25-2014, 10:55 PM
  3. Delete Entire Row For All Empty Cells In Column
    By johnreid7477 in forum Excel Help
    Replies: 4
    Last Post: 06-15-2013, 05:50 AM
  4. Delte a specific column and does not delete the top row
    By jffryjsphbyn in forum Excel Help
    Replies: 1
    Last Post: 06-13-2013, 02:00 PM
  5. Replies: 4
    Last Post: 03-22-2013, 01:47 PM

Posting Permissions

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