Finding the last row/column in a worksheet [Excel VBA]

I often stumble across blog posts where people are trying to discover the last cell in a column or row. Typically the example at hand is filled with non-contiguous data.  Often bloggers/commenters/arctic creatures other than polar bears will present the following solution:

Note: The following examples assumes the data range starts in Cell "A1" or Cell(1,1).

For Rows:

Set MyRng = Range("A1", Range("A65536").End(xlUp))

Or 

Set MyRng = Range(Cells(1,1), Cells(65536,1).End(xlUp))

For Columns:

Set MyRng = Range("A1", Range("IV1").End(xlToLeft))

Or 

Set MyRng = Range(Cells(1,1), Cells(1,256).End(xlToLeft))

I don't know why people hard code the last row/column values; the practice makes for code that can be difficult to edit/troubleshoot later on. Plus, it's not really backwards compatible. Sure the above examples work for Excel 2003 and earlier, but if you're working with Excel 2007 or later you potentially miss capturing a large amount of data.  (1,048,576 Rows and 16,384 Columns are available in Excel 2007 and late).

To avoid hardcoding values, and making the code compatible with Excel 03 and later, I do this:

Dim lMaxRows as Long
Dim lMaxCols as Long

lMaxRows = Rows.Count
lMaxCols = Columns.Count

So defining your range now looks like this:
For Rows:

Set MyRng = Range("A1", Range("A" & lMaxRows).End(xlUp))

Or 

Set MyRng = Range(Cells(1,1), Cells(lMaxRows,1).End(xlUp))

For Columns:

Set MyRng = Range(Cells(1,1), Cells(1,lMaxCols).End(xlToLeft))

Non-sequitur: Some people don't like using the "Cells" property to reference ranges or cells, but I say poo on that (admittedly it does make reading code more difficult). Using the Cells property makes coding loops easier. So there.

Takeaway: Avoid hardcoding - it makes debugging more difficult.

Mitigating % Complete and Duration Headaches (Project VBA)

Most of our projects are setup as Fixed Unit, non-effort driven tasks in MSP (Note: We also setup our schedules with the Updating task status updates resource status, located under the Calculation tab, checked).

When entering status for a task, we update (primarily) two fields: % Complete and/or Duration.  Changing the Duration field will affect the % Complete field in 2 ways:

1. Decreasing the Duration will increase the % Complete field;
2. Increasing the Duration will decrease the % Complete field.

