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

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.

Upgraded to WordPress 2.7

i like the upgraded admin interface, it looks a lot cleaner now. functional, nothing much has changed. i do need to re-add links to fellow blogs, that seems to have disappeared somewhere along the way.

On a lighter note.

I have a Kodak EasyShare V570!

V570

It’s a loaner for the part-time gig and, man, I am digging it. It has a 23mm lens that allows you to take some really interesting pics, really widening your field of view on close shots. In a world’s first, the camera also has a second lens for that works in the 39mm-117mm range!

What else? Well, it is a 5 mega pixel camera with a quality Schneider lens, large 2.5 inch LCD and a lithium-ion battery, stuffed into the small package of 2x4x.8 inches. It shoots VGA video (640×480) at 30 frames/sec. and you can use the zoom while you shoot. There is also this new blur detection technology that helps make sure that your pictures come out looking good and this awesome panoramic option where you take three pictures and the camera stitches them together to give you a 180 degree view.

So do I sound like a Kodak marketing rep yet? Even so, I am really excited about this camera and look forward to demoing it at the retails, ’cause I think that this camera can stand toe-to-toe with any camera out there. When I put it up against my roomy Nick’s new Lumix DMC-FX8K it’s an even heat in a lot of ways. The Lumix has optical image stabilization a bigger Litium-Ion battery and a clear protective cover for the LCD monitor, features that my Kodak just doesn’t have. The only one of those options that I am jealous of though is the bigger battery. The LCD cover is a good idea but not necessary and I am not convinced that OIS is going to help that much with image quality, perhaps in low light, but that’s it. Still, it is forward thinking to have those options available and I appreciate that.

The Lumix doesn’t have that 23mm capability though, nor blur detection technology and doesn’t let you use the zoom in video mode so that are all plusses in the Kodak camp.

It really comes down to preference. I think the 23mm lens is dang unique and gives Kodak an edge on any of the compact digitals out there. Feel free to disagree.

I never took pictures much before this job with Kodak but I imagine that I will be turning into a shutterbug now. Look for me on Flickr soon:)

Now you can know if it’s raining at my house.

Look to your immediate right and notice that the weather for Minneapolis is displayed. Isn’t that great?! Now you can know what type of weather I am experiencing, day or night. This satisfies a need that was out there amongst my many non-Minnesotan readers… (clear’s throat) Ok, that’s it then.

Deep-fried and squeaky clean

I was reading about how to make my own bio-diesel at this wonderful website and was blown away when they said that one of the by-products of the process was soap. You take used deep-fryer grease and mix it with some chemicals and, viola, soap and diesel fuel.

What a payback! Not only do I get the fuel I need to run my vehicle but I also receive for my effort an unlimited supply of hygeine products.

I could even sell the soap and recoup on my expenses. I already have a name for it…

Fried Chicken Soap

Let me know if your interested, I may be taking pre-orders soon!

Cutting-edge training for little ol’ me.

Yay, starting next week I get to go to three weeks of training on our company’s hightech phone system. Check out this picture of the person I am replacing hard at work on it.

I just hope that I can figure it all out… it just looks so intimidating.