Announcement

Collapse
No announcement yet.

macro to clear contents of 2 ranges if particular cell is blank

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

  • macro to clear contents of 2 ranges if particular cell is blank



    Hi.

    I've seen macros on clearing contents based on conditions in other cells, but cannot piece together the code given multiple ranges.

    Here's my request: Beginning at row 5 in my spreadsheet, I need a macro to clear the contents of rows in the column ranges AJI:AOJ and AOO:AOS if the cells in column AOT are blank. This macro should perform this function down 500 rows.

    Please advise if a sample spreadsheet would be helpful.

    Thanks so much for the help!

  • #2
    Re: macro to clear contents of 2 ranges if particular cell is blank

    Hi vtmft,

    Consider the following example, may work for you. If so, only adapt it to your real ranges
    and cell condition.

    Code:
    Sub ClearRanges()
    Dim MyRange As Range, UsedInRange As Integer
    
    UsedInRange = WorksheetFunction.CountA(Columns("AOT:AOT")) 'Verify if cells in AOT are blank
    
    Set MyRange = Union(Range("AJI5:AOJ500"), Range("AOO5:AOS500"))
    
        If UsedInRange = 0 Then
            MyRange.ClearContents
        End If
    End Sub
    Hope this helps,

    Regards
    Regards.

    CÚsar C.

    Comment


    • #3
      Re: macro to clear contents of 2 ranges if particular cell is blank

      Thank you so much for the help. However, when I try running it, I can't see that anything is happening. Am I supposed to modify the code to make it more specific, for instance? You mentioned that I'd need to adapt it, but based on my limited knowledge, it looks accurate (i.e., if the cell in AOT is blank, then the contents of those two ranges gets deleted.

      (I thought I had replied to this yesterday...sorry for the delay.)

      Thank you again for the assistance!

      Comment


      • #4
        Re: macro to clear contents of 2 ranges if particular cell is blank

        try
        Code:
        Sub test()
        Dim rng As Range, r As Range
        On Error Resume Next
        set rng = Range("AOT5:AOT500").SpecialCells(4)
        On Error Go To 0
        If Not rng Is Nothing Then
            For Each r In rng
                r.EntireRow.Range("AJI1:AOJ1,AOO1:AOS1").ClearContents
            Next
        End If
        End Sub

        Comment


        • #5
          Re: macro to clear contents of 2 ranges if particular cell is blank

          Unfortunately, I get a syntax error which highlights "On Error Go To 0."

          I'll try to play around with it, but my knowledge is pretty limited. For example, I deleted "On Error Go To 0" and the macro ran, but it only worked partially (i.e., it cleared the contents of all applicable rows except for one).

          Thanks again for any help you can offer.

          Comment


          • #6
            Re: macro to clear contents of 2 ranges if particular cell is blank

            Missed the spell
            That line should read as

            Code:
            On Error GoTo 0
            no space...

            Comment


            • #7
              Re: macro to clear contents of 2 ranges if particular cell is blank

              Hi vtmft,

              May you upload a sample file, the macro I sent you it works for me as I understand that need
              to work from your first post.

              Doubt:
              When any cell in column AOT is blank or when a specific cell in AOT is blank, clear contents of ranges "AJI5:AOJ500" and "AOO5:AOS500"?
              Regards.

              CÚsar C.

              Comment


              • #8
                Re: macro to clear contents of 2 ranges if particular cell is blank

                (my apologies - I'm not sure what happened with this reply....I'm not sure how to introduce line breaks into this text. Again, my apologies.) Alright. Fantastic! I had inadvertently messed with the cell values (ha...to think I can figure this stuff out!). The macro as you have it written gets the job done. Here are two comments and a request. 1. Regarding the clarification, the macro should clear the contents of the particular row (i.e., within AJI to AOJ, etc.) if that row's cell in column AOT is blank. So it is according to each row. As far as I know, the code you've produced does this successfully, since it leaves data in the row whose AOT cell contains text. 2. On my machine, at least, the macro runs rather slowly. On the document in question, there will be at most around 4-5,000 rows of data and it looks like the macro is clearing data at just over 1 row per second. I know that my machine is a bit slow when it comes to Excel docs as I'm running Office 2010 on Windows 7 via VMware on a Macbook Pro, but this seems quite slow. The macro has been running for 30 minutes and it is still not complete on my spreadsheet with 3600 rows of data. Is there any way to speed it up? I like your code for its simplicity, but is there any way to speed it up? I did find an alternative code (if you'd like to see it, I can try posting it here - I attempted to include it here but apparently I can't get the hang of how to use code tags). 3. Finally, and perhaps most importantly, I now need the macro to look in several other columns to determine if it should clear the contents of this range. I.e., In addition to column AOT, the macro should check to see if cells in columns WA, ASQ, AYI, BFB, and BPN are all blank. If all six of those cells (again, this should check row by row) are blank, then the data in the range AJI:AOJ should be cleared (I no longer need two separate ranges' data cleared, just the one). I tried adding these to your code but, obviously, failed. Any help would be greatly appreciated! Thanks again for your time and patience.
                Last edited by vtmft; July 4th, 2011, 06:19. Reason: can't figure out line breaks...apologies

                Comment


                • #9


                  Re: macro to clear contents of 2 ranges if particular cell is blank

                  Let's see if this code is better in speed

                  You must test the code with the back up
                  Code:
                  Sub test()
                  Dim x As String, y As String, e, i As Long
                  x = Range("AOT5:AOT45000").Address
                  For Each e In Array(Array(945, 1076), Array(1081, 1085))
                      For i = e(0) To e(1)
                          With Range(Cells(5, i), Cells(500, i))
                              y = .Address
                              .Value = Evaluate("if(" & x & "="""",""""," & y & ")")
                          End With
                      Next
                  Next
                  End Sub

                  Comment

                  Working...
                  X