Thanks in advance for any help.
I'm struggling with this one and can't seem to find a clear enough answer for me on the forums to accomplish this. I'll say that I have difficulty getting my hands around VBA so please be gentle
Here is what I have accomplished so far with the existing VBA I have written in the sheet. It basically removes blank rows and un-merges any merged cells.
'Removes blank rows
Public Sub DeleteBlankRows()
Dim r As Long
Dim c As Range
Dim Rng As Range
On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For r = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(r).EntireRow) = 0 Then
ActiveSheet.Rows(r).EntireRow.Delete
End If
Next r
'Unmerge any merged cells
With Columns("A:A")
.UnMerge
.HorizontalAlignment = xlLeft
End With
EndMacro:
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
Display More
Once this is accomplished I need to be able to replace the text "MTD Test <insert state> Total:" from each row of column A. Furthermore, I need to fill any blank cells by row in column D with the words "TOTALS:" after the previous 3 macros run.
I have successfully filled the blank cells using the following code, but I'm not sure how to implement it without setting a static range in the macro.
The sheet should only fill the first 40 rows or so with data, but I need the macros to loop through each row and make the changes then stop -- without any manual selections required.