Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro to Delete Contents (Excel 2002)

    Hello!
    Legare wrote some code for me in regards to copying & pasting data automatically to a new worksheet (in a certain format)... I'm now in need to add to his code so that after it copies and pastes to the new worksheet, that it will then look at the new worksheet and "clear" the contents of the rows that did NOT have anything copied & pasted to it from the other worksheet. I don't wish to actually "delete" the rows as I'd like to keep the spaces as the next time it's used the data that's copied and pasted may be more than this time, and then there wouldn't be enough rows. I've attached a file that shows what I'm trying to acheive... as usual, any help would be appreciated!!
    Thank you!
    LJM

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Delete Contents (Excel 2002)

    You could add the following lines immediately above the End Sub:
    <code>
    lDMax = oDst.Cells.Find(What:="*", SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    For I = 1 To lDMax
    If Range("A" & I) = "" Then
    Rows(I).ClearContents
    End If
    Next I</code>

  3. #3
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Delete Contents (Excel 2002)

    Of course that worked fabulous... thanks for the code Hans!
    LJM

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Delete Contents (Excel 2002)

    Is it possible for this macro to start looking at ROW 15 for which rows need the "deleting of contents"... I need rows 1 thru 14 to stay "as is"...
    Thanks!
    LJM

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro to Delete Contents (Excel 2002)

    As you might expect, you have to change

    For I = 1 To lDMax

    to

    For I = 15 To lDMax

  6. #6
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: Macro to Delete Contents (Excel 2002)

    Of course I'd know this if I had taken my ever elusive class on VBA, however that hasn't happened yet. Yet again, I thank you... someday I'll know more than the microscopic morsel of knowledge I have at this moment!
    Thanks again!!
    LJM

Posting Permissions

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