Report view and data view Macro

This macro is for when you have a large spreadsheet that you maintain and update.

Usually there is a lot of data hidden when you present this as a report.

To complete your work you unhide everything, and make your edits, then go through the tedious process of hiding the data that doesnt need to be seen.

My solution is to have a pulldown in cell A1 to choose between Report view and Data view.

Start by adding a pulldown in A1.

Select A1, then click the data tab and choose data validation.

In the settings Tab, choose List, check the two boxes, and type in Data,Report in the source Input.

Now were ready to insert the code.

Right click the sheet dab and click view code.

Now paste the following into the text area.

Private Sub Worksheet_Change(ByVal Target As Range)
   
    If Range("A1").Value = "Data" Then
        Range("B:C,E:H,K:N").EntireColumn.Hidden = False
    End If

    If Range("A1").Value = "Report" Then
        Range("B:C,E:H,K:N").EntireColumn.Hidden = False
    End If

End Sub

This will hide Columns, B,C,E,F,G,H,K, AND N.

Adjust this to fit your needs.

Now we can toggle the sheet from Data View to Report view at the click of a button.
Click A1 and choose Data, and everything is unhidden.

Now click A1 again and choose Report and the columns are hidden.

I hope this makes your day a little easier.

Engineering Stationing Custom Format

This is going to be a short but useful one for those who need to work with Stations.

To keep values as numbers and display them as Stations we will need to create a custom format.
Select the cells you want to display as stations, rightclick and choose format.
Under the Number Tab choose Custom at the bottom.

Input this"##+#0.00" as the Type:

Now our raw number of 8676.70 in N3 is displayed as 86+76.70