Capture rapnge in Worksheet as Picture

To capture the range as an image use

range("A1:C10").CopyPicture xlScreen,xlBitmap 

To do this manually select the range A1:C10 and the whilst holding the shift key use the menu Edit. You should see a new menu item Copy Picture. The dialog allows you to specify picture or bitmap, screen or printer version.

List All the Name and other Information of the File in Folder and Sub Folder

The following Macro lists the all detail of the file in folder and subfolder

'*****************************************************************

Sub TestListFilesInFolder()
  Workbooks.Add ' create a new workbook for the file list
  ' add headers
  With Range("A1")
  .Formula = "Folder contents:"
  .Font.Bold = True
  .Font.Size = 12
  End With
  Range("A3").Formula = "File Name:"
  Range("B3").Formula = "File Size:"
  Range("C3").Formula = "File Type:"
  Range("D3").Formula = "Date Created:"
  Range("E3").Formula = "Date Last Accessed:"
  Range("F3").Formula = "Date Last Modified:"
  Range("G3").Formula = "Attributes:"
  Range("H3").Formula = "Short File Name:"
  Range("A3:H3").Font.Bold = True
  ListFilesInFolder "C:\FolderName\", True 
  ' list all files included subfolders
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
  Set FSO = New Scripting.FileSystemObject
  Set SourceFolder = FSO.GetFolder(SourceFolderName)
  r = Range("A65536").End(xlUp).Row + 1
  For Each FileItem In SourceFolder.Files
  ' display file properties
  Cells(r, 1).Formula = FileItem.Path & FileItem.Name
  Cells(r, 2).Formula = FileItem.Size
  Cells(r, 3).Formula = FileItem.Type
  Cells(r, 4).Formula = FileItem.DateCreated
  Cells(r, 5).Formula = FileItem.DateLastAccessed
  Cells(r, 6).Formula = FileItem.DateLastModified
  Cells(r, 7).Formula = FileItem.Attributes
  Cells(r, 8).Formula = FileItem.ShortPath & FileItem.ShortName
  ' use file methods (not proper in this example)
' FileItem.Copy "C:\FolderName\Filename.txt", True
' FileItem.Move "C:\FolderName\Filename.txt"
' FileItem.Delete True
  r = r + 1 ' next row number
  Next FileItem
  If IncludeSubfolders Then
  For Each SubFolder In SourceFolder.SubFolders
  ListFilesInFolder SubFolder.Path, True
  Next SubFolder
  End If
  Columns("A:H").AutoFit
  Set FileItem = Nothing
  Set SourceFolder = Nothing
  Set FSO = Nothing
  ActiveWorkbook.Saved = True
End Sub

'***************************************************************

Using F4 and Shift F4 key for finding in Excel

It becomes annoying to have find dialogue box while searching in front of the view. The following trick shows that we can hide the find dialogue box and still search. 

1. Use the Find option from the Edit menu as normal, specifying what I want to search for and then looking for the first occurrence.

2. When the first occurrence is displayed, I press the Esc key (or click on Cancel). The Find dialog box disappears.

3. To find the next occurrence, I press Shift+F4.

This procedure works the same as clicking Find Next repeatedly, and it is just as fast, but it gets rid of the annoying Find dialog box.

Drag Data Between Excel Worksheets

Have you ever tried to drag and drop data from one worksheet to another in MS Excel?

What happened?

I just bet that you started dragging that information towards the sheet tabs (looking to switch between worksheets) and were stunned to find that all the worksheet wanted to do was zoom off to some extremely high row number.

Now what?

Many people use Ctrl + Page Up/Down to move between sheets but if you're in the middle of a drag that won't work.

Some people just use the Cut/Paste routine which absolutely works but what if that's not your first instinct? What if the drag and drop is just the way you naturally work? Is there a solution for you?

I'm glad to report that the answer is yes.

Next time you're dragging the data, simply hold down the Alt key and the scrolling will stop.

Once you've got the scrolling stopped you'll find that you're allowed access to the sheet tabs.

While still holding the mouse button as a part of the drag, highlight a different sheet tab and poof!

You're on a different worksheet where you can easily complete the "drop" part of the data movement using your preferred method.

Simple Operation to Repair Microsoft Excel

Running Excel with the /regserver switch will cause Excel to rewrite all of its keys in the system registry. That rewrites all of the class ids for all the controls and objects back to the registry, overwriting any values that may have become corrupt. In addition all library files and type library get reassociated correctly.

It doesn't replace all of the actual libraries file like a real install does, but it rebuilds all the associations between those libraries and objects with the "factory defaults". It cannot fix a file because it doesn't alter the files, but can repair a faulty "reference" to a DLL, or something pointing to the wrong library.

This method can cure lots of odd problems with Excel, and is far easier and safer than doing a real re-install of the program. You don't have to worry about losing your toolbars etc. Before you ever do a full re-install, you should always run Excel with the /regserver switch as it may save you a lot of time and trouble.

(Press Windows Key + R, then type Excel /regserver to reset the registry)

[Comments from Chip Pearson via Excel.Programing Newsgroup]

Using Named Range in Visual Applications (macros) in Excel

I have a named range (Account) defined in a workbook, and I was wondering how to access and use that named range from within a macro.

After taking help and browsing the web i was able to find out several ways we can access the range, using either the Range object or the Names collection.

To access the named range using the Range object, all you need to do is provide the name of the range as a parameter to the object. This name is the same one that you defined within Excel. For instance, the following line could be used to change the interior color of the entire range:

Worksheets("Sheet1").Range("Account").Interior.Color = vbYellow

Note that the Range object is used relative to a particular worksheet, in this case Sheet1. You could also define a range object within VBA and then assign it to be equal to the named range, in this manner:

Set rng = Worksheets("Sheet1").Range("Account")
The other method of using the named range is to use the Names collection. The following line will again set the interior color of the range to yellow:

Workbooks("Book1.xls").Names("Account").RefersToRange.Interior.Color = vbYellow

Note that the Names collection is relative to the entire workbook, so it is not necessary to know which worksheet the named range is associated with when you use this method of access. You can also define a range object in VBA and assign it to be the same as the named range:

Set rng = Workbooks("Book1.xls").Names("Account").RefersToRange

You should know that the Names collection method of accessing a named range will only be viable if you don't have the same named range defined on different worksheets in the workbook. If you do, then you will need to use the Range object method, which requires the use of a specific worksheet name in the reference.

Delete Duplicate Rows in Excel

This code works very nice which deletes the duplicate rows in given set of data.

Sub DelDups() 

' Deletes duplicate rows in the selected range. 
' All columns in the selected range must be identical for 
' a row to be deleted. The entire row, not just the selected 
' cells in the row, will be deleted if a duplicate is found. 
' The first instance of the duplicate row is the copy that 
' will be retained. 

  Dim iRow As Long 
  Dim jRow As Long 
  Dim iCol As Integer 
  Dim LastRow As Long 'The last row in the selected range 
  Dim FirstRow As Long 'The first row in the selected range 
  Dim FirstCol As Integer 
  Dim LastCol As Integer 
  Dim DelCount As Long 'The count of duplicate rows removed 
  Dim DupFound As Boolean 'True if duplicate row found 
   
  DelCount = 0 
   
  FirstRow = Selection.Row 
  LastRow = FirstRow + Selection.Rows.Count - 1 
  FirstCol = Selection.Column 
  LastCol = FirstCol + Selection.Columns.Count - 1 
   
  For iRow = FirstRow To LastRow - 1 
   
  For jRow = iRow + 1 To LastRow 
   
  DupFound = True 
  For iCol = FirstCol To LastCol 
  DupFound = DupFound And (Cells(jRow, iCol) = Cells(iRow, iCol)) 
  If Not DupFound Then Exit For 
  Next iCol 
  If DupFound Then 
  ' Duplicate row found--delete it 
  Rows(jRow).Delete 
  LastRow = LastRow - 1 
  DelCount = DelCount + 1 
  End If 
   
  Next jRow 
   
  Next iRow 
   
  Beep 
  MsgBox DelCount & " duplicate rows deleted.", _ 
  vbInformation, "Duplicate Removal Results" 

End Sub 

Capturing Image from the Screen

I have written this trick to be used in excel or any program but I advice to use instead 'Turbonote' procedure as explained and taught which is quiet simple and more robust.

There may be times when you need to include a screen shot within your Elxcel worksheet. The easiest way to do this is as follows:

Set up your screen to look the way you want it to. 
Press the PrintScreen key. This copies the screen to the Clipboard. 
Start Excel (if it is not already started) and select the cell closest to where you want the screen to appear. 

Press Ctrl+V to paste the contents of the Clipboard. 

This action results in the entire screen being pasted in your worksheet. If you wanted, instead, to only copy and paste a single dialog box or the active window, simply use Alt+PrintScreen in step 2.

If either of these methods still does not fit your needs (for instance, you want to include only a small part of the screen), you can use the following:

Set up your screen to look the way you want it to. 
Press the PrintScreen key. This copies the screen to the Clipboard. 
Start the Paint accessory (or some other graphics program, such as Paint Shop Pro) and maximize the screen. 

Press Ctrl+V to paste the contents of the Clipboard into the program. 

Use the program's editing tools to change the image as desired. 
When complete, press Ctrl+C to copy the image to the Clipboard. 
Start Excel (if it is not already started) and select the cell closest to where you want the screen to appear. 

Press Ctrl+V to paste the contents of the Clipboard. 

You should understand that once the screen is placed in your workbook, you can move and manipulate it the same as any other graphic.

