Results 1 to 5 of 5

Thread: clearing contents of array of ranges using a single line of code

  1. #1
    Junior Member
    Join Date
    May 2012
    Posts
    25
    Rep Power
    0

    clearing contents of array of ranges using a single line of code

    Hi,

    i have made global string constants for ranges.

    PHP Code:
    Global Const G_Cl As String "G2:G13"
    Global Const G_Vl As String "I2:I13"
    Global Const G_VlS As String "L2:L13" 
    i want to clear these ranges using a single line of code.

    PHP Code:
    With wkData
        
    .Range(G_ClG_VlG_VlS).ClearContents
    End With 
    but i get error:

    "Wrong no of arguments or invalid property assignment!"
    i even tried this:

    PHP Code:
    With wkData
        
    .Range(Array(G_ClG_VlG_VlS)).ClearContents
    End With 
    OR

    PHP Code:
    With wkData
        
    .Range([{G_ClG_VlG_VlS}]).ClearContents
    End With 
    how can i clear all the ranges in one line of code?

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi,

    Try with UNION

    Code:
    Union(.Range(G_Cl), .Range(G_Vl), .Range(G_VlS)).ClearContents
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Junoon View Post
    i have made global string constants for ranges.

    PHP Code:
    Global Const G_Cl As String "G2:G13"
    Global Const G_Vl As String "I2:I13"
    Global Const G_VlS As String "L2:L13" 
    i want to clear these ranges using a single line of code.

    PHP Code:
    With wkData
        
    .Range(G_ClG_VlG_VlS).ClearContents
    End With 
    but i get error:
    If you did it without using the constants, this is how the code line would look...

    Code:
      .Range("G2:G13,I2:I13,L2:L13").ClearContents
    To duplicate this structure, you have to concatenate the constants together using commas between them in order to get the right syntax for the multiple ranges within the Range call. Try it like this...

    Code:
    With wkData
      .Range(G_Cl & "," & G_Vl & "," & G_VlS).ClearContents
    End With
    Last edited by Rick Rothstein; 05-12-2012 at 08:25 PM.

  4. #4
    Junior Member
    Join Date
    May 2012
    Posts
    25
    Rep Power
    0
    Hi,

    Thank you for your solutions. this has definitely resolved my issue. please consider this thread as resolved.

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi Junoon,

    Thanks for the feedback.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Exclude Contents From List :
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 16
    Last Post: 06-08-2013, 12:29 AM
  2. Clearing Data from Specific cols
    By Howardc in forum Excel Help
    Replies: 3
    Last Post: 05-15-2013, 08:39 PM
  3. Replies: 14
    Last Post: 01-26-2013, 04:58 AM
  4. Clearing UsedRange Of WorkSheet Programmatically
    By tfurnivall in forum Excel Help
    Replies: 1
    Last Post: 12-04-2012, 09:05 AM
  5. Replies: 3
    Last Post: 05-14-2012, 11:30 AM

Tags for this Thread

Posting Permissions

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