Posts tagged Alt F8

True blanks

Apr23
2005 Leave a Comment Written by admin

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.

Posted in Uncategorized - Tagged ,

Importing IE bookmark

Feb12
2005 2 Comments Written by admin

Steps to get your bookmarks in a database format

First way:

Open Excel>Data>Import External Data>New Web QueryPaste the address of your bookmark on your hardisk in the web query addressSelect the tableImport it to ExcelYou’ll see first column has all the addresses of the links, and after few columns the text for that hyperlinkPress Ctrl + H (Find and replace) to remove all the unessesary characters in those two columnssuch as, < ,”, etcAutoFilter on the first column for blank rows and delelte those blank rows.There you go; have two columns one with the address and one with the text.If you want to make that address as hyperlink you can use Excel funtion HYPERLINK, check help on that.

Second way;Copy all the text from the from the bookmark.htm file to cell A1Then paste this code after pressing Alt + F11

Sub giveaddress()
Application.ScreenUpdating = False
Dim k As Hyperlink
Dim i As Byte
i = 1

With ActiveSheet
For Each k In ActiveSheet.Hyperlinks
.Cells(i, “B”) = k.Address
.Range(“A” & i).Copy
.Range(“C” & i).PasteSpecial xlPasteValues
i = i + 1
Next
.Columns(“C:C”).Cut
.Columns(“A:A”).Select
ActiveSheet.Paste
End With
Application.ScreenUpdating = True
End Sub

Run this macro pressing Alt + F8 in the worksheet you’ve pasted the links and select macro giveaddress and run it.
Column B will have the addresses for the links in column A you’ll have the text.
Formula to get the site name, i.e. text of a URL excluding http
=IF(MID(C3,1,11)=”http://www.”,MID(C3,12,FIND(“/”,C3,10)-12),”")
Formula to get just the site name, i.e. including http and .com or .org
=MID(C2,1,FIND(“/”,C2,FIND(“//”,C2,1)+2))

Posted in Uncategorized - Tagged ,