Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

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

  1. #1
    Join Date
    24th August 2010
    Posts
    32

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    15th May 2011
    Location
    Tegucigalpa, Honduras
    Posts
    134

    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.

    VB:
    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.

  3. #3
    Join Date
    24th August 2010
    Posts
    32

    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!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,638

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

    try
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th August 2010
    Posts
    32

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,638

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

    Missed the spell
    That line should read as

    VB:
    On Error [B]GoTo[/B] 0 
    
    
    no space...

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    15th May 2011
    Location
    Tegucigalpa, Honduras
    Posts
    134

    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.

  8. #8
    Join Date
    24th August 2010
    Posts
    32

    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 at 07:19. Reason: can't figure out line breaks...apologies

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,638

    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
    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 2
    Last Post: May 25th, 2011, 05:40
  2. Replies: 3
    Last Post: April 26th, 2008, 13:43
  3. Clear Contents Multiple Non-Continuous Ranges
    By eughud in forum EXCEL HELP
    Replies: 4
    Last Post: November 17th, 2006, 04:52
  4. Replies: 2
    Last Post: November 4th, 2006, 09:21

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