Automate serial number using Formula, Macro and Worksheet Change event

Have you ever know you can automate the serial numbering in excel. You can achieve this in multiple way using formulas and macros. 
You want to automate the serial numbering in live when entering data just set the formulas in appropriate column in worksheet. Also you can use Worksheet_Change event to automate the serial numbering to your data. 
You have the data set already then have to use either formula or macro. Using macro you can achieve this by one click away otherwise have to do some copy and paste or fill handle options.

So many formulas are available in internet, here is the best one I ever used.
Assume having column headers in row 1,

=IF(ISBLANK(B2),"",COUNTA($B$2:B2))

Paste the above formula in cell A2 and copy down or where you need to have the serial number. When the column B is filled, the serial number will be automatically generated in column A.

When you insert a row, you need to copy the formula from the row above to the new row and the row below it. This is more irritating thing for me.

Automate serial numbering using VBA macro is given below.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MaxNo
If Not Intersect(Target, Range("B:B")) Is Nothing Then
    'More than one row is changed do not run
    If Target.Rows.Count > 1 Then Exit Sub
    'Do not run if column A in the current row has a value
    If Cells(Target.Row, 1) > 0 Then Exit Sub
    'Get the highest number in column A, then add 1 and write to the _
        current row in column A
    MaxNo = Application.WorksheetFunction.Max(Range("A:A"))
    Target.Offset(0, -1) = MaxNo + 1
End If
End Sub

Slow Excel Spreadsheet? Try this...

There are a number of things that can cause an Excel spreadsheet to slow down. Sometimes, the spreadsheet can become unusable, requiring up to a minute for saving or even the most basic of changes. Here’s a brief checklist of the most frequent causes of Excel slowdowns.

(Have a spreadsheet slow-down that I haven’t described below? Let me know in the comments!)

§  Network problems. If the spreadsheet is accessed over the network (local or remote), network issues can delay saving.

§  Macros/VBA code. If you are using macros (or Visual Basic for Applications), make sure you don’t have unnecessary macros running, or macros that perform unnecessary calculations.

§  Burdensome animation. Try disabling animation. 2003: Tools > Options > Edit > Turn off “Provide feedback with animation”. 2007: Excel Button > Excel Options > Advanced > General > Provide feedback with animation (uncheck)

§  Complicated calculations. If you’re doing lots of calculations, you can disable auto-updating of these calculations. When you do so, your spreadsheet will not update until you explicitly desire (e.g. when you hit the F9 key). To disable auto-updating in 2007, go to the Excel Button > Excel Options > Formulas > Workbook Calculation, and select “Manual”.

§  Duplicate Conditional Formatting Rules. Conditional formatting can be a processor-intensive task if your rules aren’t set up right. Manage your conditional formatting rules (2007) via the ribbon at Home > Conditional Formatting > Manage Rules… Try to consolidate similar rules into one rule (e.g. a rule for an entire column instead of a rule for each cell in the column)

§  Funky Formatting. Formatting can really slow down a spreadsheet, especially on slower machines. Try removing all formatting: Removing All Formatting.

§  Unnecessary junk. Sometimes, an Excel file can become backlogged with a bunch of unnecessary junk. Try saving as a CSV (comma-separated) file, which will strip out everything except for the data. (Make sure to not save over your original file in case this workaround does not, well, work!)

§  Unknown, but it seems to help sometimes. Install the Lookup Wizard add-in.

§  Other issues. Do you know of another issue that causes Excel to slow down? Let me know.

Cell Referencing Method - MS Excel

In Excel there are three types of cell references, these are Relative, Absolute and Mixed.
There are two types in Mixed reference, Absolute Row Relative Column Reference and Relative Row Absolute Column Reference.

Detailed usage of cell references are listed below.

