Announcement

Collapse
No announcement yet.

Macro To Clear Rows On Another Sheet

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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).

  • #2
    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

    Comment


    • #3
      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

      Comment


      • #4
        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

        Comment


        • #5
          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, 10:38.

          Comment


          • #6
            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
            Reafidy

            Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

            Comment


            • #7
              Re: Macro To Clear Rows On Another Sheet

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

              Comment


              • #8


                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.
                Reafidy

                Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                Comment

                Working...
                X