Also note that adding quite a few graphics to your worksheet can dramatically increase the size of the file in which your workbook is saved.

Custom Format Cells

Working with excel one of the main problem we face how to custom format the cell. That means how to show 12 as 00012 or 123 as 00123.

There is also an issue how to only show month in different forms.

Another issue is how to pre-fix or suffix custom text with the number in the cell. 

The file 'custom format cell' can be downloaded from

www.ncon.in/excel.htm ('custom format cell')

which demonstrates the use of Tricks where you can custom format the cell.

Excel has powerful function for custom formats only you need to learn and understand how to use it.

Using Index, Match, Vlookup, Hlookup, Lookup Function

The Example on www.ncon.in/excel.htm download section Demonstrates the Use of Vlookup, Combination ofMatch and Index , Offset and Match Function to get the value from the table.You can learn more about this function by taking online Help.

I will prefer to use Index-Match or Offset Function Instead ofusing Vlookup, Hlookup or Lookup functions.

Here I have shown two methods one is by using of cell references and other by Naming the range and then using Name range to get the data.

It depends on individual choice what to use.This example does not demonstrate the use of Dyanamic Range.It is because I presume let us first understand function and then goto advance stage.

Sheet2 of example demonstrate How you can use Form controls to reterive the value with combination of INDEX - MATCH and OFFSET - MATCH function.Last to say , if you prefer to use VLOOKUP or HLOOKUP function then you need to sort the data. For INDEX - MATCH, OFFSET - MATCH you donot require that so you can go on adding data to your range and can use dynamic range to get data.

Next week we shall discuss of dyanmic range, I already have example file of 'Dynamic Range' which can be download from the website, www.ncon.in/excel.htm.

75 Essential Microsoft Excel Tips

Click on the file to find out the 75 essential microsoft excel Tips.

www.ncon.in/excel.htm

Nishith Desai

List File in a folder using VBA in Microsoft Excel

Microsoft Scripting Runtime is included in these products:  
Windows98, Windows2000, IE5, and Office2000.  
The macro examples below assumes that your VBA project has added a reference to the 
Microsoft Scripting Runtime library.  
You can do this from within the VBE by selecting the menu Tools, References and selecting 

Microsoft Scripting Runtime.  

********************** Code**************************************************************************

Sub TestListFilesInFolder() 
  Workbooks.Add ' create a new workbook for th
  ' add headers 
  With Range("A1") 
  .Formula = "Folder contents:" 
  .Font.Bold = True 
  .Font.Size = 12 
  End With 
  Range("A3").Formula = "File Name:" 
  Range("B3").Formula = "File Size:" 
  Range("C3").Formula = "File Type:" 
  Range("D3").Formula = "Date Created:" 
  Range("E3").Formula = "Date Last Accessed:" 
  Range("F3").Formula = "Date Last Modified:" 
  Range("G3").Formula = "Attributes:" 
  Range("H3").Formula = "Short File Name:" 
  Range("A3:H3").Font.Bold = True 
  ListFilesInFolder "C:\FolderName\", True 
' list all files included subfolders 
End Sub

****************************** Code 2***************************************

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder 
' example: ListFilesInFolder "C:\FolderName\", True 
Dim FSO As Scripting.FileSystemObject 
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder 
Dim FileItem As Scripting.File 
Dim r As Long 
  Set FSO = New Scripting.FileSystemObject 
  Set SourceFolder = FSO.GetFolder(SourceFolderName) 
  r = Range("A65536").End(xlUp).Row + 1 
  For Each FileItem In SourceFolder.Files 
  ' display file properties 
  Cells(r, 1).Formula = FileItem.Path & FileItem.Name 
  Cells(r, 2).Formula = FileItem.Size 
  Cells(r, 3).Formula = FileItem.Type 
  Cells(r, 4).Formula = FileItem.DateCreated 
 Cells(r, 5).Formula = FileItem.DateLastAccessed 
  Cells(r, 6).Formula = FileItem.DateLastModified 
  Cells(r, 7).Formula = FileItem.Attributes 
  Cells(r, 8).Formula = FileItem.ShortPath & FileItem.ShortName 
  ' use file methods (not proper in this example) 
' FileItem.Copy "C:\FolderName\Filename.txt", True 
' FileItem.Move "C:\FolderName\Filename.txt" 
' FileItem.Delete True 
  r = r + 1 ' next row number 
  Next FileItem 
  If IncludeSubfolders Then 
  For Each SubFolder In SourceFolder.SubFolders 
  ListFilesInFolder SubFolder.Path, True 
  Next SubFolder 
  End If 
  Columns("A:H").AutoFit 
  Set FileItem = Nothing 
  Set SourceFolder = Nothing 
  Set FSO = Nothing 
  ActiveWorkbook.Saved = True 
End Sub