Serial No	Method	What is referenced?	Type	Usage
1	A1	Single cell	Relative	A is column identifier, 1 is row identifier
2	A1:B3	Range	Relative	This references 6 cells
3	5:05	Range	Relative	All cells in row 5
4	B:B	Range	Relative	All cells in column B
5	5:07	Range	Relative	All cells in rows from 5 to 7
6	B:E	Range	Relative	All cells in columns from B to E
7	$A$1	Cell	Absolute	The cell A1. Cut-pasting formula to another location will not change the reference.
8	A$1	Cell	Mixed	The $ makes the column reference absolute, leaving the row reference relative
9	R1C1	Cell	Absolute	R1 is row 1, C1 is column 1
10	R1C1:R3C2	Range	Absolute	Same as A1:B3 as shown above
11	R	Range	Absolute	Reference to current row
12	C	Range	Absolute	Reference to current column
13	R[-2]C	Cell	Relative	Cell two rows up and in the same column
14	R[-2]C1	Cell	Mixed	Row reference is relative, column reference is absolute
15	R[2]C[3]	Cell	Relative	Cell two rows down of current row and three rows to right of current column
16	R[-1]	Range	Relative	Cells in the row above the current row
17	(defined name)	Range or Cell	Absolute	Named cells are always absolute
18	Sheet1!(reference)	Either	Either	Prefixing the sheet number and exclamation mark before any of the above references indicates the source worksheet.
19	=‘C:\[sample.xls]Sheet1’!$A$1	Cell	Absolute	Refers to an external file - a worksheet in the file and an absolute position within that worksheet.
20	=‘C:\[sample.xls]Sheet1’!octroi	Cell	Absolute	Same as above. Refers to a defined name “octroi”
21	Sheet1:Sheet3!A1:B3	Cell	Relative	Refers to all cells of A1 to B3 in all worksheets from Sheet 1 to sheet 3. “Sheet1” and “sheet3” are actual worksheet names.
22	Getpivotdata function	Range	Absolute	This is a special function which is used to refer to a range of rows/columns in a pivot table.

Fill Blank Cells - Inbuilt option in Excel

How to fill blank cells in a data range (A1:A20) with out copy paste option in simple steps?

1. Select the complete data set range (A1:A20).

2. Use CTRL + G (goto window) then, hit ALT + S and select Blanks or type 'K'.

3. Click on 'Ok' or press 'Enter'.

4. Now, all of the blank cells have been selected.

5. Now the active cell is the first blank cell in the data set.

6. Type '=' and press 'UP' arrow and press CTRL + Enter, excel will copy the respective formula to all blank cells.

7. Again select the complete data set range to copy and paste the values from formulas. The filled contents are formulas, and we need to convert the formulas to values.

8. Copy the data set and press Alt + E + S (or) Ctrl + Alt + V to enable paste special method, and select values and press enter to paste values.

9. Now the formulas are converted to values.

How to Paste MS Excel sheet data without losing its Format in Gmail?

When you copy a range of cells from Excel, you are only copying data. No HTML is copied, as Excel doesn't generate it.

The grid lines are non-printing in any case, so you need first to turn on cell borders in Excel for any grid lines you want to appear. You then need to copy the selection into a program that understands Excel formatting and can show the grid lines and colors on screen.

So select your cell range and copy-paste into a Word document. Word will generate the HTML code required to display the table, and you can then copy-paste from Word into a Gmail compose message.

It's an extra step, but works every time AND should be received correctly by any recipient.

Trim all cells values in entire worksheet using VBA

The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160.

This character is commonly used in Web pages as the HTML entity,  . By itself, the TRIM function does not remove this nonbreaking space character.

We get data from many sources like web pages, databases, software outputs and many more. Copying a webpage data to excel, will have non breakable space characters, linefeed characters and carriage return characters in any portion of a sentence. 

Using Trim function alone we can not remove that characters. It only removes leading and trailing spaces from the text.

Here is a solution to remove that unwanted characters. If data has all the non breakable space characters, linefeed characters and carriage return characters, this subroutine will converts all the above said characters to normal space character. After converting to normal space character it will execute Trim function.

