Archive

Archive for April, 2005

True blanks

April 23rd, 2005 No comments

This about the problem of cells which appear to be blank and which are true blanks. If you check a cell using IsBlank() formula, you’d know if it’s a true blank or not.

Even if you copy paste special (values) this =”", there wouldn’t be anything in the cell and length of that cell would be zero – still it won’t be a blank. But if you press delete on that cell it would become a true blank.

Now, here is a macro to do the same thing, it would check if the cell has zero length and check if Istext is true, if both conditions are satisfied it would press a delete key on that cell.

Please check the Excel help on the functions- Len, Istext, and Isblank

To use this macro Press Alt + F11, double click the sheet you’re working on, copy and paste this code. Close VBA window.
To run it, select a range in the worksheet you want true blanks, press Alt + F8, select this macro and press Run.

‘macro to make a blank true blank
Sub make_true_blanks()
Dim c As Range
For Each c In Selection
If Len(c) = 0 And WorksheetFunction.IsText(c) Then
c.ClearContents
End If
Next
MsgBox “Done”
End Sub

Please check this discussion on mrexcel.com.

Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: Uncategorized Tags:

Find last filled column

April 8th, 2005 No comments

Hello All,

I was searching for something else at MrExcel.com but found this interesting bit of code. This code will give you the last filled column in a Sheet.

Public Sub find_last_column()
‘Found at MrExcel.com-solution given by Tom Urtis
Dim LC%
LC = Cells.Find(What:=”*”, After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
MsgBox LC
End Sub
Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: Uncategorized Tags:

Find the last filled column

April 8th, 2005 No comments

Hello All,

I was searching for something else at MrExcel.com but found this interesting bit of code. This code will give you the last filled column in a Sheet.

Public Sub find_last_column()
‘Found at MrExcel.com-solution given by Tom Urtis
Dim LC%
LC = Cells.Find(What:=”*”, After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
MsgBox LC
End Sub
Share
[CiteULike] [del.icio.us] [Digg] [Facebook] [Furl] [Google] [Reddit] [Slashdot] [StumbleUpon] [Technorati] [Twitter] [Email] More »
Powered by Bookmarkify™
Categories: Uncategorized Tags: