Learn Excel Blog

               

Ed wrote and asked about a simple macro: Do you have a macro that can add rows to a spreadsheet (at the bottom or

at cell pointer), then copy from the previous row and insert the data in the new row. And, finally delete data in certain cells.

Here is a macro that will insert a row above the cellpointer and copy the contents of the prior row. Let’s assume your data extends from A to T, and that you want to delete the contents of the new row’s cells H, K, and M

I’ll use the keyword ActiveCell to make the macro work in relation to the cell pointer.


Sub AddRowForEd()
ActiveCell.EntireRow.Insert
Cells(ActiveCell.Row - 1, 1).Resize(1, 20).Copy _
Destination:=Cells(ActiveCell.Row, 1)
Cells(ActiveCell.Row, 8).Clear ' Column H
Cells(ActiveCell.Row, 11).Clear ' Column K
Cells(ActiveCell.Row, 13).Clear ' Column M
End Sub

               

Something different today: If you are a math neophyte, you will love the add-in created by Brad Hunter, called Math Easy. Brad writes:

The product is straight forward; a “Math” button is visibly added to the Excel toolbar, performing “Addition”, “Subtraction”, “Average”, etc. No prior Excel experience is required. Hopefully beginners can use Math Easy as a stepping-stone to learning more advanced math. Visit http://www.excelmatheasy.com to download the software, view screenshots, and gain additional information.

The price is about $5 – a great investment. Check it out.

               

Joe is trying to puzzle out an error on a new macro that he just wrote. The line that assigns NextRow is returning an error that says “Object Variable or With Block Variable Not Set”

Object variables are pretty cool. In the old tips at MrExcel, I would use regular variable like these:

OrigName = ActiveSheet.Name

Worksheets(OrigName).Copy

There is a better way. You can define a variable to be any object, such as a worksheet. There are several advantages to this. It is shorter to use the object variable in place of Worksheets(OrigName). Also, as you are typing code, VBA will know that the variable is a worksheet and offer the appropriate properties and methods after you type the dot. Here is the new code:

Dim WSO as Worksheet

Set WSO = Activesheet

WSO.Copy

The trick, though, is that when you have an object variable, it has to be assigned with the Set keyword. If you fail to put the Set in the code, you will get the somewhat non-intuitive “Object Variable or With Block Variable Not Set”. Joe simply needs to precede his variable assignment with the word Set.

As I continue reviewing the tip of the week archives, you will see object variables being used more frequently.

               

Delaine asks: I need to enter a series of time values in military time. Then, I need to total them up and round to the nearest quarter hour.

To enter values in military time, select those cells and then Format > Cells. Click the Number tab. Click Time in the left listbox. Select 13:30 as the number format. You can then enter the times.

To get a total in cell A99 of all the times, you first must format cell A99 to show hours in excess of 24. Select A99, Format > Cells. Click Custom in the left listbox. In the Custom Format box type: [h]:mm

You can now enter a formula to sum the times, something like =SUM(A2:A98). Since you want to round to the nearest quarter hour, use this formula instead:

=ROUND(SUM(A2:A99)*96,0)/96

There are 96 quarter-hours in a day. Multiplying the total time by 96, rounding, and then dividing by 96 will give you the nearest quarter hour.

               

Ryan writes: I have a very long spreadsheet with many columns of numbers. I am looking for a way to make the header row “float” near the top of the data pane while scrolling down for each reference.

Ryan – set up you window so that the header row is the top row in the window. Then, put your cell pointer in column A, in the row just below the headers. From the menu, choose Window – Freeze Panes.

Anything visible in the window above and to the left of the cell pointer will remain visible as you scroll down the worksheet. Note that since the cell pointer was in column A, only the top row of headers will remain visible.

Learn Excel from MrExcel