RowDown

The RowDown does not need to check whether the active row is the first row in the worksheet because it moved the row down. When executed, selects the row with the active cell, cuts it, and inserts it one row down.

The second part of the code checks whether the first cell of the row is a numbered list. If it is, the numbers are swapped to reflect the new row positions.

Sub RowDown()

' This function selects the row of the active cell and moves it down by one.

    Selection.EntireRow.Select

    Selection.EntireRow.Cut

    Selection.Offset(2, 0).Insert Shift:=xlDown

    Selection.Offset(1, 0).Select

' Check if column one of the row contains a numbered list. If it does change the numbers to reflect the new positions of the rows.

If IsNumeric(Cells(ActiveCell.Row, 1).Value) And (Cells(ActiveCell.Row, 1).Value) <> "" Then

Cells(ActiveCell.Row, 1).Value = Cells(ActiveCell.Row, 1).Value + 1

Cells(ActiveCell.Row - 1, 1).Value = Cells(ActiveCell.Row - 1, 1).Value - 1

End If

End Sub

To use these macros, you can enter them in an individual worksheet, or you can enter them in the Personal Macro Workbook which will make them globally available to all worksheets on your PC.

To insert the macros in your Personal Macro Workbook, follow the steps below:

1. Open the worksheet.
2. On the Developer tab, in the Code group, click the Visual Basic command button to open the Visual Basic Editor.
3. In the Project Explorer window of the Visual Basic Editor, expand the tree for the Personal Address Workbook. This should be named in the format, VBAProject(PERSONAL.XLSB).
4. Expand the project tree, and under the Modules folder, create a new module to store your macros (see the chapter on Creating New Modules for how to create new modules in Project Explorer).
5. Once you’ve created the new module, double-click on it to open its code window on the right pane and type in your code.
6. When done, click File > Save on the menu bar (or click on the Save button on the Visual Basic Editor toolbar).
7. On the Visual Basic Editor toolbar, click the View Microsoft Excel button (or press Alt+F11) to switch to Excel.
8. In Excel, create two command buttons on the Ribbon to run the RowUp and RowDown macros you’ve just created. The macros do not have to be local to your current Excel workbook in order for you to assign them to buttons on the Ribbon. See chapter 3 for how to assign macros to command buttons on the Ribbon.

With that, you’re set. You can now click on the buttons to move items up or down your list.

To enter the code in an individual worksheet, do the following:

1. Open the worksheet.
2. On the Developer tab, in the Code group, click the Visual Basic command button to open the Visual Basic Editor.
3. In the Project Explorer window of the Visual Basic Editor, expand the tree for the current workbook. This should be named in the format, VBAProject(YourWorkbookName).
4. Double-click on the object named ThisWorkbook to open its code window.
5. Type in your code in the code window on the right pane.
6. When done, click File > Save on the menu bar (or click on the Save button on the Visual Basic Editor toolbar).
7. On the Visual Basic Editor toolbar, click the View Microsoft Excel button (or press Alt+F11) to switch to Excel.
8. In Excel, create two command buttons on the Ribbon to run the RowUp and RowDown macros. See chapter 3 for how to assign macros to command buttons on the Ribbon.
9. Save your Excel workbook file as an Excel Macro-Enabled Workbook (.xlsm file) because it now contains macros. Note that the macros will be discarded if you save the file as a regular Excel file (.xlsx).

That’s it! You can now select any cell within the list and use the command buttons to move rows up or down your list.

Unhide all rows and columns

This macro unhides all the hidden rows and columns. A macro like this is useful when there are several hidden rows and columns in your worksheet and you're not sure which ones are hidden. Running this macro ensures that there is no hidden data as you work on the worksheet.

Sub UnhideRC()

    ' Unhide all hidden rows and columns in the current worksheet.

    Columns.EntireColumn.Hidden = False

    Rows.EntireRow.Hidden = False

End Sub

