+ Reply to Thread
Results 1 to 9 of 9

Clear range of columns where row is known (J to BC) and row is a variable

  1. #1
    Registered User
    Join Date
    01-07-2015
    Location
    Croydon, England
    MS-Off Ver
    Office 2007
    Posts
    7

    Clear range of columns where row is known (J to BC) and row is a variable

    Thanks for any help on this.
    I want to programmatically clear the contents of a row in Columns J to BC where the row number is a previously-derived variable.
    I have tried variations on something like this - R is the row variable: Worksheets("C1").Range ["J" & r]: ["BC" & r].ClearContents
    but none of them work.
    Grateful for any assistance.
    Nickoby

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,461

    Re: Clear range of columns where row is known (J to BC) and row is a variable

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,461

    Re: Clear range of columns where row is known (J to BC) and row is a variable

    Where's "Croyon"?

  4. #4
    Registered User
    Join Date
    01-07-2015
    Location
    Croydon, England
    MS-Off Ver
    Office 2007
    Posts
    7

    Re: Clear range of columns where row is known (J to BC) and row is a variable

    Thanks TMS but your solution has a floating open square bracket. I tried some variations but no success yet. Casn you suggest something?
    Thanks
    Nickoby

  5. #5
    Registered User
    Join Date
    01-07-2015
    Location
    Croydon, England
    MS-Off Ver
    Office 2007
    Posts
    7

    Re: Clear range of columns where row is known (J to BC) and row is a variable

    Sorry - should be Croydon

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,461

    Re: Clear range of columns where row is known (J to BC) and row is a variable

    Did you try taking out the square bracket? It was an oversight, but not meant to be there ... all the others were removed.

    Tested OK

    Please Login or Register  to view this content.

    Regards, TMS

  7. #7
    Registered User
    Join Date
    01-07-2015
    Location
    Croydon, England
    MS-Off Ver
    Office 2007
    Posts
    7

    Re: Clear range of columns where row is known (J to BC) and row is a variable

    Hi TMS
    I did reply to your above email (but it must have got lost) to say that I tried your solution in the command:
    Worksheets("C" & cat).Range(Range("J" & cfel), Range("BC" & cfel)).ClearContents
    (Variables cat and cfel contain integers eg 1 and, both declared at start of sub routine)
    but all I get is an error message "Application-defined or object-defined error".
    I must be doing something wrong but cannot see what.
    Can you help again?
    Nickoby

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,461

    Re: Clear range of columns where row is known (J to BC) and row is a variable

    Nothing wrong with the line:

    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    01-07-2015
    Location
    Croydon, England
    MS-Off Ver
    Office 2007
    Posts
    7

    Re: Clear range of columns where row is known (J to BC) and row is a variable

    Thanks TMS. I looked into the Error 1004 question on the web; it seems to give a lot of trouble to many people. So far I have not come across an answer. Meanwhile I can get round my problem by clearing each cell individually.
    Regards
    Nickoby

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy Range with Offset Variable and Clear Data
    By ptmuldoon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-28-2014, 09:59 AM
  2. [SOLVED] Clear Range with variable
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-08-2014, 07:47 AM
  3. Clear Contents of a column in a range variable
    By jed38 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-11-2014, 07:59 PM
  4. [SOLVED] vba clear contents of range of columns with certain criteria
    By bkeat in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-11-2013, 09:01 AM
  5. Add the titles to the columns, after Range().clear
    By sacoo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-12-2005, 08:49 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1