Monday, November 13, 2017

Excel macro to clear cell contents, textboxes, pictures, and lines within a range

Clearing the cell contents using an Excel macro is very easy (and if you’re unfamiliar see my getting started with macros quick start guide). In fact, it can be done with only one line of code. To clear the contents of range B4 to C30 for example:
ActiveSheet.Range("B4:C30").ClearContents
But this macro will only delete the contents (numbers and words) inside the cells of the active sheet. What if you also want to delete any shapes within the range, including textboxes, pictures, and lines? You can use OLEFormat.Object to identify specific types of shapes and delete the ones you want. This is a better method than simply deleting every single shape within a sheet.
So the code below basically:
  1. Looks for all shapes in the active sheet
  2. Picks out the type specified, for example it will look for all the shapes that are of type TextBox
  3. Checks if the top left corner of the specified shape falls within the defined range of cells
  4. If the selected shape is within the range, then delete that shape
Sub DeleteCellContentsInARange()
'clear cell contents in the specified range
ActiveSheet.Range("B4:C30").ClearContents
'Delete TextBoxes
Dim Tbox As Shape
For Each Tbox In ActiveSheet.Shapes
If TypeName(Tbox.OLEFormat.Object) = "TextBox" Then
  If Not Intersect(Range(Tbox.TopLeftCell.Address), Range("B4:C30")) Is Nothing Then
    Tbox.Delete
End If
End If
Next Tbox
'Delete lines and arrows in Excel
Dim Tline As Shape
For Each Tline In ActiveSheet.Shapes
If TypeName(Tline.OLEFormat.Object) = "Line" Then
  If Not Intersect(Range(Tline.TopLeftCell.Address), Range("B4:C30")) Is Nothing Then
    Tline.Delete
End If
End If
Next Tline
'Delete Pictures
Dim TPic As Shape
For Each TPic In ActiveSheet.Shapes
If TypeName(TPic.OLEFormat.Object) = "Picture" Then
  If Not Intersect(Range(TPic.TopLeftCell.Address), Range("B4:C30")) Is Nothing Then
    TPic.Delete
End If
End If
Next TPic
End Sub
You could easily add more to the code and have it loop through every sheet in a workbook instead of just the active sheet. I hope that helps!

Wednesday, November 8, 2017

Ask Excel Help: How to add or count across multiple sheets in Excel

I recently received a question from a reader on our Excel Help Facebook page asking the following:
=SUM(First:Last!A1) adds up numbers across a series of sheets in the same workbook only if A1 contains a number. How do I add up the total number of times an A1 cell contains a text value x?
First of all, if you’re not familiar you are able to add values across different sheets using SUM function. Say you have 3 worksheet named Sheet1, Sheet 2, and Sheet3. You can use this formula to add up all the values in cell A1 in all three sheets:
=SUM(Sheet1:Sheet3!A1)


