How to find-replace empty cells (Excel, QTP, VBScript)

Posted by Albert Gareev on Jan 29, 2010 | Categories: How toMS Excel Data

Parent page: Service Functions – Excel (QTP, VBScript)



Connect to Excel through COM – Open workbook – Open worksheet – Loop through data range – close and save the workbook.

Note.  In the example below script finds empty cells and assigns zero  to them.

Make sure you targeted the worksheet that you need. You can find it by index or by name.

Set XLHandle =  CreateObject("Excel.Application")
XLHandle.DisplayAlerts = False

Set XLBook = XLHandle.WorkBooks.Open("c:\temp\1.xls")

Set objUsedRange = XLBook.Worksheets.Item(1).UsedRange

For RowIter = 1 To objUsedRange.Rows.Count
 For ColIter = 1 To objUsedRange.Columns.Count
  If Trim(objUsedRange.Cells(RowIter, ColIter)) = "" Then
   objUsedRange.Cells(RowIter, ColIter) = 0
  End If



Set XLBook = Nothing
Set XLHandle = Nothing

  • Leave a Reply

    * Required
    ** Your Email is never shared

Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported
This work by Albert Gareev is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported.