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

[Taubaina]

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!

[Paul_Hossler]

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?

[Taubaina]

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.

[大灰狼1976]

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

[Taubaina]

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?

[p45cal]

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.

[Paul_Hossler]

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

[Taubaina]

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