I've got 2 macros that would run one after another (A4ResetFormulaOnBlankRow, then A5ClearToRight), but the problem is the A4ResetFormulaOnBlankRow macro runs really slowly when I've got 5000+ rows of data. If there isn't many rows of data, there isn't really a problem, but I'm working with lots of data. Is there anyway to optimise the code/make it more efficient to run faster?
The picture below (can't seem to upload on mrexcel as it's too big) is what I'm trying to achieve, which my 2 current macros can do, but it takes really long when there's many rows of data. Thanks!
The picture below (can't seem to upload on mrexcel as it's too big) is what I'm trying to achieve, which my 2 current macros can do, but it takes really long when there's many rows of data. Thanks!
VBA Code:
Sub A4ResetFormulaOnBlankRow()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim denominator As Double
Dim FRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row
FRow = 3
For i = 3 To lastRow
If ws.Cells(i, 1).Value = "" Then
denominator = ws.Cells(i, 4).Value
FRow = i + 1
Else
ws.Cells(i, 15).Formula = "=" & "H" & i & "/" & "$D$" & FRow
ws.Cells(i + 1, 16).Formula = "=" & "H" & i + 1 & "/" & "$D$" & FRow + 1
ws.Cells(i + 2, 17).Formula = "=" & "H" & i + 2 & "/" & "$D$" & FRow + 2
ws.Cells(i + 3, 18).Formula = "=" & "H" & i + 3 & "/" & "$D$" & FRow + 3
ws.Cells(i + 4, 19).Formula = "=" & "H" & i + 4 & "/" & "$D$" & FRow + 4
ws.Cells(i + 5, 20).Formula = "=" & "H" & i + 5 & "/" & "$D$" & FRow + 5
ws.Cells(i + 6, 21).Formula = "=" & "H" & i + 6 & "/" & "$D$" & FRow + 6
ws.Cells(i + 7, 22).Formula = "=" & "H" & i + 7 & "/" & "$D$" & FRow + 7
ws.Cells(i + 8, 23).Formula = "=" & "H" & i + 8 & "/" & "$D$" & FRow + 8
ws.Cells(i + 9, 24).Formula = "=" & "H" & i + 9 & "/" & "$D$" & FRow + 9
ws.Cells(i + 10, 25).Formula = "=" & "H" & i + 10 & "/" & "$D$" & FRow + 10
ws.Cells(i + 11, 26).Formula = "=" & "H" & i + 11 & "/" & "$D$" & FRow + 11
ws.Cells(i + 12, 27).Formula = "=" & "H" & i + 12 & "/" & "$D$" & FRow + 12
ws.Cells(i + 13, 28).Formula = "=" & "H" & i + 13 & "/" & "$D$" & FRow + 13
End If
Next i
End Sub
Code:
Sub A5ClearToRight()
On Error Resume Next
Intersect(Columns("O").SpecialCells(xlBlanks).EntireRow, Columns("O:XFD")).ClearContents
Intersect(Columns("P").SpecialCells(xlBlanks).EntireRow, Columns("P:XFD")).ClearContents
Intersect(Columns("Q").SpecialCells(xlBlanks).EntireRow, Columns("Q:XFD")).ClearContents
Intersect(Columns("R").SpecialCells(xlBlanks).EntireRow, Columns("R:XFD")).ClearContents
Intersect(Columns("S").SpecialCells(xlBlanks).EntireRow, Columns("S:XFD")).ClearContents
Intersect(Columns("T").SpecialCells(xlBlanks).EntireRow, Columns("T:XFD")).ClearContents
Intersect(Columns("U").SpecialCells(xlBlanks).EntireRow, Columns("U:XFD")).ClearContents
Intersect(Columns("V").SpecialCells(xlBlanks).EntireRow, Columns("V:XFD")).ClearContents
Intersect(Columns("W").SpecialCells(xlBlanks).EntireRow, Columns("W:XFD")).ClearContents
Intersect(Columns("X").SpecialCells(xlBlanks).EntireRow, Columns("X:XFD")).ClearContents
Intersect(Columns("Y").SpecialCells(xlBlanks).EntireRow, Columns("Y:XFD")).ClearContents
Intersect(Columns("Z").SpecialCells(xlBlanks).EntireRow, Columns("Z:XFD")).ClearContents
Intersect(Columns("AA").SpecialCells(xlBlanks).EntireRow, Columns("AA:XFD")).ClearContents
End Sub