Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts

    clearing contents of an array of ranges

    In office 2013, I have created and populated and then pasted an array of ranges as follows:
    k = 1
    For j = 1 To ModLastCells.LastCellInWS(ws5).Row
    If ws5.Cells(j, 3).Value = "KPMG/" & CostCentre Then
    Set RangeArray2(k) = ws5.Range("A" & j & ":Z" & j)
    k = k + 1
    End If
    Next j

    For j = 1 To k - 1
    RangeArray2(j).Copy WB1.Worksheets("Lending").Range("A" & j + 1 & ":Z" & j + 1)
    Next j

    I now wish to clear the RangeArray so as to re-use it. However I cannot find a way of doing this. The following does not work.

    For j = 1 to 1000
    RangeArray2(j).clear 'or = nil
    Next j

    Can somebody please provide some code to clear the range array

  2. #2
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    try

    .CurrentRegion.Clear
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    milton keynes, Buckinghamshire
    Posts
    252
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Supershoe View Post
    try

    .CurrentRegion.Clear
    Unfortunately this did not work. Neither did = Nothing

  4. #4
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    try
    Erase RangeArray2(j)
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  5. #5
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 986 Times in 916 Posts
    You could DIM the variable again.

    cheers, Paul

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    No need for the j there:
    Code:
    Erase RangeArray2
    will reset the whole array.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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