Often we do not want to change % Complete, but adjust the Duration and maintain our current % Complete. We track performance using % Complete (for better or for worse, and yes I'm aware of using % Work Complete to track actual track progress...), so we are extra cautious with any negative changes in % Complete.  Why?  Well, let's just say reporting negative performance to the customer is not a positive experience.

Although we take extra steps to ensure we don't inadvertently decrease performance (though we do make the downward adjustment if appropriate, god forbid), we all have a habit of falling into the debacle that is colloquially known as "month end swirl".  Only the strong survive, and those that do are permanently scarred. 

I wrote the code below to speed up entering status and reduce schedule rework.  I have the macro set to my Ctrl-A shortcut in MSP.  To run the code select your task you want to update, and run the macro.  The code will pop-up an input box asking for the task's new duration.  The code will adjust your the task to your new duration while maintaining the previous % Complete.

Option Explicit
'---------------------------------------------------------------------------------------
' Procedure : NewDuration
' Author    : Business Bear
' Date      : 1/23/2012
' Purpose   : http://www.yunolikeexcel.com
'             This code will adjust your the task to your new duration
'             while maintaining the previous % Complete.
'             Setting the finish date to a date prior to the start date
'             will turn the task into a milestone.
'---------------------------------------------------------------------------------------
'
Sub NewDuration()

    Dim dCurStart As Date
    Dim vFinishDate As Variant
    Dim dNewFinish As Date
    Dim dCurFinish As Date
    Dim pj As Project
    Dim lNewDuration As Long
    Dim ac As Cell
    Dim lPctCompl As Long
    Dim vMsgBoxResp As Variant
   
    'Adjust conlMinutesInDay constant to reflect the number of minutes/day
    'in your schedule.  We use 8hrs/day so 8Hrs*60min = 480min/day
    Const conlMinutesInDay As Long = 480
    Const convMsgBoxButtons As Variant = vbYesNo + vbSystemModal + vbExclamation
    Const consMsgBoxTitle As String = "Warning"
    Const consDateFormat As String = "mm/dd/yyyy"
    Const consInputBxTitle As String = "New Finish Date?"
   
    'prevent code from running if no active project
    On Error GoTo EndMacro
    Set pj = ActiveProject
    On Error GoTo 0
    Set ac = ActiveCell
    Set t = ac.Task

    'prevent code from running if no task selected
    If t Is Nothing Then GoTo EndMacro
    dCurStart = t.Start
    dCurFinish = t.Finish
    lPctCompl = t.PercentComplete

    'loop until user enters a valid date
    'if user enters nothing exit procedure
    Do
        vFinishDate = InputBox(consInputBxTitle)
        If vFinishDate = vbNullString Then
            GoTo EndMacro
            Exit Do
        End If
    Loop Until IsDate(vFinishDate)

    '=== DateDifference returns minutes; divide by 480 for days
    lNewDuration = Application.DateDifference(StartDate:=dCurStart, _
            FinishDate:=vFinishDate) / conlMinutesInDay
    dNewFinish = Application.DateAdd(StartDate:=dCurStart, _
            Duration:=lNewDuration * conlMinutesInDay)
    If Format(dNewFinish, consDateFormat) <> vFinishDate Then
        If dNewFinish < vFinishDate Then
            lNewDuration = lNewDuration + 1
        ElseIf dNewFinish > dCurFinish Then
            lNewDuration = lNewDuration - 1
        End If
    End If

    '=== Set New finish date for testing against old finish date
    dNewFinish = Application.DateAdd(StartDate:=dCurStart, _
            Duration:=lNewDuration * conlMinutesInDay)
    If dCurFinish > dNewFinish Then
        vMsgBoxResp = MsgBox(Prompt:="Old finish date (" & Format(dCurFinish, _
                consDateFormat) & ") is after selected Finish Date (" & Format(dNewFinish, _
                consDateFormat) & ")." & vbCrLf & vbCrLf & "Continue?", _
                Buttons:=convMsgBoxButtons, Title:=consMsgBoxTitle)
        If vMsgBoxResp = vbYes Then
            'User wants to update even though date is in past
            GoTo Continue
        Else
            'User opted to exit
            GoTo EndMacro
        End If
    End If

Continue:
    t.Duration = lNewDuration * conlMinutesInDay
    t.PercentComplete = lPctCompl

EndMacro:
    Set pj = Nothing
    Set vFinishDate = Nothing
End Sub

Tracking/Documenting Defined Names [Excel VBA]

If you've ever developed large models in workbooks, there's a high probability you've ran into a scenario where you're working with a fairly large number of defined names.  Tracking these defined names, where they link to, and what data they represent can be cumbersome at best.  Especially if you're tracking down bugs or errors.  If you're working with hidden defined names the debugging process can become a nightmare.

To document all of the defined names (hidden or visible) in a workbook I use the procedure below.

The code creates a new Worksheet in the Active Workbook labeled "Defined_Names".  Looping through each name in the workbook, the code creates columns for the following information:

1. Name: The name of the defined Range/cell;
2. Visible: Is the name visible to the user through the Name Manager (ALT-I, N, D or Formulas>Name Manager)
3. RefersTo: The address of the linked range or cell.  Sometimes this can also be a value that's been set to a defined name.
4. Value: The value of the range the defined name references.

Option Explicit
'Code created by Business Bear for yunolikeexcel.com
Sub ListNames()
    Dim x As Long
    Dim i As Long
    Dim wb As Workbook
    Dim Name As Name
    Const WSName = "Defined_Names"

    'Turn off alerts; prevents user from having to select yes if sheet
    'to delete exists
    Application.DisplayAlerts = False
    Set wb = ActiveWorkbook
    'Dirty way to check and delete if sheet exists
    On Error Resume Next
    wb.Worksheets(WSName).Delete
    On Error GoTo 0
    wb.Worksheets.Add
    ActiveSheet.Name = WSName

    'Set columns to read as text only; 
    'prevents links to ranges and arrays displaying an error
    Range(Columns(1), Columns(4)).EntireColumn.NumberFormat = "@"
    Cells(1, 1) = "Name"
    Cells(1, 2) = "Visible"
    Cells(1, 3) = "RefersTo"
    Cells(1, 4) = "Value"

    i = 0
    'Loop through each name in workbook
    For Each Name In wb.Names
        i = i + 1
        Cells(i + 1, 1) = Names(i).NameLocal
        Cells(i + 1, 2) = Names(i).Visible
        Cells(i + 1, 3) = Names(i).RefersTo
        'Prevents error if name references value only
        On Error Resume Next
        Cells(i + 1, 4) = Names(i).RefersToRange.Value
        On Error GoTo 0
    Next Name
    Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
    'Turn on alerts again for user
    Application.DisplayAlerts = True
End Sub

15 Spreadsheet Formatting Tips

Over on PowerPivotPro, Dan lists 15 great formatting tips for spreadsheet design. 
What does design boil down too? Less is more; no 3d charts; color sparingly; and don't user Column or Row 1.

Using "Center Across" was another one listed. I guess not too many people know about this text formatting option?

Guest Post: 15 Spreadsheet Formatting Tips [via Excel Blog]

Two of My Favorite Development Tools [VBA]

Tedious Coding Tasks Be Gone

Looking to save time coding and designing all while improving the quality of your code?  Let me introduce my two favorite VBA development add-ons (each work in both Excel and Project VBA editor):

• Smart Indenter [v3.5 Mirror]
• MZTools

Note: Both tools are freeware.

What They Do

Smart Indenter: Rebuilds (or builds) indentation for procedure, modules, or an entire project.  The utility allows you to set your own indentation preferences in a dialog box, and allows you to view the sample output before applying the formatting to your project.  

If you hate formatting code (like I do) this is your solution.  Also, your co-workers will love you: indenting your code in a consistent manner helps improve readability.

MZTools: If you want to: 

• Find code faster
• Code faster
• Design faster
• Generate documentation for your projects
• Ensure the quality of your work
• Enhance your VB / VBA experience

Then this tool is for you.  Some of my favorite features include:

• Code Templates - Save procedures you use frequently and access them on the fly.
• Add Procedure/Module Headers: Insert procedure/module headers based on your preferences.  Options to include variables exist as well (e.g. inserts date procedure created, procedure author, etc. in header)
• Line Numbering - Inserts line numbers along the gutter of your code.  Great for integrating with error handling.
• Error Handler -  Add an error handler to a procedure with a customizable template. If the procedure body is not empty the tool merges the error handler with the procedure code.
• Select Case/Msgbox Assistant - 2 great features that let you quickly build Message Boxes and Case statements.

Enable Developer Tab and Create Personal Workbook (Excel)

To enable the Developer tab in Excel:

To create your personal workbook (if you have a fresh copy of Excel):

Generating a list of shortcuts unique to your version of MS Project [VBA]

I often get tired of searching Google for obscure shortcuts in Project (or Excel for that matter).  Solution?  VBA to the rescue.

Drop the code below in a module and setup a command to the macro List_Shortcut_Keys.

The code is 2 procedures:

1. List_Shortcut_Keys: Opens a new Excel workbook, then initializes the actual meat of the code.
2. ListCtrls: This is a recursive procedure that lists all the shortcuts from Project's Command Bar.  The code scans each toolbar in the commandbar and produces a list of shortcuts specific to the version of MSP you're running.   

If you have version/country specific shortcuts this will generate a list of shortcuts that will work for you.

I originally found the code at Chip Pearson's site and modified it for use in MS Project.  Given how MSP is comparatively undocumented the world of VBA, I figured this would help others who struggle finding resources for Project.

UPDATE: The code works with MSP 2003, 2007, and 2010.  Before running make sure you've selected Excel from the References dialog box.

Option Explicit
'Code modified by yunolikeexcel.com, Business Bear
'Original code found at http://www.cpearson.com/excel/ShortCuts.aspx

Sub List_Shortcut_Keys()
    Dim r As Range
    Dim xlApp As Excel.Application
    Dim wb As Workbook
    Dim ws As Worksheet

    Set xlApp = CreateObject("Excel.Application")
    Set wb = xlApp.Workbooks.Add
    Set ws = wb.ActiveSheet
    Set r = ws.Range("A3")
    ListCtrls Application.CommandBars.ActiveMenuBar, r, wb

    MsgBox "Process Complete.", vbSystemModal + vbOKOnly
    xlApp.WindowState = xlMaximized
    wb.Activate
    xlApp.Visible = True
    
    Set ws = Nothing
    Set wb = Nothing
    Set r = Nothing
    Set xlApp = Nothing

End Sub

Private Sub ListCtrls(ByRef Ctrl As Object, ByRef Rng As Range, ByRef wb As Workbook)

    Dim c As Office.CommandBarControl
    Static S As String
    Dim Pos As Integer

    If TypeOf Ctrl Is CommandBar Then
        S = "ALT"
    End If

    If Not TypeOf Ctrl Is Office.CommandBarButton Then
        For Each c In Ctrl.Controls
            Rng.Value = c.Caption
            Pos = InStr(1, c.Caption, "&")
            If Pos Then
                S = S & " " & Mid(c.Caption, Pos   1, 1)
                Rng.EntireRow.Cells(1, "H").Value = UCase(S)
            End If
            Set Rng = Rng(2, 2)
            ListCtrls c, Rng, wb
            Set Rng = Rng(1, 0)
            If Len(S) > 3 Then
                S = Left(S, Len(S) - 2)
            End If
        Next c
    End If

End Sub

Bears get angry when other analysts don't use Excel Tables

For those who don't want to read:

Use Tables in Excel.  

They will make your life easier and save you time.

Is there a large contingency of  daily, heavy-use Excel jockeys that do not know about - or purposefully do not use (even worse!) - Excel Tables (aka Excel Lists for those still running 2003) when creating datasets in Excel workbooks?

If you don't know what a Table is in Excel let me help you:

1. Open a workbook with a data table in it, preferably with the data organized in columns.
2. Select the data including the header.
3. Hit Ctrl-N, T (alternatively Insert>Table).  Make sure the "My table has headers" checkbox is checked.
4. Hit OK
5. Congrats, you just created an Excel table.

Excel Tables make life as an analyst a breeze.  Want to add data to the table?  No sweat just start typing in the row or column next to the table.  Excel will auto-expand the table dimensions to incorporate the new data.    

If you have a Pivot Table linked to a data table, just hit refresh and your PivotTable will capture new data.  No need to manually update the data range that links to the pivot table.

Pretty table

Excel tables make lookup formulas a breeze as well.  The structured references may be intimidating at first, but take the plunge - you'll enjoy it!  No longer will you have to adjust ranges in formulas after additional rows/columns have been added to the set - the Table will auto-update for you!  Boom, time Saver! 

Another added perk of tables is the auto fill down feature.  When you create a formula in the first row of the data table (that is, the first row below the header), the table will auto-fill down the formula for the entire column in the range.  

E.g. Let's say you create a formula in Cell B2, which pulls the first 2 letters of the Cell in A2.  You've already created a table from your data set called, "Table.Data", with the first column labeled, "FirstCol" (original, I know).

To create your formula you would write it as you normally would:

=Left(A1,2)

Now Excel, depending on how your options are set, should create a formula like so (for Excel 2010):

=LEFT([@FirstCol],2)

The "@" tells the formula to find the data in This Row (this row being row 2), and FirstCol tells the formula to find the data in the "FirstCol" column of the data set.

Now depending on the number of rows you have in your Table "Table.Data", the formula you just created will auto fill down.  Pretty slick.

There's also a laundry list of formatting options available as well, plus the ability to create your formatting templates for future use.

I could go on and on, but I gotta go eat a seal or 3.  Here's the file used in the above example.

Export Hanging Dependencies in MS Project [Project - VBA]

Working with Microsoft Project and looking for ways to discover poor network logic?  Below is a little procedure I wrote that scans the relationships in your project schedule in search of tasks with only Start-To-Start (SS) successors.

If the procedure finds any tasks that meet this criteria it exports information about the task to an Excel workbook for further analysis.

Why are SS-only successors bad?  They create poor network logic: Without at least one FS successor the Task will not accurately push schedule milestones and tasks further down the path.

What's an easy way to fix SS-only successor relationships?  Link the offending task to a milestone (or another task) further down the path with a Finish-To-Start (FS) relationship.

Quick notes:

1. You'll want to change the Constant consMinInDay depending on how many hours you have scheduled in your workday (in the example code consMinInDay is set to 480 minutes.  I have 8hr days X 60min = 480min/d).  
2. Despite how you may view the data in the Project, the application stores the data (or at least VBA exports the data) in minutes.
3. If your Project file is not already referenced to Excel, you'll need to set this reference in the VBA editor.  
1. To set the Excel reference: Open up the VBA Editor, Alt-F11>Tools>References>Check Microsoft Excel X.0.  The "X" will be the version number of Excel installed on your computer.  E.g. Excel 2007 = 12.0; Excel 2010 = 14.0.
4. I haven't tested the procedure in Project 2010, but running it in MSP2003 and MSP2007 should work without a problem.

Sub Hanging_Deps()
    Dim TaskDep As TaskDependency
    Dim ts As Tasks
    Dim t As Task
    Dim y As Long
    Dim xlApp As Excel.Application
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim bTaskDepSucc As Boolean

    Const consMinInDay As Long = 480
    
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.Add
    Set wb = xlApp.ActiveWorkbook
    Set ws = wb.ActiveSheet
    xlApp.Visible = True
    ws.Cells(1, 1) = "Task UID"
    ws.Cells(1, 2) = "Task ID"
    ws.Cells(1, 3) = "Task Name"
    ws.Cells(1, 4) = "Task Pct Complete"
    ws.Cells(1, 5) = "Task Succs"
    ws.Cells(1, 6) = "Task Finish"
    ws.Cells(1, 7) = "Succ UID"
    ws.Cells(1, 8) = "Succ ID"
    ws.Cells(1, 9) = "Succ Name"
    ws.Cells(1, 10) = "Succ Type"
    ws.Cells(1, 11) = "Succ Lag"
    ws.Cells(1, 12) = "Succ Pct Complete"
    ws.Cells(1, 13) = "Succ Start"
    y = 2
    
    WindowActivate TopPane:=True
    FilterApply Name:="All tasks"
    GroupApply Name:="No Group"
    SelectSheet
    Set ts = ActiveSelection.Tasks
    For Each t In ts
        If (Not t Is Nothing) And (Not t.ExternalTask) And (Not t.Summary) _
                And t.ActualFinish = "NA" And InStr(1, t.UniqueIDSuccessors, "SS") > 0 Then
            bTaskDepSucc = False
            For Each TaskDep In t.TaskDependencies
                If t.UniqueID = TaskDep.From Then
                    If TaskDep.Type = 3 Then
                        bTaskDepSucc = True
                    Else
                        bTaskDepSucc = False
                        Exit For
                    End If
                End If
            Next TaskDep
            If bTaskDepSucc Then
                For Each TaskDep In t.TaskDependencies
                    If t.UniqueID = TaskDep.From Then
                        ws.Cells(y, 1) = t.UniqueID
                        ws.Cells(y, 2) = t.id
                        ws.Cells(y, 3) = t.Name
                        ws.Cells(y, 4) = t.PercentComplete
                        ws.Cells(y, 5).NumberFormat = "@"
                        ws.Cells(y, 5) = t.UniqueIDSuccessors
                        ws.Cells(y, 6) = Format(t.Finish, "mm/dd/yyyy")
                        ws.Cells(y, 7) = TaskDep.To.UniqueID
                        ws.Cells(y, 8) = TaskDep.To.id
                        ws.Cells(y, 9) = TaskDep.To.Name
                        Select Case TaskDep.Type
                            Case 0
                                ws.Cells(y, 10) = "FF"
                            Case 1
                                ws.Cells(y, 10) = "FS"
                            Case 2
                                ws.Cells(y, 10) = "SF"
                            Case 3
                                ws.Cells(y, 10) = "SS"
                        End Select
                        ws.Cells(y, 11) = TaskDep.lag / consMinInDay
                        ws.Cells(y, 12) = TaskDep.To.PercentComplete
                        ws.Cells(y, 13) = Format(TaskDep.To.Start, "mm/dd/yyyy")
                        y = y   1
                    End If
                Next TaskDep
            End If
        End If
    Next t
End Sub

Bear out.

My Thoughts on Meetings

I Hate Page Break Previews (Part Deux)

Following through on last night's post, below is the VBA code I mentioned.  I'll assume you already know how to create procedures in your personal Excel workbook (maybe a future post will detail setting up Excel for development/advanced use...), and you know how/where to insert the code below.


Toggle Page Breaks On/Off:
Toggles page breaks on or off for the active sheet (currently selected sheet).

Sub PageBreaksOnOff()
    ActiveSheet.DisplayPageBreaks = Not ActiveSheet.DisplayPageBreaks
End Sub

Remove Page Breaks:
Removes page breaks for the active sheet (currently selected sheet).

Sub PageBreaksOff()
    ActiveSheet.DisplayPageBreaks = False
End Sub

Toggle Page Breaks On/Off for All worksheets in workbook:
Toggles page breaks on or off for ALL worksheets in active workbook.

Sub AllPageBreaksOnOff()
    Dim ws as Worksheet
    Dim wb as Workbook

    Set wb = ActiveWorkbook
    For each ws in wb.Worksheets
        ws.DisplayPageBreaks = Not ws.DisplayPageBreaks
    Next ws
    Set wb = Nothing
End Sub

Remove Page Breaks on All worksheets in workbook
Removes page breaks for ALL worksheets in active workbook.

Sub AllPageBreaksOff()
    Dim ws as Worksheet
    Dim wb as Workbook

    Set wb = ActiveWorkbook
    For each ws in wb.Worksheets
        ws.DisplayPageBreaks = False
    Next ws
    Set wb = Nothing
End Sub

Stay polar my furry friends.

I Hate Page Break Previews (Part 1)

No, seriously.  I really, really hate page break previews in Excel.

You know what page breaks are - those damn dotted lines that appear in a worksheet after you print.  Or sometimes page breaks appear randomly, at the whim of some preternatural Excel superbeing.  Call it an irrational hatred (at least that's what I chalk it up to).

Example of  a page break in Excel

To turn off page breaks In Excel 2010 is 4 to 5 clicks.  Seriously, 4 to 5 clicks to turn off these damn lines.  Microsoft, GTFO.

First up, you need to click 3 times (File>Options>Advanced), then scroll 75% of the way  down the Advanced Options dialog to Display Options for this worksheet.  Make sure you select the worksheet you want to turn off page breaks for from the dropdown box.  Finally deselect Show Page Breaks. God help you if you have a large workbook with 100+ sheets.

Moar seals, less page breaks!

VBA solution to follow in a future post...
UPDATE: VBA solutions here

Don't forget to include Finance...

I'll kick off this blog with a bit of humor.

If Scott Adams had the panel space to include a few more support functions, this strip would be spot on.

Original strip found here: Dilbert, January 18, 2010.