Taubaina
06-11-2019, 12:39 PM
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!
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!