+ Reply to Thread
Results 1 to 5 of 5

Deleting Data Using A Macro

  1. 11-26-2021, 11:33 AM #1
    Tubtech
    Tubtech is offline
    Forum Contributor
    Join Date
    11-30-2020
    Location
    Ny
    MS-Off Ver
    Office 365
    Posts
    173

    Deleting Data Using A Macro

    I have a workbook with 13 sheets, Iam using the following macro to delete all the selected data from all the sheets. Is there a way to write this macro to delete the information from just the first 12 sheets? Thanks in advance!!!

    Dim xSh As Worksheet
    Application.ScreenUpdating = False
    For Each xSh In Worksheets
    xSh.Select
    Call RunCode
    Next
    Application.ScreenUpdating = True

    Sheets("JANUARY").Select
    Range("A1").Select
    End Sub
    Sub RunCode()



    Range("F8:H11").ClearContents
    Range("F16:G16").ClearContents
    Range("F17:H17").ClearContents
    Range("F18:G18").ClearContents
    Range("F23:G26").ClearContents
    Range("F31:G33").ClearContents
    Range("F38:H42").ClearContents
    Range("F47:G49").ClearContents
    Range("P8:R13").ClearContents
    Range("P14").ClearContents
    Range("R14").ClearContents
    Range("P15:R18").ClearContents
    Range("P19").ClearContents
    Range("R19").ClearContents
    Range("P20:R28").ClearContents
    Range("P33:R35").ClearContents
    Range("P40:Q41").ClearContents
    Range("P47:Q49").ClearContents
    Range("Y11:Y14").ClearContents
    Range("X19:y20").ClearContents
    Range("AC21:AD22").ClearContents
    Range("Z28:AA32").ClearContents
    Range("Z37:AA42").ClearContents
    Range("AB37").ClearContents
    Range("Z48:AB49").ClearContents





    End Sub
    Reply With QuoteRegister To Reply
  2. 11-26-2021, 12:11 PM #2
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Deleting Data Using A Macro

    Just single sub like this:
    PHP Code: 
    Sub DeleteMutiSheets()
    myRang = "F8:H11,F16:G16,F17:H17,F18:G18,F23:G26,F31:G33,F38:H42,F47:G49,P8:R13,P14,R14,P15:R18,P19,R19,P20:R28,P33:R35,P40:Q41,P47:Q49,Y11:Y14,X19:y20,AC21:AD22,Z28:AA32,Z37:AA42,AB37,Z48:AB49"
    For i = 1 To 12
    Sheets    (i).Range(myRang).ClearContents
    Next
    End Sub 
    Quang PT
    Reply With QuoteRegister To Reply
  3. 11-26-2021, 01:16 PM #3
    Tubtech
    Tubtech is offline
    Forum Contributor
    Join Date
    11-30-2020
    Location
    Ny
    MS-Off Ver
    Office 365
    Posts
    173

    Re: Deleting Data Using A Macro

    Thanks !! That worked like a charm, one more question, is there a way to copy a value from sheet 12 to sheet 1 and let's say put in in cell a1 on sheet 1 before clearing the sheets?
    Reply With QuoteRegister To Reply
  4. 11-27-2021, 09:57 AM #4
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,466

    Re: Deleting Data Using A Macro

    Quote Originally Posted by Tubtech View Post
    Thanks !! That worked like a charm, one more question, is there a way to copy a value from sheet 12 to sheet 1 and let's say put in in cell a1 on sheet 1 before clearing the sheets?
    PHP Code: 
    Sub DeleteMutiSheets()
    sheets(12).range("F8:H11").copy ' copy your range
    sheets(1).range("A1").paste
    myRang = "F8:H11,F16:G16,F17:H17,F18:G18,F23:G26,F31:G33,F38:H42,F47:G49,P8:R13,P14,R14,P15:R18,P19,R19,P20:R28,P33:R35,P40:Q41,P47:Q49,Y11:Y14,X19:y20,AC21:AD22,Z28:AA32,Z37:AA42,AB37,Z48:AB49"
    For i = 1 To 12
    Sheets(i).Range(myRang).ClearContents
    Next
    End Sub 
    Reply With QuoteRegister To Reply
  5. 11-27-2021, 10:35 AM #5
    Tubtech
    Tubtech is offline
    Forum Contributor
    Join Date
    11-30-2020
    Location
    Ny
    MS-Off Ver
    Office 365
    Posts
    173

    Re: Deleting Data Using A Macro

    Thanks again ! ii figured it out overnight but your way works better ! I appreciate your help !!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macro for deleting duplicate data
    By dmarcrum in forum Access Programming / VBA / Macros
    Replies: 1
    Last Post: 04-29-2010, 12:05 PM
  2. Deleting data using macro
    By w_lred in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-21-2008, 06:38 PM
  3. saving, deleting data, macro
    By burgerboy2k6 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-21-2007, 10:01 PM
  4. Deleting select data with macro
    By mkingsley in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-31-2007, 06:07 PM
  5. Deleting unwanted data by macro or...
    By Leibtek in forum Excel General
    Replies: 2
    Last Post: 08-14-2006, 12:22 PM
  6. list: Deleting unwanted data by macro or...
    By Leibtek in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2006, 12:51 PM
  7. [SOLVED] Macro for deleting every second Row of Data
    By Tufail in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-27-2006, 04:25 AM
  8. Deleting excess data in a macro
    By ChuckW in forum Excel General
    Replies: 3
    Last Post: 02-24-2005, 03: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1