Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / 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...
    Code:
    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,205

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

    Code:
    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,283

    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.
    Code:
    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,691

    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.

    Code:
    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 11:38.

    Excel Video Tutorials / Excel Dashboards Reports


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

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

    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, 06:03
  2. Clear Sheet
    By HxF in forum EXCEL HELP
    Replies: 2
    Last Post: May 4th, 2007, 20:59
  3. Clear Numeric Rows
    By cochese in forum EXCEL HELP
    Replies: 3
    Last Post: January 25th, 2007, 05:04
  4. Macro: Print Sheet & Clear All But Heading
    By 29CDO in forum EXCEL HELP
    Replies: 9
    Last Post: November 14th, 2006, 04:02
  5. clear rows
    By ROLG in forum EXCEL HELP
    Replies: 7
    Last Post: December 3rd, 2004, 09: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