+ Reply to Thread
Results 1 to 3 of 3

Clear Contents of Cells

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    11-20-2008
    Location
    UK
    Posts
    132

    Clear Contents of Cells

    In my worksheet I have a button which generates a new invoice number and clears the contents of certain fields. This is the code:

    Sub PostToCommercialTracking()
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Set WS1 = Worksheets("Tracking Sheet")
    Workbooks.Open "M:\Commercial Clients\Current Year Test.xlsx"
    Set WS2 = Worksheets("Current")
    
    ' Figure out which row is the next row
    NextRow = WS2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    
    ' Write the important values to the Commercial Tracker
    WS2.Cells(NextRow, 1).Resize(1, 4).Value = Array(WS1.Range("D2"), WS1.Range("B1"), WS1.Range("D1"), WS1.Range("F1"))
    
    End Sub
    
    Sub NextJobNumber()
    Range("D2").Value = Range("D2").Value + 1
    Range("A4:A9,B1:B9,C32,C33,D1,D3,D4,D10,E8,G1:G13,I6,J1:J5,K1:K13").ClearContents
    ActiveSheet.CheckBoxes.Value = False
    Range("E18,E19,E20,E21,E22,E23,E24,E25,E26,E27,E28,E29,E30").Value = "Select"
    End Sub
    
    Sub SaveTrackingSheetWithNewName()
    Dim NewFN As Variant
    ' Copy Tracking Sheet to a new workbook
    ActiveSheet.Copy
    NewFN = "M:\Commercial Clients\Commercial Booking Forms\" & Range("D2").Value & Range("B1").Value & Range("D1").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
    ActiveWorkbook.Close
    PostToCommercialTracking
    Workbooks("Current Year Test.xlsx").Close SaveChanges:=True
    NextJobNumber
    End Sub
    The problem I have is that some of the cells are merged cells and it won't let me do this. I thought I might be able to get around it by using a named range but that doesn't work either.

    Is there a way of clearing the contents of merged cells as well? The cells in question are D10, I6, C32, C33

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Clear Contents of Cells

    Hi,

    You should be able to use this with merged cells
    Range("A4:A9,B1:B9,C32,C33,D1,D3,D4,D10,E8,G1:G13,I6,J1:J5,K1:K13").Value2 = vbnullstring
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,463

    Re: Clear Contents of Cells

    Ideally
    Range("D10").MergedArea.ClearContents
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

+ 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] VBA if any value in a range changes, clear contents of other cells.
    By devi1337 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-29-2017, 02:07 PM
  2. [SOLVED] Clear Contents of cells in red text!
    By Keibri in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-13-2016, 08:01 AM
  3. [SOLVED] Clear contents of 2 cells
    By laguna92651 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 03-24-2015, 02:00 AM
  4. Clear Contents of Cells On Another Worksheet
    By Tellm in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-12-2012, 05:27 PM
  5. Clear contents of cells that do not contain specific text, sort cells that do
    By feckless.lout in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2010, 01:41 AM
  6. Clear contents of cells
    By talytech in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-23-2007, 03:08 PM
  7. Clear Contents - NonBold cells
    By Steve in forum Excel General
    Replies: 3
    Last Post: 02-13-2005, 08:06 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