Best Blogging Tips and Templates,Windows Hacks: “Delete Empty Rows in Excel Sheet Using Macro” plus 1 more

Link to Blogger Tips and Hacks | Best Blogger Templates | SEO Tips and Tricks

Delete Empty Rows in Excel Sheet Using Macro

Posted: 05 Aug 2010 10:29 AM PDT

I have already published articles on different macros and use of them with almost all excel versions. Do you know How to Delete empty rows in Excel sheet using a simple Macro. I have also suggest one simple way to remove empty rows from your excel sheet.

Steps To delete empty rows between data using Sorting:

1. Select all columns containing data.
2. Click the Sort icon (either Ascending or Descending).

Steps To delete empty rows between data using Macro:

Open a Microsoft Excel Workbook which contains bland data within it.
Press the ALT+ F11 Key from keyboard to bring up the VBA editor (Double click on your excel spread sheet from left side , which contains blank data).
Paste the below given code and press F5 Key from keyboard.

' Sub Purpose: Delect all blank ROWS within the active cell's

' Used Range

'

Public Sub DeleteBlankRows()

Dim dbMaxRow As Double, dbMinRow As Double, i As Double

Dim dbMaxCol As Double

Dim rng As Range

On Error Resume Next

'only look in used area of the worksheet where active cell is

Set rng = Selection.Parent.UsedRange

'calculate area to be searched for blank rows

dbMaxRow = rng.Rows.Count '# of rows in used area

dbMinRow = rng.Cells(1, 1).Row '1st row in used area

dbMaxCol = rng.EntireColumn.Count '# of columns in used area

For i = dbMaxRow To dbMinRow Step -1

If IsError(rng.Cells(1, 1).Offset(i - 1, 0).EntireRow. _

SpecialCells(xlCellTypeBlanks).Count) Then

Else

If rng.Cells(1, 1).Offset(i - 1, 0).EntireRow. _

SpecialCells(xlCellTypeBlanks).Count = dbMaxCol Then

rng.Cells(1, 1).Offset(i - 1, 0).EntireRow.Delete

End If

End If

Next i

Set rng = Nothing

End Sub

Although I agree that AutoFilter works great for some worksheets, a large worksheet will bog down using this method. If you need to maintain the order of the rows, insert a new column, fill with numbers, sort by desired column, delete blank rows, sort by number column, delete number column.

Note: This not work when cell is containg untype characters, like NULL or formula ="" or something that similar but it is not really enpty cell. Very oftet in occure when copy from NULL from access by CTR + C and CTR + V. Export table feature work correctly and instead of NULL return really empty cell.

Pen Drive Removal Reminder - Remove Pen Drive Safely from Computer

Posted: 04 Aug 2010 07:16 PM PDT

Because of not removing Pen drive safely from computer my friend yesterday damage his pend drive and now its not detecting in any computer. Pen drive is needed for storage of data from computer so many times, i daily use pen drive to transfer data with my friends.

The use of pen drive and USB drive is increased day by day. Sometimes it happens that we forget to remove Pen Drive from computer but here is a way to solve this problem. The way is, the use Pen Drive Reminder.

Setting up Pendrive Reminder is very simple, The Pen Drive reminder utility provides three mode of operation:

Alarm mode
silent mode
countdown mode.

Based on mode of operation you select you will get a reminder as balloon popup with/without alarm. In countdown mode it pops up when the counter reaches zero seconds. It also pops up when removing the pendrive from the PC to indicate that the application ends.

Download Pendrive Reminder

Note: You must have .Net Framework 2.0 or higher installed in order to use Pendrive Reminder.

Sense of Adsense