Excel Cut-n-Paste Slow

So, I was doing a cut-n-paste maybe 50 cells and it was taking forever. I did this multiple times in all sorts of documents and it always took forever. Though I am not certain what started the behavior, Googling it was proving less than useful until Chief93 over at superuser.com led me to the solution. It was the dang default printer. Somehow it got set to a printer that I no longer have setup on the system. My goodness, how aggravating! I reset the default to the current printer and, viola, cut-n-paste is working as expected again. Thanks Chief93, where ever you are…

Reference: https://superuser.com/questions/397032/excel-freezes-when-copying-cutting-to-paste-elsewhere

Excel VBA: Getting Rid of Merged Cells

I dislike merged cells in an Excel¬†table. Any attempt to format and prettify Excel tables annoys me. It is a pet peeve. Give me a spreadsheet and I am going to expect to be able to be able to dig into data and manipulate it. A simple way to thwart my ability to do this is to add in a bunch of merged cell into your table to make it more “presentable”. I just had such a situation and found a post over at stackoverflow that presents a simple VBA solution that will remove the merge cells and duplicate any data that was in that merged cell into he newly liberated unmerged cells.

Take a gander: http://stackoverflow.com/questions/9215022/unmerging-excel-rows-and-duplicate-data

Why can’t I seem to get rid of this blog?

I mean, i don’t do anything with it so why have it? yet when it comes time to renew the domains, i do it. When there is an update to WordPress, i apply it. And now, i have transferred the whole thing so that WordPress is managed within my provider’s cpanel.

What kind of sickness is this that i have, writing out a post no one will read?

Excel VBA: Copy a Table Column Unique Data to Another Table Column

I have updated the functionality of my earlier post to only copy over unique values, then I added a sort on the values copied over.


Option Compare Text

'Copy a column from one Table to another
Sub copyUnique()

'Delete current values prior to paste if values exist
If Application.WorksheetFunction.CountA(Range("Table2[[Column1]]")) <> 0 _
Then Range("Table2[[Column1]]").Delete

'Advanced filter for unique entries only
Range("Table1[[#All],[Column3]]").AdvancedFilter Action:=xlFilterInPlace, _
Unique:=True

'Select desired column to copy with destination option
Range("Table1[[Column3]]").Copy _
Destination:=Range("Table2[[Column1]]")

'Remove Filter After Copy
Range("Table1[[#Headers],[Column3]]").Select
ActiveSheet.ShowAllData

'Sort a table column A to Z
Worksheets("Sheet2").ListObjects("Column1").Sort.SortFields.Clear

Worksheets("Sheet2").ListObjects("Column1").Sort.SortFields.Add _
Key:=Range("Table2[[Column1]]"), SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal

With Worksheets("Sheet2").ListObjects("Column1").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

I am not certain if this is the best way to do it but the code is snappy and works for my needs.

Excel VBA: Copy a Table Column Data to Another Table Column

Update: Added missing ‘Sub End’

On Excel 2010, I created this VBA to copy data from a specific column on a specific table to another table on another spreadsheet to editing the values later without disturbing the original source data.

I am posting it here since I had to do multiple searches for the syntax.

    Sub copyColumn()
    'Delete current values prior to paste if values exist
    If Application.WorksheetFunction.CountA(Range("Table2[Column1]")) <> 0 _
    Then Range("Table2[Column1]").Delete

    'Select desired column to copy with destination option
    Range("Table1[[Column3]]").Copy _
    Destination:=Range("Table2[[Column1]]")
    Sub End

If you find this snippet of code useful, leave a comment.

One mo’gain: The road to fiscal responsibility.

Here I am again, trying to implement the annual new year’s resolution of living within a budget. Every year I fail miserably but every year I get back up on that horse and try again.

Last year I purchased “You Need a Budget” (YNAB) and was very ambitious about entering all the data I could for as far back as I could and and drowned under the weight of the volume of the numbers. I could never get caught up to current and so gave up. This year I upgraded to the latest version of YNAB and I am back at it, but now I am only going back to the first of the year so that I have a hope of getting caught up.

We have way more outgoings this year with 2 car payments and a wee little one that we feel compelled to keep surrounded by battery operated plastic toys. Even with that I am hopeful that this is the year we get it under control. Wish us luck, pray for us; we need all the help we can get!

Relationships require commitment…

I think I forget that . Maybe I just assume they will maintain themselves by virtue of past shared experiences but time has proven that friendships fade when not tended to. And to call someone a friend and to not invest that time, what does that communicate to that person you call ‘friend’ when you don’t invest the time?

To my old friends who haven’t heard from me in a while, so, a long while, know I still love you and I will try and do better.