Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    ClearContent with Range

    Hi Guys,

    I been trying to clear the content of a range in the ActiveSheet but couldn't figure out what I am doing wrong. At the time of the bold line, the ActiveSheet = [1], but the line keeps clearing the content from [Stock]... Any suggestions how to go about it, other then clearing the range cell by cell?

    Code:
            With ActiveSheet
                For I = 3 To 47 Step 1                              'Check every 24 steps down, starting at 2
                    If .Cells(I, "F") = "" Then Exit Sub            'Exit at the first empty cell
                    If .Cells(I, "F") = iName Then                  'Check for match of Item name
                        .Cells(I, "G") = Target                     'Update Qty
                        '-----------------------------------
                        If .Cells(I, "G") = "0" Then                           'Item Name cell in column F is empty so..
                            Application.EnableEvents = False                    'turn events OFF during changes to sheet..
    
                            Temp = "F" & I & ":K" & I
                            Range(Temp).ClearContents 
    ...
    Thanks for looking
    Ferenc

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    try changing:

    Range(Temp).ClearContents

    To:

    .Range(Temp).ClearContents

  3. #3
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    try changing:

    Range(Temp).ClearContents

    To:

    .Range(Temp).ClearContents

    I used ActiveSheet.Range(Temp).ClearContents and it worked...

    Using .Range kept giving me trouble...

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,641
    Thanks
    115
    Thanked 652 Times in 594 Posts
    As long as the end with statement is after

    Code:
    Temp = "F" & I & ":K" & I
    Range(Temp).ClearContents
    it should work fine

  5. #5
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Maudibe View Post
    As long as the end with statement is after

    Code:
    Temp = "F" & I & ":K" & I
    Range(Temp).ClearContents
    it should work fine

    Can I use:
    ["F" & I]

    To replace F2 in the line below?


    Code:
    ActiveSheet.Range("F" & I & ":W47").Sort key1:=[F2], Header:=xlYes, Order1:=xlAscending

    I am trying to sort the sheet alphabetically, starting the position/row where the cells were deleted, to the end of the sheet....

  6. #6
    2 Star Lounger
    Join Date
    May 2015
    Posts
    104
    Thanks
    15
    Thanked 0 Times in 0 Posts

    Post

    I will just make a new post about this particular issue...

    Thank you
    Attached Images Attached Images
    Last edited by Ferenc Nagy; 2015-07-24 at 14:23.

Posting Permissions

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