Camera: Takes a linked picture of the current selection and pastes it in a new location. The picture is linked by a formula that refers to the copied cells, so it is updated when the copied cells change. Click the Camera button to take the picture, and then click where you want to paste the upper-left corner of the selection.
The Camera Tool in Excel is a very useful tool when it comes to capturing data in Excel that you want to display in another area in Excel or another application. Using copy/paste doesn't always give you the results you want so try the camera tool.
First, add the camera tool to a toolbar in Excel :
1. Choose View...Toolbars, Customize
2. Click on the Commands tab
3. Select Tools from the Categories list and then scroll down the commands list until you located the Camera tool
4. Click and drag the camera onto any toolbar
5. Click on close
Using the Camera Tool
To make a linked Camera picture of a range:
* Select a range
* Press Camera Icon, a + cursor appears
* Move to destination location and click once - the selected range is copied
* Move, manipulate your live picture as desired
Since your picture is linked, any changes in the original cells will appear in your picture.
You can move it anywhere you want, it floats over the rows/columns.
FOR Excel 2007
Click office Button-->Excel Options -->Customize-->Commands not in ribbon--> Camera
Regards,
Praveen KVC
31 December 2009
Thursday, December 31, 2009
Wednesday, December 30, 2009
How to Recover a Corrupt Excel File
Microsoft has some great tips for recovering data from Excel files that won't open or are otherwise corrupt although they are sometimes a little difficult to try. See the tips section for them. You may want to try them first, or a combination with the list below.
Steps
1.Close down Excel, reopen it and try to open the file again.
2.If this doesn't work, restart the computer, open up Excel and try to open the file again.
3.If this doesn't work, delete the contents of your c:\windows\temp directory then reboot. Try again.
4.If this doesn't work, open Excel in Safe Mode. This will disable VBA and Add-ins. Click on Start then Run then enter (including the quotes and the /s extension): "C:\Program Files\Microsoft Office\Office\excel.exe" /s ["C:\Program Files\Microsoft Office\Office10\excel.exe" /s (for Excel 2002, Office XP, click cancel if the MS Office Installer starts, Excel in safe mode will still start)]. Try to open the file.
5.If this doesn't work, be sure to scan the file for viruses. Make sure your macro heuristics scanning option is turned on. Ask your IT administrator how to do this.
6.If you don't find a virus, find the file in Explorer, right click on it and rename it, replacing the .xls extension with the .doc extension. Try to open it as Word document.
7.If this doesn't work, try to determine if the file is unrecoverable on the disk. Open the file in Explorer and try to copy it to another location. If you can copy the file to another location skip to step 9, if not the actual sectors on the disk may be corrupted. If the data is valuable enough to you, there are several labs that can recover files off damaged disks, you can find a lab here: http://www.disasterrecoverygroup.com/। There are other such services too)। Damaged hard disk file recovery is not for the faint at heart! If you would like to try there are a bunch of free tools starting here:http://www।s2services.com/baddisk.htm. There are several programs you can buy, one for as little as 40$. A good list is here:http://www.webattack.com/shareware/system/swdatarecovery.shtml. The File Recovery program at the top of the list appears to be the best. For recovery from a bad floppy disk download or use a freeware program to recover the file from the floppy. Some freeware for this purpose can be found here:http://www.simtel.net/pub/pd/60018.html and here: http://www.s2services.com/cdzipandfloppyreapir.htm.
8.Try to open any damaged disk recovered file immediately, miracles do happen.
9.If the recovered file won't open or you could copy the file to another location, try to open the file in a more recent version of Excel. As the version numbers increase, their ability to recover corrupt files increases. If this doesn't work or such a version of Excel is not available, see if some other spreadsheet application is and try to open the file in that program.
10.A hopefully exhaustive list of Office Suites is available from Google at: http://directory.google.com/Top/Computers/Software/Office_Suites/. but here is one too: Microsoft Works Spreadsheet, Lotus 123, Quattro Pro, Star Office Calc, ThinkFree Office Calc, Ability Office Spreadsheet, Gobe Productive Spreadsheet, EI Office's Spreadsheet Module, Xoom Office Calc, Open Office Calc (part of the freeware Open Office Suite - similar to Star Office, free business use!) - available at: http://www.openoffice.org/dev_docs/source/1.0.1/index.html, 602 Tab (part of the freeware 602Pro PC Suite 2001, also free for business use!), available at: http://www.software602.com/products/pcs/download.html, Easy Spreadsheet (part of the freeware Easy Office 2001 - $39.95 for business use), available at: http://www.e-press.com/demo_downloads.html, standalone spreadsheets: http://directory.google.com/Top/Computers/Software/Spreadsheets.
11.If Microsoft can't help you (see the tips section) and you've exhausted all the free methods above for damaged disk recovered or copiable files there are a number of excellent commercial applications which will recover your files almost immediately. Try the free demos first (see "External Links" below).
12.If you are adventurous, go into Explorer, locate the file again, and rename it with a .txt extension instead of an .xls extension. Try opening the file, and if Windows says it can't open it Notepad but will open it in WordPad, agree. Be sure to turn on word wrapping in either program, in Notepad it's under the Format menu; in WordPad it's under the View menu, choose Options, choose the Text Tab, and choose Wrap to window. Next look for where your data stops, and is followed by a bunch of spacer characters they are little squares. These actually begin to tell Excel where cells, columns and rows are. When you find your last bit of data, delete the rest of the file. After this rename it back to an .xls extension, try opening the file in the oldest version of a spreadsheet you can find, or one of the freeware spreadsheets programs mentioned in Step 9. You may be able to recover your data in some semblance of a spreadsheet.
Regards,
Praveen KVC
December 30 2009
Steps
1.Close down Excel, reopen it and try to open the file again.
2.If this doesn't work, restart the computer, open up Excel and try to open the file again.
3.If this doesn't work, delete the contents of your c:\windows\temp directory then reboot. Try again.
4.If this doesn't work, open Excel in Safe Mode. This will disable VBA and Add-ins. Click on Start then Run then enter (including the quotes and the /s extension): "C:\Program Files\Microsoft Office\Office\excel.exe" /s ["C:\Program Files\Microsoft Office\Office10\excel.exe" /s (for Excel 2002, Office XP, click cancel if the MS Office Installer starts, Excel in safe mode will still start)]. Try to open the file.
5.If this doesn't work, be sure to scan the file for viruses. Make sure your macro heuristics scanning option is turned on. Ask your IT administrator how to do this.
6.If you don't find a virus, find the file in Explorer, right click on it and rename it, replacing the .xls extension with the .doc extension. Try to open it as Word document.
7.If this doesn't work, try to determine if the file is unrecoverable on the disk. Open the file in Explorer and try to copy it to another location. If you can copy the file to another location skip to step 9, if not the actual sectors on the disk may be corrupted. If the data is valuable enough to you, there are several labs that can recover files off damaged disks, you can find a lab here: http://www.disasterrecoverygroup.com/। There are other such services too)। Damaged hard disk file recovery is not for the faint at heart! If you would like to try there are a bunch of free tools starting here:http://www।s2services.com/baddisk.htm. There are several programs you can buy, one for as little as 40$. A good list is here:http://www.webattack.com/shareware/system/swdatarecovery.shtml. The File Recovery program at the top of the list appears to be the best. For recovery from a bad floppy disk download or use a freeware program to recover the file from the floppy. Some freeware for this purpose can be found here:http://www.simtel.net/pub/pd/60018.html and here: http://www.s2services.com/cdzipandfloppyreapir.htm.
8.Try to open any damaged disk recovered file immediately, miracles do happen.
9.If the recovered file won't open or you could copy the file to another location, try to open the file in a more recent version of Excel. As the version numbers increase, their ability to recover corrupt files increases. If this doesn't work or such a version of Excel is not available, see if some other spreadsheet application is and try to open the file in that program.
10.A hopefully exhaustive list of Office Suites is available from Google at: http://directory.google.com/Top/Computers/Software/Office_Suites/. but here is one too: Microsoft Works Spreadsheet, Lotus 123, Quattro Pro, Star Office Calc, ThinkFree Office Calc, Ability Office Spreadsheet, Gobe Productive Spreadsheet, EI Office's Spreadsheet Module, Xoom Office Calc, Open Office Calc (part of the freeware Open Office Suite - similar to Star Office, free business use!) - available at: http://www.openoffice.org/dev_docs/source/1.0.1/index.html, 602 Tab (part of the freeware 602Pro PC Suite 2001, also free for business use!), available at: http://www.software602.com/products/pcs/download.html, Easy Spreadsheet (part of the freeware Easy Office 2001 - $39.95 for business use), available at: http://www.e-press.com/demo_downloads.html, standalone spreadsheets: http://directory.google.com/Top/Computers/Software/Spreadsheets.
11.If Microsoft can't help you (see the tips section) and you've exhausted all the free methods above for damaged disk recovered or copiable files there are a number of excellent commercial applications which will recover your files almost immediately. Try the free demos first (see "External Links" below).
12.If you are adventurous, go into Explorer, locate the file again, and rename it with a .txt extension instead of an .xls extension. Try opening the file, and if Windows says it can't open it Notepad but will open it in WordPad, agree. Be sure to turn on word wrapping in either program, in Notepad it's under the Format menu; in WordPad it's under the View menu, choose Options, choose the Text Tab, and choose Wrap to window. Next look for where your data stops, and is followed by a bunch of spacer characters they are little squares. These actually begin to tell Excel where cells, columns and rows are. When you find your last bit of data, delete the rest of the file. After this rename it back to an .xls extension, try opening the file in the oldest version of a spreadsheet you can find, or one of the freeware spreadsheets programs mentioned in Step 9. You may be able to recover your data in some semblance of a spreadsheet.
Regards,
Praveen KVC
December 30 2009
Hide the Ribbon in Office 2007
Press Control + F1 and ribbon disappears and press again to reappear ribbon. You can also have it disappear so it will come back with a single click on any tab name. To do this, double click a tab name and the ribbon disappears. Single click a tab name and it reappears - click again on the tab or in the document, and it disappears. Repeat until you're tired of the magic! Double click or Control + F1 to go back to how it was.
Regards,
Praveen KVC
December 29 2009
Regards,
Praveen KVC
December 29 2009
Sunday, December 27, 2009
Find First or Last Populated Column in a sheet
Code:
Option Explicit
Sub Test_xlFirstLastCols()
' Target Application: MS Excel
' Demonstration: display first and last non-blank columns in the active sheet
' and one target sheet
Dim SheetName As String
'
' display sheet name and results from xlFindFirstCol and xlFindLastCol
' for the active sheet. Since activesheet is assumed if procs are called
' without a passed arguement, use that method here
'
MsgBox "Worksheet name = " & ActiveSheet.Name & vbCrLf & _
"First non-blank col = " & xlFirstCol() & vbCrLf & _
"Last non-blank col = " & xlLastCol(), vbInformation, _
"Active Sheet Demonstration"
'
' display sheet name and results from xlFindFirstCol and xlFindLastCol
' for "Sheet4". Since this is not the active sheet, the sheet must
' be defined via the passed arguement.
'
SheetName = "Sheet4"
MsgBox "Worksheet name = " & SheetName & vbCrLf & _
"First non-blank col = " & xlFirstCol(SheetName) & vbCrLf & _
"Last non-blank col = " & xlLastCol(SheetName), vbInformation, _
"Passed Sheet Name Demonstration"
End Sub
Function xlFirstCol(Optional WorksheetName As String) As Long
' finds the first populated col in a worksheet
If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlFirstCol = .Cells.Find("*", .Cells(.Cells.Count), xlFormulas, _
xlWhole, xlByColumns, xlNext).Column
If Err <> 0 Then xlFirstCol = 0
End With
End Function
Function xlLastCol(Optional WorksheetName As String) As Long
' finds the last populated col in a worksheet
If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlLastCol = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious).Column
If Err <> 0 Then xlLastCol = 0
End With
End Function
How to use:
Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
In the left side window, select the target spreadsheet [it will likely be called VBAProject(name.xls) where name is the name of the spreadsheet]
Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.
Test the code:
In the attached example, there are 5 sheets with varying amounts of data.
Select any of these sheets (or create another sheet)
Go to Tools Macro Macros (or Alt+F8) and double-click on Test_xlFirstLastCols
The results for the active sheet and Sheet4 will be displayed.
Each sheet has a text box with info on what you should expect from the demo. You may also click on any of these text boxes to execute the demo.
(N.B. if you select Sheet4 as the active sheet the same result will be displayed twice.
xlFirstCol and xlLastCol are functions to be called by a higher level procedure, further testing will depend on how the functions are used).
Regards,
Praveen KVC
December 28 2009
Option Explicit
Sub Test_xlFirstLastCols()
' Target Application: MS Excel
' Demonstration: display first and last non-blank columns in the active sheet
' and one target sheet
Dim SheetName As String
'
' display sheet name and results from xlFindFirstCol and xlFindLastCol
' for the active sheet. Since activesheet is assumed if procs are called
' without a passed arguement, use that method here
'
MsgBox "Worksheet name = " & ActiveSheet.Name & vbCrLf & _
"First non-blank col = " & xlFirstCol() & vbCrLf & _
"Last non-blank col = " & xlLastCol(), vbInformation, _
"Active Sheet Demonstration"
'
' display sheet name and results from xlFindFirstCol and xlFindLastCol
' for "Sheet4". Since this is not the active sheet, the sheet must
' be defined via the passed arguement.
'
SheetName = "Sheet4"
MsgBox "Worksheet name = " & SheetName & vbCrLf & _
"First non-blank col = " & xlFirstCol(SheetName) & vbCrLf & _
"Last non-blank col = " & xlLastCol(SheetName), vbInformation, _
"Passed Sheet Name Demonstration"
End Sub
Function xlFirstCol(Optional WorksheetName As String) As Long
' finds the first populated col in a worksheet
If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlFirstCol = .Cells.Find("*", .Cells(.Cells.Count), xlFormulas, _
xlWhole, xlByColumns, xlNext).Column
If Err <> 0 Then xlFirstCol = 0
End With
End Function
Function xlLastCol(Optional WorksheetName As String) As Long
' finds the last populated col in a worksheet
If WorksheetName = vbNullString Then WorksheetName = ActiveSheet.Name
With Worksheets(WorksheetName)
On Error Resume Next
xlLastCol = .Cells.Find("*", .Cells(1), xlFormulas, _
xlWhole, xlByColumns, xlPrevious).Column
If Err <> 0 Then xlLastCol = 0
End With
End Function
How to use:
Copy the above code.
Open any workbook.
Press Alt + F11 to open the Visual Basic Editor (VBE).
In the left side window, select the target spreadsheet [it will likely be called VBAProject(name.xls) where name is the name of the spreadsheet]
Select an existing code module for the target worksheet; or from the Insert Menu, choose Insert Module.
Paste the code into the right-hand code window.
Close the VBE, save the file if desired.
Test the code:
In the attached example, there are 5 sheets with varying amounts of data.
Select any of these sheets (or create another sheet)
Go to Tools Macro Macros (or Alt+F8) and double-click on Test_xlFirstLastCols
The results for the active sheet and Sheet4 will be displayed.
Each sheet has a text box with info on what you should expect from the demo. You may also click on any of these text boxes to execute the demo.
(N.B. if you select Sheet4 as the active sheet the same result will be displayed twice.
xlFirstCol and xlLastCol are functions to be called by a higher level procedure, further testing will depend on how the functions are used).
Regards,
Praveen KVC
December 28 2009
Other Shortcut Keys in Excel
Key Description
ARROW KEYS Move one cell up, down, left, or right in a worksheet.
CTRL+ARROW KEY moves to the edge of the current data region (data region: A range of cells that contains data and that is bounded by empty cells or datasheet borders.) in a worksheet.
SHIFT+ARROW KEY extends the selection of cells by one cell.
CTRL+SHIFT+ARROW KEY extends the selection of cells to the last nonblank cell in the same column or row as the active cell.
LEFT ARROW or RIGHT ARROW selects the menu to the left or right when a menu is visible. When a submenu is open, these arrow keys switch between the main menu and the submenu.
DOWN ARROW or UP ARROW selects the next or previous command when a menu or submenu is open.
In a dialog box, arrow keys move between options in an open drop-down list, or between options in a group of options.
ALT+DOWN ARROW opens a selected drop-down list.
BACKSPACE Deletes one character to the left in the Formula Bar.
Also clears the content of the active cell.
DELETE Removes the cell contents (data and formulas) from selected cells without affecting cell formats or comments.
In cell editing mode, it deletes the character to the right of the insertion point.
END Moves to the cell in the lower-right corner of the window when SCROLL LOCK is turned on.
Also selects the last command on the menu when a menu or submenu is visible.
CTRL+END moves to the last cell on a worksheet, in the lowest used row of the rightmost used column.
CTRL+SHIFT+END extends the selection of cells to the last used cell on the worksheet (lower-right corner).
ENTER Completes a cell entry from the cell or the Formula Bar, and selects the cell below (by default).
In a data form, it moves to the first field in the next record.
Opens a selected menu (press F10 to activate the menu bar) or performs the action for a selected command.
In a dialog box, it performs the action for the default command button in the dialog box (the button with the bold outline, often the OK button).
ALT+ENTER starts a new line in the same cell.
CTRL+ENTER fills the selected cell range with the current entry.
SHIFT+ENTER completes a cell entry and selects the cell above.
ESC Cancels an entry in the cell or Formula Bar.
It also closes an open menu or submenu, dialog box, or message window.
HOME Moves to the beginning of a row in a worksheet.
Moves to the cell in the upper-left corner of the window when SCROLL LOCK is turned on.
Selects the first command on the menu when a menu or submenu is visible.
CTRL+HOME moves to the beginning of a worksheet.
CTRL+SHIFT+HOME extends the selection of cells to the beginning of the worksheet.
PAGE DOWN Moves one screen down in a worksheet.
ALT+PAGE DOWN moves one screen to the right in a worksheet.
CTRL+PAGE DOWN moves to the next sheet in a workbook.
CTRL+SHIFT+PAGE DOWN selects the current and next sheet in a workbook.
PAGE UP Moves one screen up in a worksheet.
ALT+PAGE UP moves one screen to the left in a worksheet.
CTRL+PAGE UP moves to the previous sheet in a workbook.
CTRL+SHIFT+PAGE UP selects the current and previous sheet in a workbook.
SPACEBAR In a dialog box, performs the action for the selected button, or selects or clears a check box.
CTRL+SPACEBAR selects an entire column in a worksheet.
SHIFT+SPACEBAR selects an entire row in a worksheet.
CTRL+SHIFT+SPACEBAR selects the entire worksheet.
If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the entire worksheet.
When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet.
ALT+SPACEBAR displays the Control menu for the Excel window.
TAB Moves one cell to the right in a worksheet.
Moves between unlocked cells in a protected worksheet.
Moves to the next option or option group in a dialog box.
SHIFT+TAB moves to the previous cell in a worksheet or the previous option in a dialog box.
CTRL+TAB switches to the next tab in dialog box.
CTRL+SHIFT+TAB switches to the previous tab in a dialog box.
Regards,
Praveen KVC
December 27 2009
ARROW KEYS Move one cell up, down, left, or right in a worksheet.
CTRL+ARROW KEY moves to the edge of the current data region (data region: A range of cells that contains data and that is bounded by empty cells or datasheet borders.) in a worksheet.
SHIFT+ARROW KEY extends the selection of cells by one cell.
CTRL+SHIFT+ARROW KEY extends the selection of cells to the last nonblank cell in the same column or row as the active cell.
LEFT ARROW or RIGHT ARROW selects the menu to the left or right when a menu is visible. When a submenu is open, these arrow keys switch between the main menu and the submenu.
DOWN ARROW or UP ARROW selects the next or previous command when a menu or submenu is open.
In a dialog box, arrow keys move between options in an open drop-down list, or between options in a group of options.
ALT+DOWN ARROW opens a selected drop-down list.
BACKSPACE Deletes one character to the left in the Formula Bar.
Also clears the content of the active cell.
DELETE Removes the cell contents (data and formulas) from selected cells without affecting cell formats or comments.
In cell editing mode, it deletes the character to the right of the insertion point.
END Moves to the cell in the lower-right corner of the window when SCROLL LOCK is turned on.
Also selects the last command on the menu when a menu or submenu is visible.
CTRL+END moves to the last cell on a worksheet, in the lowest used row of the rightmost used column.
CTRL+SHIFT+END extends the selection of cells to the last used cell on the worksheet (lower-right corner).
ENTER Completes a cell entry from the cell or the Formula Bar, and selects the cell below (by default).
In a data form, it moves to the first field in the next record.
Opens a selected menu (press F10 to activate the menu bar) or performs the action for a selected command.
In a dialog box, it performs the action for the default command button in the dialog box (the button with the bold outline, often the OK button).
ALT+ENTER starts a new line in the same cell.
CTRL+ENTER fills the selected cell range with the current entry.
SHIFT+ENTER completes a cell entry and selects the cell above.
ESC Cancels an entry in the cell or Formula Bar.
It also closes an open menu or submenu, dialog box, or message window.
HOME Moves to the beginning of a row in a worksheet.
Moves to the cell in the upper-left corner of the window when SCROLL LOCK is turned on.
Selects the first command on the menu when a menu or submenu is visible.
CTRL+HOME moves to the beginning of a worksheet.
CTRL+SHIFT+HOME extends the selection of cells to the beginning of the worksheet.
PAGE DOWN Moves one screen down in a worksheet.
ALT+PAGE DOWN moves one screen to the right in a worksheet.
CTRL+PAGE DOWN moves to the next sheet in a workbook.
CTRL+SHIFT+PAGE DOWN selects the current and next sheet in a workbook.
PAGE UP Moves one screen up in a worksheet.
ALT+PAGE UP moves one screen to the left in a worksheet.
CTRL+PAGE UP moves to the previous sheet in a workbook.
CTRL+SHIFT+PAGE UP selects the current and previous sheet in a workbook.
SPACEBAR In a dialog box, performs the action for the selected button, or selects or clears a check box.
CTRL+SPACEBAR selects an entire column in a worksheet.
SHIFT+SPACEBAR selects an entire row in a worksheet.
CTRL+SHIFT+SPACEBAR selects the entire worksheet.
If the worksheet contains data, CTRL+SHIFT+SPACEBAR selects the current region. Pressing CTRL+SHIFT+SPACEBAR a second time selects the entire worksheet.
When an object is selected, CTRL+SHIFT+SPACEBAR selects all objects on a worksheet.
ALT+SPACEBAR displays the Control menu for the Excel window.
TAB Moves one cell to the right in a worksheet.
Moves between unlocked cells in a protected worksheet.
Moves to the next option or option group in a dialog box.
SHIFT+TAB moves to the previous cell in a worksheet or the previous option in a dialog box.
CTRL+TAB switches to the next tab in dialog box.
CTRL+SHIFT+TAB switches to the previous tab in a dialog box.
Regards,
Praveen KVC
December 27 2009
Saturday, December 26, 2009
Function key shortcuts in Excel
Key Description
F1 Displays the Help task pane.
CTRL+F1 closes and reopens the current task pane.
ALT+F1 creates a chart of the data in the current range.
ALT+SHIFT+F1 inserts a new worksheet.
F2 Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when editing in a cell is turned off.
SHIFT+F2 edits a cell comment.
F3 Pastes a defined name into a formula.
SHIFT+F3 displays the Insert Function dialog box.
F4 Repeats the last command or action, if possible.
CTRL+F4 closes the selected workbook window.
F5 Displays the Go To dialog box.
CTRL+F5 restores the window size of the selected workbook window.
F6 Switches to the next pane in a worksheet that has been split (Window menu, Split command).
SHIFT+F6 switches to the previous pane in a worksheet that has been split.
CTRL+F6 switches to the next workbook window when more than one workbook window is open.
Note When the task pane is visible, F6 and SHIFT+F6 include that pane when switching between panes.
F7 Displays the Spelling dialog box to check spelling in the active worksheet or selected range.
CTRL+F7 performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press ESC.
F8 Turns extend mode on or off. In extend mode, EXT appears in the status line, and the arrow keys extend the selection.
SHIFT+F8 enables you to add a non-adjacent cell or range to a selection of cells by using the arrow keys.
CTRL+F8 performs the Size command (on the Control menu for the workbook window) when a workbook is not maximized.
ALT+F8 displays the Macro dialog box to run, edit, or delete a macro.
F9 Calculates all worksheets in all open workbooks.
F9 followed by ENTER (or followed by CTRL+SHIFT+ENTER for array formulas) calculates the selected a portion of a formula and replaces the selected portion with the calculated value.
SHIFT+F9 calculates the active worksheet.
CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.
CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.
CTRL+F9 minimizes a workbook window to an icon.
F10 Selects the menu bar or closes an open menu and submenu at the same time.
SHIFT+F10 displays the shortcut menu for a selected item.
ALT+SHIFT+F10 displays the menu or message for a smart tag. If more than one smart tag is present, it switches to the next smart tag and displays its menu or message.
CTRL+F10 maximizes or restores the selected workbook window.
F11 Creates a chart of the data in the current range.
SHIFT+F11 inserts a new worksheet.
ALT+F11 opens the Visual Basic Editor, in which you can create a macro by using Visual Basic for Applications (VBA).
ALT+SHIFT+F11 opens the Microsoft Script Editor, where you can add text, edit HTML tags, and modify any script code.
F12 Displays the Save As dialog box.
Regards,
Praveen KVC
December 26 2009
F1 Displays the Help task pane.
CTRL+F1 closes and reopens the current task pane.
ALT+F1 creates a chart of the data in the current range.
ALT+SHIFT+F1 inserts a new worksheet.
F2 Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when editing in a cell is turned off.
SHIFT+F2 edits a cell comment.
F3 Pastes a defined name into a formula.
SHIFT+F3 displays the Insert Function dialog box.
F4 Repeats the last command or action, if possible.
CTRL+F4 closes the selected workbook window.
F5 Displays the Go To dialog box.
CTRL+F5 restores the window size of the selected workbook window.
F6 Switches to the next pane in a worksheet that has been split (Window menu, Split command).
SHIFT+F6 switches to the previous pane in a worksheet that has been split.
CTRL+F6 switches to the next workbook window when more than one workbook window is open.
Note When the task pane is visible, F6 and SHIFT+F6 include that pane when switching between panes.
F7 Displays the Spelling dialog box to check spelling in the active worksheet or selected range.
CTRL+F7 performs the Move command on the workbook window when it is not maximized. Use the arrow keys to move the window, and when finished press ESC.
F8 Turns extend mode on or off. In extend mode, EXT appears in the status line, and the arrow keys extend the selection.
SHIFT+F8 enables you to add a non-adjacent cell or range to a selection of cells by using the arrow keys.
CTRL+F8 performs the Size command (on the Control menu for the workbook window) when a workbook is not maximized.
ALT+F8 displays the Macro dialog box to run, edit, or delete a macro.
F9 Calculates all worksheets in all open workbooks.
F9 followed by ENTER (or followed by CTRL+SHIFT+ENTER for array formulas) calculates the selected a portion of a formula and replaces the selected portion with the calculated value.
SHIFT+F9 calculates the active worksheet.
CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation.
CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated.
CTRL+F9 minimizes a workbook window to an icon.
F10 Selects the menu bar or closes an open menu and submenu at the same time.
SHIFT+F10 displays the shortcut menu for a selected item.
ALT+SHIFT+F10 displays the menu or message for a smart tag. If more than one smart tag is present, it switches to the next smart tag and displays its menu or message.
CTRL+F10 maximizes or restores the selected workbook window.
F11 Creates a chart of the data in the current range.
SHIFT+F11 inserts a new worksheet.
ALT+F11 opens the Visual Basic Editor, in which you can create a macro by using Visual Basic for Applications (VBA).
ALT+SHIFT+F11 opens the Microsoft Script Editor, where you can add text, edit HTML tags, and modify any script code.
F12 Displays the Save As dialog box.
Regards,
Praveen KVC
December 26 2009
Thursday, December 24, 2009
Find the Last used row in a particular sheet
There are several methods to accomplish this, some more exact than others, but they can all serve your needs.
One common method
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
which is not very exact, because Excel doesn't keep track of the last cell in a very adequate form.
Another method to find the last used row in a particular column is:
LastRowColA = Range("A65536").End(xlUp).Row
but this doesn't tell you FOR SURE the last used row in the entire sheet, unless you can be certain that Column A holds the data.
A couple extra methods are more reliable.
LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
or
LastRow = ActiveSheet.UsedRange.Rows.Count
This methods can be used on any sheet, not just the active sheet.
Regards,
Praveen KVC
December 25 2009
One common method
LastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
which is not very exact, because Excel doesn't keep track of the last cell in a very adequate form.
Another method to find the last used row in a particular column is:
LastRowColA = Range("A65536").End(xlUp).Row
but this doesn't tell you FOR SURE the last used row in the entire sheet, unless you can be certain that Column A holds the data.
A couple extra methods are more reliable.
LastRow = Cells.Find("*",SearchOrder:=xlByRows,SearchDirection:=xlPrevious).Row
or
LastRow = ActiveSheet.UsedRange.Rows.Count
This methods can be used on any sheet, not just the active sheet.
Regards,
Praveen KVC
December 25 2009
Subscribe to:
Posts (Atom)