Trim - Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing.

Syntax:

Function Trim(String)

The required string argument is any valid string expression. If string contains Null, Null is returned.

Sub TrimCellsEntireWorksheet() 'Assigning # of rows and columns to trim intTotalRows = Cells.SpecialCells(xlLastCell).Row intTotalCols = Cells.SpecialCells(xlLastCell).Column For rwIndex = 2 To intTotalRows 'First row is header, so here ignore the header row For colIndex = 1 To intTotalCols 'Replaces Non-Breakable Space Character (Char(160)) to Space Character(Char(32)) Do While InStr(Cells(rwIndex, colIndex).Value, Chr(160)) Cells(rwIndex, colIndex).Value = Replace(Cells(rwIndex, colIndex).Value, Chr(160), " ") Loop 'Replaces Linefeed Character (Chr(10)) to Space Character(Char(32)) Do While InStr(Cells(rwIndex, colIndex).Value, "" & Chr(10) & "") Cells(rwIndex, colIndex).Value = Replace(Cells(rwIndex, colIndex).Value, "" & Chr(10) & "", " ") Loop 'Replaces Carriage Return Character (Chr(13)) to Space Character(Char(32)) Do While InStr(Cells(rwIndex, colIndex).Value, "" & Chr(13) & "") Cells(rwIndex, colIndex).Value = Replace(Cells(rwIndex, colIndex).Value, "" & Chr(13) & "", " ") Loop 'Replaces Double Spaces " " to Single Space " " Do While InStr(Cells(rwIndex, colIndex).Value, " ") Cells(rwIndex, colIndex).Value = Replace(Cells(rwIndex, colIndex).Value, " ", " ") Loop 'Removes Leading and Trailing spaces from the text Cells(rwIndex, colIndex).Value = Trim(Cells(rwIndex, colIndex).Value) Next colIndex Next rwIndex MsgBox "Trimming of Cell values in entire worksheet is completed", , "Trim - Cell Values on entire worksheet" End Sub

Getting Internet time via Excel VBA

Using VBA to automate Internet Explorer sessions from excel spreadsheet. The GetiNetTime subroutine is used to extract the date and time from web page "http://greenwichmeantime.com/".


Sub GetiNetTime()

'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'
'
'   The GetiNetTime macro is written by Karthikeyan T.
'
'   Please Note: Original code adjusted here for setting Indian Standard Time,
'   India Standard Time (IST) = GMT+5:30
'
'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'

Dim ws
Dim http
Dim GMT_Time, NewNow, NewDate, NewTime, Hr, Mn ', Sc

'Below line wont work since clock providers changed the URL.
'Const GMTTime As String = "http://wwp.greenwichmeantime.com/time/scripts/clock-8/runner.php"


'Updated URL to fetch internet time ***
'Macro updated Date & Time: 27-Oct-12 1:07 PM

Const GMTTime As String = "http://wwp.greenwichmeantime.com/time/scripts/clock-8/runner.php?tz=gmt"

On Error Resume Next
Set http = CreateObject("Microsoft.XMLHTTP")

http.Open "GET", GMTTime & Now(), False, "", ""
http.send

GMT_Time = http.getResponseHeader("Date")
GMT_Time = Mid$(GMT_Time, 6, Len(GMT_Time) - 9)

'Set Indian Standard Time from Greenwich Mean Time.
'India Standard Time (IST) = GMT+5:30
    Hr = 5      'Hours.
    Mn = 30     'Minutes.
    'Sc = 0     'Seconds.

NewNow = DateAdd("h", Hr, GMT_Time) 'Adding 5 Hours to GMT.
NewNow = DateAdd("n", Mn, NewNow)   'Adding 30 Minutes to GMT.
'NewNow = DateAdd("s", Sc, NewNow)  'Adding 0 Seconds to GMT.

MsgBox "Current Date & Time is:  IST " & NewNow, vbOKOnly, "GetiNetTime"

'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'
'
'   If you want to insert the new date & time in excel worksheet just unquote
'   the following lines,
'
'   Sheets("Sheet1").Select
'   Range("A1").Select
'   ActiveCell.Value = NewNow
'
'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'

'Insert current date & time in cell on selected worksheet.
'Sheets("Sheet1").Select        'Select worksheet as you like
'Range("A1").Select             'Change the destination as you like
'ActiveCell.Value = NewNow

'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'
'
'   If you want to change the system time just unquote the following lines,
'
'   Set ws = CreateObject("WScript.Shell")
'   NewDate = DateValue(NewNow)
'   NewTime = Format(TimeValue(NewNow), "hh:mm:ss")
'   ws.Run "%comspec% /c time " & NewTime, 0
'   ws.Run "%comspec% /c date " & NewDate, 0
'   Set ws = Nothing
'
'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'*'

'Set ws = CreateObject("WScript.Shell")
'Split out date.
'NewDate = DateValue(NewNow)

'Split out time.
'NewTime = Format(TimeValue(NewNow), "hh:mm:ss")

'Run DOS Time command in hidden window.
'ws.Run "%comspec% /c time " & NewTime, 0

'Run DOS Date command in hidden window.
'ws.Run "%comspec% /c date " & NewDate, 0

Cleanup:
'Set ws = Nothing
Set http = Nothing

End Sub

Zip Multiple files via Excel VBA

Using this template zip the multiple files with their folder name in one path.
Ex, 
Assuming this path......  C:\Program Files\
In this folder it contain many folders.
Get directory list of folder names and give this list to this template under the name "FolderList:"
In "Your Path:" put the path above mentioned, (i.e., where the folders and files are located) and click the "GetZipfiles" button.


**Before using this template you must save as this file with the name "ConvertToZip.xls"


Click here to download ConvertToZip.xlt

LockCells with WorkSheet_Change event

Locking a cell is to prevent alteration of cell contents. 
In excel go to Format|Cells... then select Protection tab and ensure the Locked tick box is Unchecked for your destination range of cells. Then use the Tools|Protection|Protect Sheet... to protect the sheet and any unlocked cells will be locked against alteration when the worksheet change.


Code:
This is a worksheet event code, which means that it needs to be placed in the appropriate worksheet code module, not a standard code module.

Option Explicit 

Public Sub Worksheet_Change(ByVal Target As Range)
Call ApplyProtection(Range("A1:B20"))
Sheets("Sheet1").Protect Password:="password", UserInterfaceOnly:=True
End Sub

Private Sub ApplyProtection(thisRange As Range)
Dim thisCell As Range
ActiveSheet.Unprotect Password:="password"
For Each thisCell In thisRange
If IsEmpty(thisCell) Then
thisCell.Locked = False
Else
thisCell.Locked = True
End If
Next thisCell
ActiveSheet.Protect Password:="password", Contents:=True
End Sub

Need More Excel Tips?

Do you need more excel tips & tricks, 1000's of excel tips websites are available on net. Some of them I have listed here..

http://excel.tips.net
http://www.mrexcel.com
http://www.vbaexpress.com
http://www.tek-tips.com/index.cfm
http://www.databison.com
http://www.ozgrid.com
http://www.contextures.com
http://www.exceltip.com
http://www.mvps.org/dmcritchie/excel/excel.htm
http://spreadsheetpage.com
http://chandoo.org
http://www.jkp-ads.com
http://www.cpearson.com
http://www.jpsoftwaretech.com
http://www.mann-jones.com
http://jonlorenzini.com
http://www.erlandsendata.no/english
http://dailydoseofexcel.com
http://www.rondebruin.nl
http://www.toolbox.com

http://www.excelfunctions.net
http://www.excel-vba.com
http://www.excelforum.com
http://excelexperts.com
http://excelvbatutor.com
http://www.wizdoh.com

http://google.com