+ Reply to Thread
Results 1 to 3 of 3

amend code to clear contents and format in vba except row 1 in specific sheets

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    MS365
    Posts
    1,086

    amend code to clear contents and format in vba except row 1 in specific sheets

    Trying to clear data on two sheets only data 1 and data 2 but do not delete row1 which are headers on both sheet .

    as below but not working

    However the range in data 1 sheets are are from column A to O
    and data 2 are from range A to G

    But in both sheets there may be headers column which are blank

    Can Anyone assist,

    
    Sub clearme 
    
    Sheets (Array(" data1", "data2"))
    
    Rows("2:" & Rows.Count).ClearContents
    
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: amend code to clear contents and format in vba except row 1 in specific sheets

    Option Explicit
    Sub clearme()
    
    Dim ws As Worksheet
    Application.ScreenUpdating = 0
    
     For Each ws In Sheets(Array("data 1", "data 2"))
    
        With ws
        
            .Rows("2:" & .Rows.Count).ClearContents
        
        End With
    
     Next
     Application.ScreenUpdating = True
    End Sub
    Last edited by AB33; 05-06-2018 at 10:38 AM.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2406 Win 11 Home 64 Bit
    Posts
    23,951

    Re: amend code to clear contents and format in vba except row 1 in specific sheets

    try this:
    Option Explicit
    
    Sub clearShts()
        Dim w1 As Worksheet, w2 As Worksheet
        Dim lr As Long
        Set w1 = Sheets("data 1")
        Set w2 = Sheets("data 2")
        With w1
            lr = .Range("A" & Rows.Count).End(xlUp).Row
            .Range("A2:O" & lr).ClearContents
        End With
        With w2
            lr = .Range("A" & Rows.Count).End(xlUp).Row
            .Range("A2:G" & lr).ClearContents
        End With
    End Sub
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

+ 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 code to clear contents into specific columns
    By voda4444 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-08-2018, 10:11 PM
  2. [SOLVED] vba clear contents and any format in specific columns
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-29-2017, 07:35 AM
  3. Replies: 1
    Last Post: 05-27-2016, 07:45 PM
  4. [SOLVED] code to clear all contents in columns with specific text in row 2
    By xcelnovice101 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-30-2014, 08:58 AM
  5. code that will delete and clear specific contents in the workbook
    By Khanzaki in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-01-2013, 01:18 PM
  6. Code to clear contents of specific cells based upon if key exists in other sheets
    By seputus in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-31-2013, 07:25 PM
  7. VBA Code to amend specific sheet based on cell contents.
    By AndrewMac in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-09-2012, 06:03 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