+ Reply to Thread
Results 1 to 4 of 4

Trying to get this code to run FASTER

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-02-2014
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    235

    Trying to get this code to run FASTER

    Hello I am trying to get this to run a little quicker. I have many working codes MUCH LONGER that run quicker. Does anyone have any ideas. Here is the attached sheet and code.

    Sub Add_Breaks()
    
    Dim ws As Worksheet
    Dim Cell As Range
    Application.ScreenUpdating = False
    
    'For Each ws In ThisWorkbook.Sheets
    'Find 6.5 hrs mark
    
        For Each Cell In ActiveSheet.Range("E:E,J:J")
            If Not IsEmpty(Cell.Offset(, -1).Value) And IsDate(Cell.Offset(, -1).Text) Then
                Cell.Value = Cell.Offset(, -2).Value + TimeSerial(6, 30, 0)
                Cell.NumberFormat = "hh:mm"
            End If
        Next Cell
        
        Columns("E:E").Select
        Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
        Columns("J:J").Select
        Selection.NumberFormat = "[$-409]h:mm AM/PM;@"
        Cells.Select
        Selection.Replace What:="blank", Replacement:="Min Break", LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        
        'Highlight Blue
    
    With Range("E:E, J:J")
        .Font.Color = vbBlue
         .Font.Bold = True
    
    End With
        
        'Colums Autofit
        
        Columns("E:E").EntireColumn.AutoFit
        Columns("J:J").EntireColumn.AutoFit
        
        'Fix page break
        
        ActiveWindow.View = xlPageBreakPreview
        ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
        ActiveSheet.PageSetup.PrintArea = "$A$1:$J$54"
        ActiveWindow.View = xlNormalView
        
        
        Range("A2").Select
        
        Application.ScreenUpdating = True
    
    End Sub
    Attached Files Attached Files

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,357

    Re: Trying to get this code to run FASTER

    I see you are using my code in another thread...
    Not so much as a thank you or feedback regarding other thread of yours....
    https://www.excelforum.com/excel-pro...ml#post4730362
    https://www.excelforum.com/excel-pro...ml#post4731299

    Not gonna get much help from us if this is the way you handle yourself

    For starters...My bad....My code is checking every cell in columns E and J....

    Have a look at this and adapt.
    Option Explicit
    Sub Add_Breaks()
    Dim Cell As Range
    Application.ScreenUpdating = False
    With ActiveSheet
        For Each Cell In .Range("E9:E54,J9:J40")
            If Not IsEmpty(Cell.Offset(, -1).Value) And IsDate(Cell.Offset(, -1).Text) Then
                With Cell
                    .Value = .Offset(, -2).Value + TimeSerial(6, 30, 0)
                    .NumberFormat = "hh:mm AM/PM"
                End With
            End If
        Next Cell
        With .Range("E:E, J:J")
            .EntireColumn.AutoFit
            .Replace what:="blank", replacement:="Min Break", lookat:=xlPart, MatchCase:=False
            .Font.Color = vbBlue
            .Font.Bold = True
        End With
    End With
    Application.ScreenUpdating = True
    End Sub
    Last edited by sintek; 08-28-2017 at 10:12 AM.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Forum Contributor
    Join Date
    06-02-2014
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    235

    Re: Trying to get this code to run FASTER

    I am so sorry. I was posting and for some reason it wasn't actually posting. Internet issue traveling right now in San Juan. This has helped me so much and it it VERY MUCH APPRECIATED!! People like you make this site the best one out there!

    Have a great day!!

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,357

    Re: Trying to get this code to run FASTER

    Coolio....Thanks for reputation to add...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] What would be the faster better VBA code instead of the following Vlookup?
    By DMacSTB in forum Excel Programming / VBA / Macros
    Replies: 32
    Last Post: 03-31-2017, 07:23 PM
  2. vba code to run faster
    By ikhan99 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-22-2016, 02:19 AM
  3. [SOLVED] Get a code to run faster
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-04-2015, 02:07 AM
  4. optimize the Code needs to run faster
    By farrukh in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-24-2012, 10:40 AM
  5. Can code run faster
    By leem in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-16-2010, 02:52 PM
  6. I need faster code
    By mpeplow in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-16-2007, 05:30 PM
  7. Faster way to code this
    By jhahes in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-06-2006, 12:08 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1