AdamDN

New Member
Joined
Sep 22, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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.
 

Attachments

  • Staffing sheet capture.PNG
    Staffing sheet capture.PNG
    43.6 KB · Views: 4

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 22, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
Something like this would add page breaks above the dates in column A, except for row 1:
VBA Code:
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

New Member
Joined
Sep 22, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Web

ADVERTISEMENT

Something like this would add page breaks above the dates in column A, except for row 1:
VBA Code:
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

New Member
Joined
Sep 22, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. Web
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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
 
Solution

Forum statistics

Threads
1,144,339
Messages
5,723,800
Members
422,517
Latest member
VisioExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top