Tag: Excel

Categories
Microsoft Office Technology

Excel – Split one cell to multiple rows

There are times when you have an excel sheet which has multiple lines of text in a single cell, which has been split using a carriage return. NOTE: I’m not talking about text which has wrapped due to the size or formatting of the cell.

If you need to separate the contents of this single cell, into one row per line then this is the macro for you. NOTE: This macro will insert rows into your sheet so you may have to “fix” the layout afterwards. Save your sheet before you run this just in case.

The first function processes the current cell – use this if you only have one cell which you want to split.

Public Sub SplitCellToRows()
    arrValues = Split(ActiveCell.Value, vbLf)
    For i = UBound(arrValues) To LBound(arrValues) Step -1
        'MsgBox i & " " & arrValues(i)
        If i > 0 Then
            ActiveCell.Offset(1).Resize(1).EntireRow.Insert (1)
        End If
        ActiveCell.Offset(Sgn(i)).Value = arrValues(i)
    Next i
End Sub

If you have multiple cells which you want to split out then there is a wrapper macro which will call this multiple times.

Public Sub SplitCellToRows_Multiple()
    For Each cell In ActiveCell.CurrentRegion.Cells
        cell.Select
        SplitCellToRows
    Next cell
End Sub

To use, simply highlight one or more cells and then run the appropriate macro.

Categories
Microsoft Office Technology

Excel – Unfilter all sheets

There are times when you are using an Excel workbook and you simply want to search for some content but on one of the tables on one of the sheets the table which contains the data has been filtered. You can spend more time looking for and then removing the filter than you do in running the actual search.

This macro will remove all filters from your current workbook.

Continue reading “Excel – Unfilter all sheets”
Categories
Microsoft Office Productivity

Outlook Calendar To Excel

I need to produce a weekly report detailing what work I have carried out. I diligently record this in my Outlook calendar. My weekly report needs to be submitted in Microsoft Excel.

The following macro will pull the entries for the last seven days from my calendar and store it in Excel format.

Continue reading “Outlook Calendar To Excel”
Categories
Microsoft Office Productivity Technology

Track Changes In Excel

One of the main reasons that I use Microsoft Excel is to keep lists to share with multiple people. Microsoft Word has great versioning and colabarative editing capabilities, Excel not so much. The main problem I have is knowing when a particular row has been changed and who changed it.

The following macro implements this.

Continue reading “Track Changes In Excel”
Categories
Microsoft Office Technology

Filter Cells In Excel With Strike Through Formatting

I have just finished reviewing an excel spreadsheet where a number of the rows were formatted with strikethough : like this.

The formatting was valid and the rows have to stay there – but I have no need to review those rows. In excel you can filter rows, by contents or even by colour but not by format.

Enter a VBA user function:

Continue reading “Filter Cells In Excel With Strike Through Formatting”
Categories
Microsoft Office Productivity Technology

Uselfull Excel Macro – Make Contents Page

I commonly have large excel spreadsheets with many tabs or worksheets within the same workbook.

I use the following Microsoft excel macro to generate a contents page.

NOTE: I’m assuming the “Contents” worksheet will be the first and the list will be generated from A2 downwards.

Private Sub CommandButton1_Click()
    For i = 2 To Sheets.Count
        Range("a" & i) = Sheets(i).Name
        ActiveSheet.Hyperlinks.Add Anchor:=Range("a" & i), Address:="", SubAddress:="'" & Sheets(i).Name & "'!A1", TextToDisplay:=Sheets(i).Name
    Next i
    Columns("A:A").EntireColumn.AutoFit
End Sub
Categories
Microsoft Office Windows Work

Multiple Excel Text Boxes With Common Content

I have just had to place a textbox object onto 17 worksheets within the same excel workbook, each text box containing the same commentary text. Type it once and then copy the text box and paste, job done.
Of course as soon as I have finished it I spot my typo and realise I will have to do it all again.
Only NOW do I slow down and think (OK I Google it) of the proper way to do it.
When you create a text box, rather than typing the text into the text box as I have been doing for years, with the text box selected you can type into the formula bar. In my case I put in the formula “=Metadata!B2”. This means that the textbox will display whatever I type into cell B2 on my “Metadata” worksheet.
Now I edit the textboxes I have created to reference the formula and in the future if the text needs to change, I change it in Metadata!B2 and the new text appears in all of the textboxes that refer to it!
NOTE: The text displayed in a formula driven text box is limited to 256 characters.

Categories
Microsoft Office Windows Work

Microsoft Office–VBA Editor Keeps Opening

For weeks now I have had the mild annoyance that the VBA code editor for various Excel spreadsheet’s appear to be opening at “randomâ€?. I finally tracked it down to me unlocking my PC. If I have excel running then lock my PC, when I unlock it, the VBA code editor appears.

I’m using Office 2003 SP3 on XP Pro SP3.

Turns out that there is a VERY easy fix – don’t maximise the VBA code editor.
If the editor is maximised, it appears on unlock, if it is not maximised it does not appear automatically on unlock.

Categories
Microsoft Office

Changing Excel Cell Values To Uppercase

A nice quick tip, I had lots of cell values in a mixture of cases that should have been uppercase.

Create a macro:

sub MakeUpper()

for each myCell in Selection

  myCell.Value = UCase(myCell.value)

next myCell

end sub

Add a menu item to your toolbar and then assign this macro to it.

Select the cells you want to upper case then press the button!

Categories
Maxima Information Group Microsoft Office Work

MS Excel – Convert Percentage To Number

I have spent most of today throwing data around in MS Excel.

One of the things that I have had to do is to convert percentages into real numbers.

In excel percentages are stored as fractions, for example 50% is stored as 0.5, 75% as 0.75 etc.

I am trying to chart these figures in Business Objects Xcelsius but the y-axis labels are just showing 0 to 0 hence why i need to convert my numbers.

I have just found a really easy way to do this.

  • In the target cells, enter the value 100 in each cell.
  • Copy the source cells
  • Select “Paste Special” using the options “Values” and “Multiply”

This takes the source value 0.75 multiplies it by 100 and stores the result 75 !

I have used pase special – values often but I have never seen the need for the multiply option until now !

You may want to check out some of my other Excel related posts here.