Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

Thread: Macro To Clear Rows On Another Sheet

  1. #1
    Join Date
    31st July 2008
    Posts
    8

    Macro To Clear Rows On Another Sheet

    I have a macro that copies data into another sheet (OR) from sheet1 (EDS) but as I don't want the data added from the last row I want it to overwrite and clear any data that was there before. However I still want to keep my headings which occupy rows 1 and 2.

    This is the code I was using...
    VB:
    Sheets("OR").Select 
    Rows("2:30").Select 
    Selection.ClearContents 
    Sheets("EDS").Select 
    
    
    This is pretty inefficent but it works. I don't want to make a new macro I like keeping it inserted in my current one. However I couldn't figure out how to get it to delete the rows past row 2 (it could go to infinity but I don't think there will ever be more than 30 rows of data).

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Simply Way To Clear Or Delete Rows In Another Sheet Without Switching Sheets

    VB:
    Sheet1.Range("A1").CurrentRegion.Offset(2, 0).ClearContents 
    
    
    This line replaces the first three lines in your code.
    Change "Sheet1" to the actual sheet code name for "OR" in your workbook.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    2nd November 2005
    Location
    Tecumseh, OK
    Posts
    1,092

    Re: Simply Way To Clear Or Delete Rows In Another Sheet Without Switching Sheets

    In this method, I assumed that column A was the more contiguous data. You can be on any sheet and run this.
    VB:
    Sub SetData() 
        Dim s1 As Range, s2 As Range 
        Set s1 = Worksheets("EDS").Range("A3", _ 
        Worksheets("EDS").Range("A3", Worksheets("EDS").Cells(Rows.Count, 1).End(xlUp)).Offset(0, 1)) 
        Set s2 = Worksheets("OR").Range("A3", _ 
        Worksheets("OR").Range("A3", Worksheets("OR").Cells(Rows.Count, 1).End(xlUp)).Offset(0, 1)) 
         
        s2.ClearContents 
        s1.Copy s2 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    23rd April 2007
    Posts
    3,431

    Re: Simply Way To Clear Or Delete Rows In Another Sheet Without Switching Sheets

    It's not clear which range in "EDS" is being copied to "OR".
    If that range is sourceRange, this will clear all of "OR" except for the first two rows and copy sourceRange's data (not formats or formulas) to OR!A3.

    VB:
    Dim temp As Variant 
    With Sheet("OR") 
        temp = .Range("1:2").Value 
        .Cells.ClearContents 
        .Range("1:2").Value = temp 
        .Range("A3").Resize(sourceRange.Rows.Count, sourceRange.Columns.Count) = sourceRange.Value 
    End With 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    31st July 2008
    Posts
    8

    Re: Simply Way To Clear Or Delete Rows In Another Sheet Without Switching Sheets

    thanks for the replies guys I got it working very well now, I do appreciate it.

    At least I can check it off my list.
    Last edited by Dave Hawley; August 13th, 2008 at 10:38.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,914

    Re: Macro To Clear Rows On Another Sheet

    gwp501,

    Please take just 3 mins of your time to read: How To Get Your Question Answered...FAST! AND Anatomy Of A Good Thread Title

  7. #7
    Join Date
    31st July 2008
    Posts
    8

    Re: Macro To Clear Rows On Another Sheet

    am I looking for something specific? A rule I violated or toe I stepped on?

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,914

    Re: Macro To Clear Rows On Another Sheet

    Im guessing you havnt actually read the links.

    I pointed you two a thread named "anotomy of a good thread title" does that not give it away?

    Imagine if every one put "simply way" in their thread title when wanting help, the search engine would be useless.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Clear Duplicate Rows
    By raharr in forum EXCEL HELP
    Replies: 3
    Last Post: March 27th, 2008, 05:03
  2. Clear Sheet
    By HxF in forum EXCEL HELP
    Replies: 2
    Last Post: May 4th, 2007, 19:59
  3. Clear Numeric Rows
    By cochese in forum EXCEL HELP
    Replies: 3
    Last Post: January 25th, 2007, 04:04
  4. Macro: Print Sheet & Clear All But Heading
    By 29CDO in forum EXCEL HELP
    Replies: 9
    Last Post: November 14th, 2006, 03:02
  5. clear rows
    By ROLG in forum EXCEL HELP
    Replies: 7
    Last Post: December 3rd, 2004, 08:54

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
  •  
porno