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

 

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 formulas to values

In many cases when working with your general ledger, it becomes necessary to move data around that have formulas attached. Sometimes, those get broken in the process. In order to “lock” in those formulas values, they must be hard coded. A easy way to do this is by using the copy/paste special feature in Excel.

To do this, select the cells with the formula

  • Right click
  • Copy
  • Paste special

Just that easy!

And an even faster way of doing this is to create a macro in Visual Basic.

Sub Copy_PasteSpecial()
 Selection.Copy
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 Application.CutCopyMode = False
End Sub