Consulting

Results 1 to 8 of 8

Thread: How to use an Array instead of a Loop to clear contents of blank cells? - Speed Issue

  1. #1
    VBAX Newbie
    Joined
    Apr 2019
    Posts
    4
    Location

    How to use an Array instead of a Loop to clear contents of blank cells? - Speed Issue

    Hello all,

    At first of all, thanks for spending some of your time to help me. I'm trying my best to figure out this amazing world of VBA

    I have a big spreadsheet (Microsoft Office 365 ProPlus) where I use a macro to populate the cells in columns "AL:AT" with formulas and then "Copy + Paste Values" at the end to replace the formulas for the values to make the file a little bit lighter to work with.
    I'm trying to include a line of code where it finds the "blank cells" and clear their contents. After some research I ended up with this:
    Sub BlankCells1()
    
    'Works fine for a small ammount of rows
    
    
    Debug.Print "Sub: BlankCells1()"
    Dim StartTime As Double
    Dim ElapsedTime As String
    Dim count As Integer
    
    
    StartTime = Timer
    count = 0
    
    
        X = Sheet4.Range("A" & Rows.count).End(xlUp).Row
    
    
    '    For Each c In Sheet4.Range("AL3:AT" & X)    '<- Original Range
        For Each c In Sheet4.Range("AL3:AT13")       '<- Limited Range for tests
        If c.Value = "" Then
        count = count + 1
                Debug.Print count, Format((Timer - StartTime) / 86400, "hh:mm:ss"), c.Address
           
        c.ClearContents
        
        End If
        Next
        
        ElapsedTime = Format((Timer - StartTime) / 86400, "hh:mm:ss")
        Debug.Print "Time to complete: " & ElapsedTime
        
    End Sub
    The problem is that the original file will have more than 67k rows and this code will take forever to perform this task.

    I've read this entire article (https://excelmacromastery.com/excel-vba-array/) but I still not knowing how to create an array to perform this task

    Any help would be very much appreciated. Thanks!
    Attached Files Attached Files

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,192
    Location
    I'm trying to include a line of code where it finds the "blank cells" and clear their contents. After some research I ended up with this:
    If the cells are blank, why would you need to clear them?
    ---------------------------------------------------------------------------------------------------------------------

    Paul

    Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Newbie
    Joined
    Apr 2019
    Posts
    4
    Location
    Hi Paul,

    My Original File has a size of 18mb and I'm trying everything to make it lighter. I made a test by manually cleaning the contents of blank cells and the size dropped to 11mb.

  4. #4
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    470
    Location
    Hi Taubaina!
    Although don't understand your purpose, but the following code can do what you say (no array,range is enough).
    Sub BlankCells1()
    'Works fine for a small ammount of rows
    Debug.Print "Sub: BlankCells1()"
    Dim StartTime As Double
    Dim ElapsedTime As String
    Dim count As Integer, rng As Range, rngBlk As Range
    StartTime = Timer
    Set rng = Sheet4.Range("AL3:AT" & Sheet4.Range("A" & Rows.count).End(xlUp).Row)
    Set rngBlk = rng.SpecialCells(xlCellTypeBlanks)
    count = rngBlk.Cells.count
    Debug.Print count, Format((Timer - StartTime) / 86400, "hh:mm:ss")
    rngBlk.ClearContents
    ElapsedTime = Format((Timer - StartTime) / 86400, "hh:mm:ss")
    Debug.Print "Time to complete: " & ElapsedTime
        
    End Sub
    Last edited by 大灰狼1976; 06-11-2019 at 07:55 PM.

  5. #5
    VBAX Newbie
    Joined
    Apr 2019
    Posts
    4
    Location
    Hi 大灰狼!
    Thanks for the answer! I gave you the wrong therms on what I'm trying to do
    I thought that "Blank Cells" were those cells with value="" but looks like "Blank Cells" are actually "Empty".
    I'm trying to find those cells with value="" and clear their contents (make them empty):

    If c.Value = "" Then
    c.ClearContents


    I know it worked when I select "AL1" and hold CTRL Key+Press Down Arrow Key and it jumps to "AL3" instead of going all the way down to "AL1001".
    Does it make sense to you?
    Last edited by Taubaina; 06-12-2019 at 05:15 AM. Reason: The code needed a break line

  6. #6
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,958
    In the FirstLoad macro try using:
    Sheet4.Range("AL3:AR" & rg7).Value = Sheet4.Range("AL3:AR" & rg7).Value
    instead of:
    Sheet4.Range("AL3:AR" & rg7).Copy
    Sheet4.Range("AL3:AR" & rg7).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    I think you'll find you get to the same situation as after running any of the BlankCells macros on the whole range, without having to run them.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  7. #7
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,192
    Location
    I know it worked when I select "AL1" and hold CTRL Key+Press Down Arrow Key and it jumps to "AL3" instead of going all the way down to "AL1001".
    Does it make sense to you?
    In your sample xlsm that takes me to the last row on the sheet

    There don't seem to be any 0 length strings in your sample


    Try this

    Option Explicit
    
    'clear any empty, but text i.e. 0 length strings
    Sub RemoveEmptyStrings()
    
        With Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("AL:At"))
            Call .Replace(vbNullString, "###ZZZ###", LookAt:=xlWhole)
            Call .Replace("###ZZZ###", vbNullString, LookAt:=xlWhole)
            
            'clear the settings
            .Find What:=vbNullString, After:=.Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, _
                   SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False
            .Replace What:=vbNullString, Replacement:=vbNullString, ReplaceFormat:=False
        End With
    End Sub
    
    Seems funny, but you do need both .Replace lines

    Last edited by Paul_Hossler; 06-12-2019 at 08:53 AM.
    ---------------------------------------------------------------------------------------------------------------------

    Paul

    Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  8. #8
    VBAX Newbie
    Joined
    Apr 2019
    Posts
    4
    Location
    Thanks a lot p45cal! I did exactly what I was looking for! I hope I can help someone in the future like all of you helped me today.
    Thanks Paul! I'll try your code as well to understand how it works

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •