Useful Excel Macros for eBTax.

Excel Macros to help with formatting data

This Macro is useful when extracting data from the database and you want to insert a line between groups of data. When ever there is a change int he value, a line is inserted, so if we have the following

GB VAT
GB VAT
FR VAT

then after the Macro is run we get
GB VAT
GB VAT

FR VAT

Insert lines
————————————————————————————————–
Sub InsertLine()
Dim i As Long
For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(i – 1, 1) <> Cells(i, 1) Then
Rows(i).Insert
End If
Next
End Sub

————————————————————————————————–

The following is particularly useful when you are trying to format the Financials Tax Register. If you have the Financials Tax Register in Excel, you can remove any column which has no entries with this macro.

Remove blank columns when there is a header – if no header change value to 1048576
————————————————————————————————–
Sub Delete_Empty_Columns()

first = Selection.Column
last = Selection.Columns(Selection.Columns.Count).Column

For i = last To first Step -1

If WorksheetFunction.CountBlank(ActiveSheet.Columns(i)) = 1048575 Then
Columns(i).Delete
End If

Next i

End Sub
—————————————————————————————————-