Delete or hide blank rows with VB macros

These macros can be helpful in data clean-up.  An example might be exporting large amounts of general ledger data with tons of extra lines in between the good data.

The key is to select the area that has the data.  This might be column that has GL account amounts.  The lines you want to delete do not have anything in this area.

In this screenshot, we want to delete the rows that are red in column N.

Here is the macro name “BlanksRows_Delete”- used for deleting:

Sub BlanksRows_Delete()
Selection.SpecialCells(xlCellTypeBlanks).Select
 Selection.EntireRow.Delete 
End Sub

In the above example, select the N column, then run the macro.

If you only want to hide the rows, you can use this macro “BlanksRows_Hide”

Sub BlanksRows_Hide()
Selection.SpecialCells(xlCellTypeBlanks).Select
 Selection.EntireRow.Hidden = True 
End Sub

 

Formula for getting the last day of the month

…a quick tip that you may find useful in your applications…

I have spreadsheets that produce journal entries that I can then import into the general ledger. I reuse these spreadsheets every month.  I always make the effective date the last day of the month. To make it easy on myself (why not), I just enter the first day of the month in the cell above, and use this handy formula:

Example:

Cell A1:
6/1/2017
Cell A2:
=(DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))-1
Results of formula
6/30/2017
How it works

It takes the date (6/1/2017), then makes it the first day of the next month (7/1/2017), then subtracts one day, making it 6/30/2017.

Creating Debits and Credits from Net

You might have a trial balance that just has net amounts that you need in separate debit/credit columns. See example:

In Col E we will create our Debits, and Col F our Credits. We’ll leave Col D blank to give some separation.

In Cell E2 insert the formula:

=IF($C2<0,0,$C2)

In Cell F2:

=IF($C2<0,-$C2,0)

Now, copy the formulas down for the whole TB. The results should  look like:

I have added a simple formula in Cell F9 to make sure that my debits and credits equal the original list. It does!

 

More Macros

Not really related to general ledgers, trial balances, or even financial statements, but I still find these macros handy on a daily basis. I’ll post a few more later…

This one gives you today’s date (hard coded).

Sub ItsToday()
 ActiveCell.FormulaR1C1 = "=TODAY()"
 Selection.Copy
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 Application.CutCopyMode = False
End Sub

Fast Format Calibri size 8 (I use for trial balance details, and other data)

Sub Format_Calibri_8()
 With Selection.Font
 .Name = "Calibri"
 .Size = 8
 .Strikethrough = False
 .Superscript = False
 .Subscript = False
 .OutlineFont = False
 .Shadow = False
 .Underline = xlUnderlineStyleNone
 .ThemeColor = xlThemeColorLight1
 .TintAndShade = 0
 .ThemeFont = xlThemeFontMinor
 End With
End Sub

Fast Format Calibri size 10 (I use for presentable results)

Sub Format_Calibri_10()
 With Selection.Font
 .Name = "Calibri"
 .Size = 10
 .Strikethrough = False
 .Superscript = False
 .Subscript = False
 .OutlineFont = False
 .Shadow = False
 .Underline = xlUnderlineStyleNone
 .ThemeColor = xlThemeColorLight1
 .TintAndShade = 0
 .ThemeFont = xlThemeFontMinor
 End With
End Sub

Easy Macro for Formatting Currency

In Excel, the default Currency ($ USD) setting is with the dollar sign and two decimal places.  As a personal preference, I remove the dollar sign and take the decimals down to 0 places.  Here is a quick macro in Visual Basic that will do both at once:

Sub Format_Currency_NoCents()
 Selection.NumberFormat = "#,##0"
End Sub

Alternatively, I have one that removes the $, and has two decimal places:

Sub Format_Currency()
 Selection.NumberFormat = "#,##0.00"
End Sub

Feel free to paste these into VB.

Converting Scanned .pdf Documents to Excel

You may have run across times when you have a sheet of paper with lots of numbers that you need in a spreadsheet.

The long way- hand type the numbers into a new spreadsheet. yuck!

The better way- scan the document (.pdf) and use OCR (optical character recognition) to pull out the numbers.

In my example, I have a Statement of Revenues, Expenses and Changes in Net Position from an audit.  But, I only a paper copy.

Step 1- Scan the  document. I use a copier with scanning capabilities.

Ideally, you want a copy that’s clean and straight. This will be important for the next step.

Step 2- Use an OCR conversion website. I prefer (Free Online OCR – convert scanned PDF and images to Word, JPEG to Word).

Step 3- Download the results in Excel:

Almost perfect! There is a little clean-up at this point, but it’s in pretty good shape.  Look at row 19. There are two lines in one cell. That’s easy to fix and beats hand typing.

Note that I took a .pdf and converted to Excel. But there are many other combinations. Your original could be .jpeg (for ex.), and your output may be Word, Excel, or even .txt.