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
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.