Find and Delete All Blank Rows in an Excel Sheet

Quick tip that I use fairly often when weeding out certain rows in reports:

  1. Select the column containing blanks.

  2. Press F5 ("Go to") on your keyboard.

  3. Click Special.

  4. Check Blanks.

  5. Click OK.

  6. Right click a blank cell in the appropriate column.

  7. Click Delete>Entire row.

  8. Click OK.

Comments

Post a Comment

Popular posts from this blog

Continuously Monitor Folder for New Files with PowerShell as a WindowsService

Recently encountered an old-school application that used a cobbled-together external service to generate reports and deliver to end-users in PDF format. Unfortunately, this service didn't have any error handling other than dropping the intended source content into a flat file in an error directory on the local disk. No monitoring, no notification, just fail and pretend it never happened. As these reports are critical to end users, I needed a way to monitor this directory for any new files and notify someone so that the report could be manually re-generated. After some initial Googling, I decided a quick PowerShell script would be the easiest method to continuously monitor the directory and fire off a notification when a new file is found. I'm admittedly an extreme PowerShell newbie, so I'm sure there are better, cleaner ways to do this, but this works nicely and is stable enough for now until my legacy system is retired in a few months. The below PowerShell script will inst...
Read more

Unprotect All Excel Sheets for All Workbooks in a Directory

This Excel macro came in very handy when I had a set of workbooks in a folder (and subfolders) that all needed unprotected before running another macro to combine all of the workbooks into a single file. Be sure to update the bolded variables below.  Source . Const cStartFolder = " C:\Documents and Settings\UserName\My Documents\Spreadsheets " 'no slash at end Const cFileFilter = " *.xlsx " Const cPassword = " CleverPassword " 'use empty quotes if blank Sub UnprotectAllWorksheets() Dim i As Long, j As Long, arr() As String, wkb As Workbook, wks As Worksheet ExtractFolder cStartFolder, arr() On Error Resume Next j = -1: j = UBound(arr) On Error GoTo 0 For i = 0 To j Set wkb = Workbooks.Open(arr(i), False) For Each wks In wkb.Worksheets wks.Unprotect cPassword Next wkb.Save wkb.Close Next End Sub Sub ExtractFolder(Folder As String, arr() As String) Dim i As Long, objF...
Read more

ChromeOS Flow by Hexxeh Install to HDD

I finally decided to pull out the netbook again and check out the ChromeOS release of Flow by Hexxeh . I got the image downloaded, untar'd and imaged to the thumbdrive. Now comes the part when I want to actually install it to the solid-state disk in my Asus EeePC, right? Well, online sources said to just run /usr/sbin/chromeos-install and it would be taken care of. It kept erroring with "Attempt to install to a removable device: /dev/sda". Of course, this is my thumbdrive so that would make sense. Since it took me some digging to figure this one out, I thought I'd throw up the solution here to make it a little more visible on the Interwebs. Instead, run this command: /usr/sbin/chromeos-install /dev/sdb This should work if your internal device is at least 8gb. Unfortunately for me, mine is 4gb and my source media is 2gb. Instead, I ended up just copying the entire live image from my thumbdrive to the hard drive. Whatever works, right? Run this command instead of the ab...
Read more