How to insert blank rows where the value changes

Insert a blank row where the value changes

I got the following question from Jonathan this week: ... Don't know if this can be done at the moment, perhaps you could advise... I have a range of cells and I want to select ALL the cells/rows in the selection where the value changes. I can select the first cell where the value changes […]

Prepare a document before sending it to others

When you send your Excel workbook to other people, sometimes you want to protect or hide certain parts. For example the formulas you used or temporary data in hidden columns. You can do this by using Excel's worksheet protection, however you should know that this protection is easily broken and not very secure. A better […]

Weird looping through cells in a range object

Below is something that I experienced a while ago and was beyond my logic. Setting a range and looping through all cells in it didn't work initially as expected. Sub WeirdCelLooping()      Dim rngCel                                       As Range      Dim rngSelection                                 As Range       Range("A1:G10").Select       ' Doesn't work correctly:      For Each rngCel In Selection.Columns(1)            ' both rngCel and the selection have now the same […]

Unhide all columns and rows

A short macro that will unhide all columns and rows in order to make all data visible again. Might be useful in some cases. Sub UnhideAllColumnsRows() ' unhide all columns and rows on the current worksheet Cells.EntireColumn.Hidden = False Cells.EntireRow.Hidden = False End Sub If you want more control on which rows and/or columns to […]