VBA for Merge and Center Dates based on condition met.

[AdamDN]

I do a LOT of scheduling people at my job. Kronos spits a nasty excel sheet with 1000's of rows and about 40 columns. I've got created enough macros to where it fine tunes it to what you see below BUT I would like to do a few more things to really make it so I can print these schedules for staff and not spend hours a week on them. List of things I seem to be incapable of doing:

1) Creating a macro that helps find a date and then merges+centers A:I in that one row. Here is what I am playing with but each time I run it it merges and centers vertically as well as horizontally.
Sub DateMergeCenter()

With Sheets(1)
For Each rCell In .Range(.Cells(1, "A"), .Cells(.Rows.Count, "A").End(xlUp))
If IsDate(rCell) Then
If rCell >= K2 Then
Lastrow = Cells(Rows.Count, "D").End(xlUp).Row
For i = 1 To Lastrow
If rCell >= K2 Then
.Range("A" & i & ":I" & i).Merge
End If
End If
Next rCell
End With

End Sub

2) Next I would like to insert a page break above each date so only one day prints per page instead of going in and adding hundreds of page breaks.

Any help on this would be so absolutely amazing and appreciated. As you can probably tell: I've only been using VBA for about 3 days. Lots of learning to do.

 

[Joe4]

Welcome to the Board!

Regarding your first question, I would recommend NOT merging cells. Merged cells are nasty things that cause all sort of issues for Excel and VBA, and should really be avoided whenever possible.
From your code, it appears that you were merging columns across single rows. You can accomplish the same visual effect without all the issues that merged cells cause by opting to use the "Center Across Selection" formatting option instead.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Regarding your second question, are those dates in the gray rows actually dates in Excel, or are they text strings?
If you are not sure, one easy way to tell is to go to one of those cells and try to change the cell format to "General".
If it changes it to a number, they are entered as valid dates. If it has no effect, then it is entered as Text.

 

[AdamDN]

Welcome to the Board!

Regarding your first question, I would recommend NOT merging cells. Merged cells are nasty things that cause all sort of issues for Excel and VBA, and should really be avoided whenever possible.
From your code, it appears that you were merging columns across single rows. You can accomplish the same visual effect without all the issues that merged cells cause by opting to use the "Center Across Selection" formatting option instead.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Regarding your second question, are those dates in the gray rows actually dates in Excel, or are they text strings?
If you are not sure, one easy way to tell is to go to one of those cells and try to change the cell format to "General".
If it changes it to a number, they are entered as valid dates. If it has no effect, then it is entered as Text.

The dates are formatted as long dates, built into cells within column A.

Welcome to the Board!

Regarding your first question, I would recommend NOT merging cells. Merged cells are nasty things that cause all sort of issues for Excel and VBA, and should really be avoided whenever possible.
From your code, it appears that you were merging columns across single rows. You can accomplish the same visual effect without all the issues that merged cells cause by opting to use the "Center Across Selection" formatting option instead.
See: Tom’s Tutorials For Excel: Using Center Across Selection Instead of Merging Cells – Tom Urtis

Regarding your second question, are those dates in the gray rows actually dates in Excel, or are they text strings?
If you are not sure, one easy way to tell is to go to one of those cells and try to change the cell format to "General".
If it changes it to a number, they are entered as valid dates. If it has no effect, then it is entered as Text.

Also, do you think there is a way to create a VBA that will first filter by date and if date is satisfied it will grab A:I and center horizontally.

 

[Joe4]

Something like this would add page breaks above the dates in column A, except for row 1:

Sub MyFormatting()

    Dim lr As Long
    Dim r As Long
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows starting on row 2
    For r = 2 To lr
'       Check to see if a date is in column A
        If IsDate(Cells(r, "A")) Then
'           Insert page breaks
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(r, "A")
        End If
    Next r
    
End Sub

I am afraid I am not quite clear on your other question/comment.

 

[AdamDN]

Something like this would add page breaks above the dates in column A, except for row 1:

Sub MyFormatting()

    Dim lr As Long
    Dim r As Long
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all rows starting on row 2
    For r = 2 To lr
'       Check to see if a date is in column A
        If IsDate(Cells(r, "A")) Then
'           Insert page breaks
            ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(r, "A")
        End If
    Next r
   
End Sub

I am afraid I am not quite clear on your other question/comment.

WOW, that works! Thank you sir.

It was in reference to our initial inquiry, merge and centering (which I now know to never do). I am trying to take a whack at building a macro that will now search column A for a date. (Since column A has dates and job position and I just want to center job position), then if it finds a date centering that date in the Grey section (A:F). Does that help?

Thanks for all your time today.

 

[AdamDN]

WOW, that works! Thank you sir.

It was in reference to our initial inquiry, merge and centering (which I now know to never do). I am trying to take a whack at building a macro that will now search column A for a date. (Since column A has dates and job position and I just want to center job position), then if it finds a date centering that date in the Grey section (A:F). Does that help?

Thanks for all your time today.

I should add, they'll be in different positions every time I generate a new schedule otherwise I would record a macro.

 

[Joe4]

The Macro Recorder is great for getting snippets of code. Though it is literal, you can often use it as a "building block" for dynamic code.

Note that the code I gave you is already looking for dates in column A, so we should be able to combine both things in there, like this:

Sub MyFormatting()

    Dim lr As Long
    Dim r As Long
    
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
    
'   Loop through all rows starting on row 1
    For r = 2 To lr
'       Check to see if a date is in column A
        If IsDate(Cells(r, "A")) Then
'           Center columns A - F
            Range("A" & r & ":F" & r).HorizontalAlignment = xlCenterAcrossSelection
'           Insert page breaks if row is after row 1
            If r > 1 Then ActiveWindow.SelectedSheets.HPageBreaks.Add Before:=Cells(r, "A")
        End If
    Next r
    
End Sub