Unmerge all merge cells

If you are working in a worksheet with a lot of merge cells it can be confusing to format cells or identify what cells formulas are referencing. You can unmerge the cells manually, but it could also be very time-consuming to identify and unmerge all the merged cells in a large worksheet. A macro like this automatically identifies and unmerges all merged cells.

Sub UnmergeAll()

   ActiveSheet.Cells.UnMerge

End Sub

Convert all formulas to values

If you have a large data set with lots of formulas that you want to convert to ordinary values, identifying the formula cells and converting them manually can be very time-consuming. A macro like this will do it automatically for all formula cells in your worksheet. This is equivalent to copying cells with formulas and pasting them as values only.

Sub ConvertFormulasToValues()

'Convert all formulas in the current worksheet to values.

    With ActiveSheet.UsedRange

        .Value = .Value

    End With   

End Sub

Create branded rows in your data list

To create banded rows in a data list or table, you highlight alternate rows in the data list. The colour separation usually makes it easier to read the data. You can use the macro below to automatically create alternate highlighting in a selected range. I’ve used vbCyan here, but other VBA colour constants you can use include vbBlack, vbRed, vbGreen, vbYellow, vbBlue, and vbMagenta.

Sub CreateBandedRows()

    'Create banded rows in the selected range by highlighting alternate rows.

    Dim objRange As Range

    Dim objRow As Range

    Set objRange = Selection

    For Each objRow In objRange.Rows

       If objRow.Row Mod 2 > 0 Then

          objRow.Interior.Color = vbCyan

       End If

    Next objRow    

End Sub

Change text to uppercase

There are built-in formulas in Excel that enable you to convert strings to uppercase, lowercase, or proper case. However, you would need to insert the formula in your data, and this can be tedious if you have a lot of values to change. You can use the code below to convert values in the selected range to uppercase. The UCase function is used here, but you can also use the lowercase function LCase if you want to convert values to lowercase instead.

Sub ConvertToUpperCase()

    ' Convert all words in the selected range to uppercase.

    Dim objRange As Range

    For Each objRange In Selection.Cells

        If objRange.HasFormula = False Then

            objRange.Value = UCase(objRange.Value)

        End If

    Next objRange

End Sub

You can use the code below to convert the selected range to Proper case (which capitalizes the first letter of each sentence).

Sub ConvertToProperCase()

    ' Capitalize the first letter of all words in the selected range.

    Dim objRange As Range

    For Each objRange In Selection.Cells

        If objRange.HasFormula = False Then

            objRange.Value = WorksheetFunction.Proper(objRange.Value)

        End If

    Next objRange

End Sub

Highlight blank cells

Occasionally, you may have a large worksheet with lots of data, and you want to be able to identify and mark the blank cells. This could enable you to quickly identify gaps in the data or missing values in the data set. The macro below automatically highlights all blank cells in the data list. The macro only works if there is some data present in the column, so it will not highlight cells in rows and columns with no data at all.

Sub HighlightBlankCells()

    'Highlight all blank cells within the selected data.

    Dim objRange As Range    

    Set objRange = Selection    

    objRange.SpecialCells(xlCellTypeBlanks).Interior.Color = vbYellow    

End Sub

Sort data using multiple columns

This macro will be useful to you if you often get a data list that you need to quickly sort using a number of columns. You can customize the macro for your data list. You can change which columns are used for the sort by changing the cell references in the code. In this example, A1 and B1 are used as the sort fields. You can also change the sort order by using xlDescending instead of xlAscending.

Sub MultiColumnSort()

    ' Sorts the data in cells A1:D16 in ascending order using columns A1 and B1.

    With ActiveSheet.Sort

        .SortFields.Add Key:=Range("A1"), Order:=xlAscending

        .SortFields.Add Key:=Range("B1"), Order:=xlAscending

        .SetRange Range("A1:D10")

        .Header = xlYes

        .Apply

    End With    

End Sub