+ Reply to Thread
Results 1 to 12 of 12

clear only some content from row

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    04-07-2020
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    219

    clear only some content from row

    Hi there , I have the code below that I use to create a second set of rows (3) when i click the command button. Everything works fine but the code ClearsContent in .Rows(lr + 3) totally. I only want columns "A", "F", "M", "Q", "R", "S", "T" cleared when it copies . Columns values "E", "U", "V", "P", "W", "X", "Y", "Z" should not be cleared. Any ideas


    Sub Create_New_CA()
    Dim ColArr, i As Long, nr As Long, lr As Long
    ColArr = Array("A", "F", "M", "Q", "R", "S", "T", "E", "U", "V", "P", "W", "X", "Y", "Z")
    lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    With Sheets("Findings_Summary_Sheet")
        nr = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
        .Range("E" & nr).NumberFormat = "dd/mm/yyyy"
        For i = 1 To 15
            .Cells(nr, i) = ActiveSheet.Cells(lr, ColArr(i - 1))
        Next i
    End With
    With ActiveSheet
        .Rows(lr - 2).Resize(3).Copy .Rows(lr + 1)
        .Rows(lr + 3).ClearContents
        
    End With
    End Sub

  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,696

    Re: clear only some content from row

    You could replace
    .Rows(lr + 3).ClearContents
    by
    .Range("A" & lr+3).ClearContents
    .Range("F" & lr+3).ClearContents
    .Range("M" & lr+3).ClearContents
    'etc
    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 Contributor
    Join Date
    04-07-2020
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    219

    Re: clear only some content from row

    Hi TMS, also get a debug on the second line

     .Range("F" & lr + 3).ClearContents
    the first one clears tough

    .Range("A" & lr + 3).ClearContents

  4. #4
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: clear only some content from row

    maybe this:
    Dim r As Long
    With ActiveSheet
        .Rows(lr - 2).Resize(3).Copy .Rows(lr + 1)
        r = lr + 3
        .Range("A" & r & ", F" & r & ", M" & r & ", Q" & r & ", R" & r & ", S" & r & ", T" & r).ClearContents
    End With
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  5. #5
    Forum Contributor
    Join Date
    04-07-2020
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    219

    Re: clear only some content from row

    Hi JLGWHIZ i get debug error on

    .Range("A" & r & ", F" & r & ", M" & r & ", Q" & r & ", R" & r & ", S" & r & ", T" & r).ClearContents

  6. #6
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,070

    Re: clear only some content from row

    Quote Originally Posted by Chris1976 View Post
    Hi JLGWHIZ i get debug error on
    Would you want to share with us what the message says? I got no error when testing. Did you copy and replace the old code with the new code, or did you manually type in the changes?
    Last edited by JLGWhiz; 12-03-2020 at 07:41 PM.

  7. #7
    Forum Contributor
    Join Date
    04-07-2020
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    219

    Re: clear only some content from row

    Hi JL

    well the top part of the code needs to stay because that is a copy and paste function to a different sheet (transfer of date to Finding Summary Sheet) . The error below

    Attachment 707116

    sorry I did remove the lower sentence End With, there were two of those

    and I noticed some of the cells were MERGED

    It works 100% now, thank you JL
    Last edited by Chris1976; 12-03-2020 at 08:02 PM.

  8. #8
    Forum Contributor
    Join Date
    04-07-2020
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    219

    Re: clear only some content from row

    Just one last question, in Column A I have a Phot Pic, why would that not delete as well

  9. #9
    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,696

    Re: clear only some content from row

    No reason I can see that one would work and one wouldn't. Most likely a mistyped quote mark.

    Nothing to test it with, so it's not tested.

  10. #10
    Forum Contributor
    Join Date
    04-07-2020
    Location
    New Zealand
    MS-Off Ver
    365
    Posts
    219

    Re: clear only some content from row

    Hi TMS, also get a debug on the second line

     .Range("F" & lr + 3).ClearContents
    the first one clears tough

    .Range("A" & lr + 3).ClearContents

  11. #11
    Forum Expert nigelog's Avatar
    Join Date
    12-14-2007
    Location
    Cork, Ireland
    MS-Off Ver
    Office 365 Windows 10
    Posts
    2,286

    Re: clear only some content from row

    to remove pic in column A try
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
    If shp.TopLeftCell.Column = 1 Then shp.Delete
    Next

+ 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. [SOLVED] With clear content
    By Undo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-06-2020, 04:28 PM
  2. vba clear content
    By Undo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-26-2019, 03:56 PM
  3. [SOLVED] Clear content referencing another cell's same content
    By papusale in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-08-2015, 05:21 AM
  4. How to clear content up to row 2?
    By simplyfocus in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-14-2012, 02:17 PM
  5. Clear some content of a row
    By Nina07 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 04-01-2010, 08:37 AM
  6. Clear content
    By spoko in forum Excel General
    Replies: 2
    Last Post: 08-29-2007, 10:51 PM
  7. Clear an XML Map of all content
    By DangerMouse in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-23-2006, 09:45 AM

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