This special syntax is referred to as a 3D Reference.
Remember, SUM adds all the numbers in a range. You can type in the formula manually, or, after typing “=SUM(“ hold down the shift key and select the sheets, then select the cells to sum. To sum a range of numbers across sheets rather than an individual cell, the formula would look like this:
=SUM(Sheet1:Sheet3!A1:B16)
If your sheet names are numbers, add apostrophes to the formula:
=SUM(‘1:3’!A1:B16)
So our reader was asking how to count cells with text rather than adding numerical values. COUNT function will count the cells containing numbers, while COUNTA will count the number that are not empty. Same formula as before but replace SUM with COUNTA:
=COUNTA(Sheet1:Sheet3!A1)
This method works for almost any other function, too, such as AVERAGE. However, if you try using SUMIF or COUNTIF you’ll probably get a #VALUE error. Using those functions requires a bit more work using named ranges and INDIRECT, a topic for another day.

If you ever have any Excel questions or need any Excel help please feel free to send them my way! I read each and every message or comment I receive.

Wednesday, October 18, 2017

How to Increase Macro Speed and Decrease Programming Time

Here are some tips to show you how to increase your macro’s running speed as well as how to save programming time when you’re creating a macro.
First, there are a few options in Excel you can automatically turn on or off to help increase your program’s speed. You may have heard of these before, but for those who haven’t here is a quick recap:

Screen Updating: You can turn screen updating off so that Excel does not update the screen image as your code executes to speed up your macro code. No, you won't be able to see what the macro is doing, but it will run faster. Use this code:

Application.ScreenUpdating = FALSE

Be sure to restore the setting to True at the end of your macro
Excel Calculation: You can actually prevent Excel from recalculating a workbook by using the statement:
Application.Calculation = xlCalculationManual
However, when the calculation mode is xlCalculationManual, Excel doesn't update values in cells, and I’ve also heard this could erase the Clipboard Memory or cause other problems with Excel “losing focus”.  If your macro relies on an updated cell value, you must force a Calculate event, with the .Calculate method, which may be applied to either a specific range (Range("MyRange").Calculate) or to the entire workbook (Calculate).
Display Alerts: Set this property to False to suppress prompts and alert messages while a macro is running; when a message requires a response, Microsoft Excel chooses the default response.
Enable Events: This property can be set to False to prevent the application from raising any of its events. Make sure you turn the events off at the start, and back on at all exit points of the procedure.
Now, we can take these a step farther and put them all in a public subroutine to be called upon as necessary. Here’s how to turn all of the above mentioned items on and off at the start or end of a program:
Public Sub SpeedOn()
With ThisWorkbook.Application
    .ScreenUpdating = False
       .Calculation = xlCalculationManual
    .DisplayAlerts = False
    .EnableEvents = False
End With
End Sub
Public Sub SpeedOff()
With ThisWorkbook.Application
    .ScreenUpdating = True
    .Calculation = xlAutomatic
    .DisplayAlerts = True
    .EnableEvents = True
End With
End Sub
Save these two functions in notepad or somewhere you have quick access to. This way, you do not have to type out this code every time you start a new macro, simply copy and paste the pre-written code.
Another macro speed tip I want to share is, notice how I used the With...End With in the above code? If you are using several statements in a row that apply to the same object, use a WITH statement, rather than fully qualifying the object each time, to help make your VBA macro run faster.
Delete Non-essential Sheets Example
Let’s take it a step further. For example, say you have a macro to delete all sheets in an Excel file except for a few important sheets you’re using that you want to keep. How might you increase the speed of this macro?
Public Sub DeleteAllSheets()
For Each ws In ThisWorkbook.Sheets
    If Not (ws.Name = "Important Sheet 1" Or ws.Name = "Important Sheet 2" Or ws.Name = "Important Sheet 3") Then
        SpeedOn
       ws.Delete
        SpeedOff
    End If
Next ws
End Sub
Notice how I call the SpeedOn and SpeedOff functions we created earlier?

Another tip to increase macro speed: when looping through a collection it is usually faster than the FOR EACH statement rather than using the index.  For example, in the code above I used FOR EACH. It would work but would run slower to use a FOR…NEXT loop:

Dim i as Integer
For i = 1 To Worksheets.Count
        If Not (ws.Item(i).Name = "Important Sheet 1" Or ws.Name = "Important Sheet 2" Or ws.Name = "Important Sheet 3") Then
        SpeedOn
        ws.Item(i).Delete
        SpeedOff
    End If
Next i
I hope these tips help increase the speeds of your macros and decrease your coding time. If you have any other tips you’ve implemented yourself I would love to hear about them!

Saturday, August 19, 2017

2017 NFL Helmet Schedule Spreadsheet

Available to download now is the 2017 NFL helmet schedule spreadsheet. You’ll see a comprehensive breakdown of every NFL pro football team's 2017 season schedule with an image of each team’s helmet design. The NFL helmet schedule is printable too. You can save the spreadsheet as an PDF file or print it out and pin up in your cubicle at work.

 



Email required download link (to automatically update you if changes or additions are made and will update you when the next year’s schedule is ready)



No email required (no notifications):


As always, I welcome any comments or suggestions about how to fix or improve the sheet!
How can I improve this spreadsheet into something you’ll use all the time during pro-football season? Maybe in the future I should also include the option to see different styles of helmets too, like these. Or maybe the Disney-inspired team logos that this guy created. What future features would you like to see?

Tuesday, July 25, 2017

2017 College Football Helmet Schedule Spreadsheet

The 2017 college football season is a little over a month away! I still can’t believe my Buckeyes got shut out in the playoff game against Clemson. I really have no idea what to expect this season.


It’s fun looking at the 2017 college football helmet schedule to try to predict which games you think your favorite team will win or lose. This spreadsheet of the college football schedules includes every team from all conferences plus independents. Every game is listed as either home, away, or neutral site (noted at the bottom of each sheet).  A college football helmet schedule spreadsheet may be available on other websites but, to my knowledge, this is the only downloadable Excel version and, unlike some of the others, is 100% FREE!

college football helmet schedule 2017



I’ve finally done it! I’ve been creating the college football helmet schedule spreadsheet since 2011, and every year I manually update every single helmet. This took forever and unfortunately resulted in many manual entry mistakes: helmets not going with the correct team and so on.


Until now.


With a little help, the creation of this template has finally been automated. The football schedules are pulled directly from ESPN. I've even linked the helmets to the team's website on ESPN. Hopefully this automation will eliminate the manual input errors as well as decreasing creation time. In a future post I hope to show you how the macro works.




I’m happy to say this year’s sheet contains 245 different college football team helmets! It’s surprising how small the file size is when considering how many helmets there are.


Email required download link (to automatically update you if changes or additions are made and will update you when the next year’s schedule is ready)



No email required (no notifications):



As always, I welcome any comments or suggestions about how to fix or improve the sheet!

Here I am running after my son down the ramp at Ohio Stadium!


Tags: 2017 NCAA Excel Helmet Schedule

Saturday, June 10, 2017

How to Hide Gridlines in Excel - Three Ways

Gridlines in Excel are the faint, grey colored lines that appear around cells in a spreadsheet to distinguish them from one another. By default, the gridlines are displayed in worksheets using a color that is set by Excel. You can essentially turn off the gridlines, cover up the gridlines, or change their color in order to hide them.
There are at least three methods you can use to hide the gridlines in Excel.
1. The proper method is to go to View then uncheck the Gridlines box. You can hide gridlines on multiple sheets by selecting all the sheets first, then unchecking the box.



2. Change a cell’s fill color to white or another color. If you apply a fill color to cells on your worksheet, you won't be able to see or print the cell gridlines for those cells.

3. Change the gridline’s color to white. You can change the default Gridline color by going to File > Options > Advanced then scroll down to Display options for this worksheet.

To see how to do each one of these check out the video below.

Borders are not the same as gridlines in Excel

People often confuse borders and gridlines in Excel. Gridlines cannot be customized in the same manner that borders can. If you want to change the width or other attributes of the lines then you need to use a border. Gridlines are always applied to the whole worksheet or workbook, and can't be applied to specific cells or ranges. If you want to apply lines selectively around specific cells or ranges of cells, you should use borders instead of, or in addition to, gridlines.



Let me know if you found this Excel tip helpful!

Sunday, June 4, 2017

Best Excel Spreadsheet Memes - Prepare to Laugh

I've given hundreds of useful Excel tips since I started blogging eight years (!) ago in 2009. But I can't be 100% serious all the time. I've gotta have a little fun from time to time, like when I shared some of the best Excel pranks and practical jokes, not to mention all the sports related templates I produce. And who doesn't love a good joke or meme? So today I thought I'd share the best Excel spreadsheet memes I've collected over the years!

WARNING: Some of these Excel memes might make you literally laugh out loud. You've been warned.

Best Excel Spreadsheet Memes



How'd you know I like Excel? It's not like I write articles or make videos about it...


Yeah, not gonna happen, I'll make my Excel formulas as long as I want to!


Hmmm ok...


I hope not.


Mind = blown.


It's so worth it once you do you learn Excel VBA though.


Gotta have at least one grumpy cat meme.


Ever feel like this?


Me neither. Do people do that? Remove the gird lines by changing the grid lines color to white?



How many have hid non-working formulas before? Admit it if you have!


Haha, not necessarily.




There's always that one co-worker who spends more time formatting then working on the actual information within the Excel sheet.




When writing those super long formulas in Excel....



Drives me crazy.



When can you ever have enough spreadsheets?


I concur Steve!


Which Excel meme is your favorite? Please let me know by commenting below. Or if have a hilarious one that I failed to mention PLEASE send it to me!