Excel : Fill up Blank Rows in a column

If you your columns have blank rows & data. And you want to fill up blank rows with cells having data.
e.g cell A2 contain 1 and cell A10 contain 2. Rows are blank from A3:A9. try below code which will fill up value if cell A2 till cell A9

Sub Mtest()
Dim i As Long
Dim Temp As String

For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
If Trim((Cells(i, 1)) = "") Then
Cells(i, 1) = Temp
Else
Temp = Cells(i, 1)
End If
Next
End Sub

Excel : Browse Internet in excel worksheet

You can browse or surf internet in excel worksheet......:)

without opening or clicking internet explorer you can surf web pages in excel worksheet.
you can chat on facebook access emails with in excel worksheet.

you can do work in one worksheet & simultaneously browse the web on another worksheet
Steps.
To make the following code work, you'll need to include the "Microsoft Internet Controls" library in your VBA references first.Go to your Visual Basic Editor, Menu Tools -> References select  "Microsoft Internet Controls

1. click on developer tab
2. Go to Control->Insert->ActiveX-Control->Click on More Control (Hammer Icon)

      3. you will get More Control window

      4. Select Microsoft Web Browser & Click ok 

    

       5.Go to Control->Insert->ActiveX-Control->Command Button

       6.Right Click on command button

       7.Select View code

       8.copy below code

       9. Exit design mode

Private Sub CommandButton1_Click()

'change you web site url here

WebBrowser1.Navigate "http://www.google.co.in/"

' Wait till the Browser is loaded

Do

DoEvents

Loop Until WebBrowser1.ReadyState = READYSTATE_COMPLETE

End Sub

Excel : Format Number Lakh Separator

Excel allows only thousand's separator not "lakh" separator
There is difference between Excel Numbering format between Indian & Western system
Both systems agree till 10,000. After that the 'problems' start.

10^5 = Hundred Thousand = 1 Lakh
10^6 = Million = 10 Lakh
10^7 = 10 Million = 100 Lakh = 1 Crore
10^8 = 100 million = 10 Crore
10^9 = 1 Billion = 100 Crore = 1 Arrab
10^10 = 10 Billion = 10 Arrab
10^11 = 100 Billion = 100 Arrab = 1 Kharrab
10^12 = 1 Trillion = 10 Kharrab
In India numerical value for lakh is 100,000 not 100,000 (one hundred thousands)


Here's a useful Excel tip for users in India and other places that use number formats like 100,00,00,000 instead of 1,000,000,000:

With 2 decimals:

[>99999]##\,##\,##0.00;[<-99999.99]-##\,##\,##0.00;##,##0.00

Without decimals:

[>99999]##\,##\,##0;[<-99999.99]-##\,##\,##0;##,##0

For Lakhs and crores (+ve)

[>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;##,##0.00

For Lakhs and crores (-ve)

[<-9999999](##\,##\,##\,##0.00);[<-99999](##\,##\,##0.00);##,##0.00

Or

For numbers upto 100 Crores or 1 Arab..

[>9999999]##\,##\,##\,###;[>99999]#\,##\,###;###,###


There is an easier way to change the global settings from Settings > Control Panel > Regional Settings > Numbers, but that will affect all your Excel sheets and the change will be visible only on your system and not on your files which you send to others.


with Vb Code (By : Graham aka parry )


Private Sub Worksheet_Change(ByVal Target As Range)
     '//Written by parry
    Dim c
    If Target.Cells.Count = 1 Then
        Select Case Target.Value
        Case Is >= 1000000000
            Target.Cells.NumberFormat = "##"",""00"",""00"",""00"",""000.00"
        Case Is >= 10000000
            Target.Cells.NumberFormat = "##"",""00"",""00"",""000.00"
        Case Is >= 100000
            Target.Cells.NumberFormat = "##"",""00"",""000.00"
        Case Else
            Target.Cells.NumberFormat = "##,###.00"
        End Select
    Else
        For Each c In Target
            Select Case c.Value
            Case Is >= 1000000000
                c.NumberFormat = "##"",""00"",""00"",""00"",""000.00"
            Case Is >= 10000000
                c.NumberFormat = "##"",""00"",""00"",""000.00"
            Case Is >= 100000
                c.NumberFormat = "##"",""00"",""000.00"
            Case Else
                c.NumberFormat = "##,###.00"
            End Select
        Next c
    End If
End Sub

Excel : Custom Cell Formatting

Right Click on Cell-->Select Format Cells-->Select Custom


"#" is used as a placeholder for permitted digits/Digit placeholder. This code does not display extra zeros.
"?" indicates either a digit/Digit placeholder. This code leaves a space for insignificant zeros but does not display them
, (comma) Thousands separator. A comma followed by a placeholder scales the number by a thousand.

You can design your own custom number formats

Excel : Display Credit Card Number

To Display Credit Card Number in Cell you have to format the cell
if last digit of your credit card number is Zero then you can format cell as 0000 0000 0000 0000
if last digit is not zero than assuming your credit card number is in cell A2 Try below formula
'=LEFT(A2,4)&" "&MID(A2,5,4)&" "&MID(A2,9,4)&" "&RIGHT(A2,4)

Microsoft Excel retains 15 significant digits. To display all 15 digits, you must use a number format (custom or built-in) other than General. The General number format displays up to 11 numeric characters, with the decimal point counting as a numeric character. Therefore, if the number contains a decimal point, Excel can display up to 10 significant digits, but if the number does not contain a decimal point, Excel can display up to 11 significant digits Adding more numbers to the left of the decimal point causes the number to appear in exponential notation.

Create Expiry for Excel Workbook

Some times you have to set expiry date for your excel workbook. which will restrict use of workbook after certain period of time.  

  Steps:

1. Press "Alt+F11" to launch the Visual Basic Editor from Excel
2. Right-click "ThisWorkbook" in the "Project Explorer" window. Select "View Code" from the list of available options
3. copy below code

Private Sub Workbook_Open()

'If Sheets("sheet1").Range("a1").Value < Date Then ' you can set value on worksheet

If doe < Date Then 'You can define date as doe

MsgBox "This tool has expired, please contact Admin.", vbCritical, "Expired"

ActiveWorkbook.Close False

End If

End Sub

MS PowerPoint- Play Video with Timer

This requirement came from my one of my MBA college friend.
show the people that they have never seen\ heard before during presentation
You can play video in Excel and PowerPoint as well.
Many option's are available to get it done. If you use youtube video URL then you require internet connectivity during the presentation. If you use windows media player option you have to save the video on particular drive & carry the video during the presentation.

Best way i found is to convert your video in swf format & insert it in PowerPoint or Excel.
Software Link : http://www.dvdvideosoft.com/products/
download presentation : http://www.mediafire.com/file/n43cez8fic2itrc/Powerpoint-Play Video with Timer.ppt

Steps :

1. Go to developer tab
2. On the control group click on insert
3. Then click on more control (icon like hammer)
4. You will get More control box ; Select shockwave flash object & click ok
5. You will get cross sign (+) drag it on Powerpoint slide & select the area for video
6. Right click on square
7. Then select properties
8. Enter the full path to the Shockwave file (SWF) in the Movie property text-box. (e.g "C:\Users\MAHESH\Desktop\Download\abc.swf")
9. Set the EmbedMovie property to True
10. Run the slide show

For Countdown timer :